question

jd0963 avatar image
jd0963 asked

Case/DATEDIFF question???

Please see the below query (This is part of a longer code that I need to edit) **select 'Admit '+cast (DATEDIFF(day,(case when Sc.AppointmentDateTime is null then Ws.EndTime else Sc.AppointmentDateTime end),sp.AdmissionDateTime) as varchar)+' day(s) later; ' AS 'data()'** from WHREPORTING.APC.Spell sp where sp.AdmissionDate>='01 jan 2016' Now I believe the part in bold brings out the `DATEDIFF` of the 2 dates as a figure. The output looks like this.. **"Admit 1 day(s) later; "** What I am trying to do is somehow make the output specify whether it is 1-2 days later, or 2-14 days later. if that makes sense? So if the figure is 5 it would say "Admit 2-14 days later" Would I need another case statement nested in there somehow? I really appreciate any help.
sqlquerysqlserver
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
select 'Admit ' + CASE WHEN DATEDIFF(day,(case when Sc.AppointmentDateTime is null then Ws.EndTime else Sc.AppointmentDateTime end),sp.AdmissionDateTime) = 1 THEN '1' WHEN DATEDIFF(day,(case when Sc.AppointmentDateTime is null then Ws.EndTime else Sc.AppointmentDateTime end),sp.AdmissionDateTime) BETWEEN 2 AND 14 THEN '2-14' ELSE '> 14' END + ' day(s) later; ' AS 'data()' from WHREPORTING.APC.Spell sp where sp.AdmissionDate>='01 jan 2016'
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jd0963 avatar image jd0963 commented ·
Thanks! I now seem to get this error.. Conversion failed when converting the nvarchar value '2-' to data type int. Are you able to advise on how I would resolve this? Thank you :o)
0 Likes 0 ·
jd0963 avatar image
jd0963 answered
Nevermind fixed it - thank you!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.