help with Select Case

[Mike Bishop]

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?

[pritaeas]

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

[Mike Bishop]

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?

[AndrisP]

add else '1' before END

[Mike Bishop]

perfect thank you