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?

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

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?

add `else '1'` before `END`

perfect thank you

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.