0

I am trying to calculate days between two dates so that I can find out if they fall in the 30 / 60 / 90 day bracket
The calculation I am using is working fine

DATEDIFF(day, dbo.TblReturnDetails.RcvdDate, { fn NOW() })
what I am trying to do now is say
if the days is less than 90 then give a number 3
if the days is less than 60 then give a nmber 2
if the days is less than 30 then give a number 1

CASE WHEN DATEDIFF(day , dbo.TblReturnDetails.RcvdDate , { fn NOW() }) >= 90 THEN '3' WHEN DATEDIFF(day , dbo.TblReturnDetails.RcvdDate , { fn NOW() }) >= 60 THEN '2' WHEN DATEDIFF(day , dbo.TblReturnDetails.RcvdDate , { fn NOW() }) >= 30 THEN '1' END

but at the moment using the above Case I get the following data.

Day
199          3
55           1
29           NULL
17           NULL
17           NULL

can anyone see where i am going wrong?

3
Contributors
4
Replies
34
Views
3 Years
Discussion Span
Last Post by Mike Bishop
0

>= means greater than. <= means less than, the one you want I guess. Note that if you switch, the order of the WHEN matters.

0

i have now changed to

CASE WHEN DATEDIFF(day , dbo.TblReturnDetails.RcvdDate , { fn NOW() }) > 60 THEN '3' WHEN DATEDIFF(day , dbo.TblReturnDetails.RcvdDate , { fn NOW() }) > 30 THEN '2' END

but need to add when <30 then '1'

199 3
55 2
29 NULL
17 NULL

can anyone help?

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.