Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cmk8895
Starting Member
5 Posts |
 Posted - 2006-11-08 : 15:18:39
|
| I am using datediff to calculate the time frame between events. However, sometimes a date was entered incorrectly, thus resulting in a negative number. I would like to, at the time of calculation, catch negative numbers and instead mark them with 'Error' or some such.An example would be:CASE WHEN datediff(day,date1,date2)<0 THEN 'Error' ELSE datediff(day,date1,date2)END AS cycle_timeThis does the calculation twice, which isn't very efficient. I could also just store the values in a temp table and tag them on the next SELECT. But surely I'm missing some simple way to catch this at calculation time without the overhead of doing the calc twice?Thanks for any thoughts. cmk |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-08 : 15:28:22
|
| If you're using SQL Server 2005 use a CTE. If you're using SQL Server 2000, create a view that has the calculation in it, then create your query on the view and use the calculated view column in the query. Then the calculation only runs once no matter how many times you use it in the query. |
 |
|
|
cmk8895
Starting Member
5 Posts |
Posted - 2006-11-09 : 08:44:04
|
| Hmmm A CTE in Server 2005. That's something I've never done.Thanks for the advice. I'll do some reading on CTEs |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-11-09 : 08:54:43
|
[code]case when date1 < date2 then convert(varchar(10), datediff(day, date1, date2)) else 'error' end as cycle_time[/code]
KH |
|
|
|
cmk8895
Starting Member
5 Posts |
Posted - 2006-11-09 : 09:05:08
|
| Ha! I knew somebody would see it in a way that I didn't.Thanks khtan. That is exactly what I was looking for.I'm still going to look into this CTE thing since it's something I don't know about, but that case statement is perfect for this application. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-11-09 : 11:12:07
|
| Where do you want to show data?If you use front end application, check the condition there and display. Using Tan's suggestion will prevent you making calculations on datediff valuesMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Fast way to tag negative fields