SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CASE Statement in Where clause?


CASE Statement in Where clause?

Author
Message
upstart
upstart
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 327
Hi,

I am having a little trouble figuring this out and don't even know if I am on the correct path.

For simplicity, lets say I have 2 tables: lab_results and patient

I am trying to write 1 Select query that will:

- Return all records from lab_results table for a patient that exists in the patient_table

-If the "start_date" of the patient in the patient table is this month -> Do Nothing

-If the "start_date" of the patient in the patient_table is last_month -> Return last 6 months of records

-If the "start_date" of the patient is not this month or last month -> Return previous months records


I am trying to use a CASE statement in the WHERE clause, but I am receiving syntax errors and am not sure what I am doing wrong or if there is a better way to approach it.



SELECT lr.*
FROM lab_results lr
,patient pat
WHERE lr.patient_id = pat.patient_id
and CASE
WHEN datepart(month,pat.start) = datepart(month, getdate()) - 1
THEN lr.sample_date >= dateadd(m, datediff(m, 0, GETDATE()) - 6, 0)
AND lr.sample_date < DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))
WHEN (datepart(month, getdate()) - datepart(month, pat.start)) > 1
THEN lr.sample_date >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0)
AND lr.sample_date < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
ELSE
END





Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '>'.


GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23789 Visits: 9730
CASE doesn't work that way in SQL.

CASE in SQL returns a value based on conditions. You're using it more like a VB version of CASE, where it executes code.

Convert to a format like this:

WHERE lr.sample_date >= CASE 
WHEN datepart(month,pat.start) = datepart(month, getdate()) - 1
THEN dateadd(m, datediff(m, 0, GETDATE()) - 6, 0)



And so on, with each pat.start rule after a WHEN statement, and each date range after the corresponding THEN statement.

Make sense?

Also, you're testing that "datepart(month) = datepart(month)", which won't take year into effect. This may or may not matter in your data, but it would in most databases. The way you're testing it, if a patient's start date was October last year, it would count as "last month", because datepart(month, 'October last year') is 10, and that's 1 less than 11 (datepart(month, 'November this year')). (Please ignore my syntax errors on datepart. I'm making a point, not writing code.)

You'll be better off using date ranges, and dateadd than datepart, for that kind of thing. Unless your data somehow prevents last year from ever mattering here.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
upstart
upstart
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 327
Thanks! You helped me greatly....and you are correct, I had to take into account the Years of the dates as well.

I think (**hope**) I have thought of all the scenarios for possible start/end dates, but if anyone is interested, here is how my query turned out:



SELECT lr.*
FROM lab_results lr
,hl7_pat_map pat
WHERE lr.patient_id = pat.patient_id
and lr.sample_date >= CASE
--- When Start Date is previous Month of Current Year
WHEN (datepart(month,pat.start_date) = datepart(month, getdate()) - 1) and (DATEPART(year, pat.start_date) = DATEPART(year, DATEADD(m, 0, GETDATE()))) and (pat.end_date is null)
THEN dateadd(m, datediff(m, 0, GETDATE()) - 6, 0)
--- When Start Date is Previous Month of Previous Year
WHEN (datepart(month,pat.start_date) = datepart(month, getdate()) - 1) and (DATEPART(year, DATEADD(m, 0, GETDATE())) - DATEPART(year, pat.start_date) = 1) and (pat.end_date is null)
THEN dateadd(m, datediff(m, 0, GETDATE()) - 6, 0)
--- When Years Are differnt and Month is not previous Month
WHEN (DATEPART(year, DATEADD(m, 0, GETDATE()))) <> DATEPART(year, pat.start_date) and (datepart(month,pat.start_date) <> datepart(month, getdate()) - 1) and (DATEPART(year, DATEADD(m, 0, GETDATE()))) !< DATEPART(year, pat.start_date) and (pat.end_date is null)
THEN dateadd(m, datediff(m, 0, GETDATE()) - 1, 0)
--- When Years are the same, but month is not Previous Month
when (DATEPART(year, DATEADD(m, 0, GETDATE()))) = DATEPART(year, pat.start_date) and (datepart(month, getdate()) - datepart(month,pat.start_date) > 1) and (DATEPART(year, DATEADD(m, 0, GETDATE()))) !< DATEPART(year, pat.start_date) and (pat.end_date is null)
THEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0)
-- End_Date is Previous Month of Same Year
WHEN (datepart(month,pat.end_date) = datepart(month, getdate()) - 1) and (DATEPART(year, pat.end_date) = DATEPART(year, DATEADD(m, 0, GETDATE()))) and (pat.end_date is not null)
THEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0)
-- End_Date is Previous Month of Previous Year
WHEN (datepart(month,pat.end_date) = datepart(month, getdate()) - 1) and (DATEPART(year, DATEADD(m, 0, GETDATE())) - DATEPART(year, pat.end_date) = 1) and (pat.end_date is not null)
THEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0)
-- End Date is This Month or in the Future
WHEN (datepart(month,pat.end_date) >= datepart(month, getdate()))
THEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0)
END
and lr.sample_date <= CASE
WHEN (datepart(month,pat.start_date) = datepart(month, getdate()) - 1) and (DATEPART(year, pat.start_date) = DATEPART(year, DATEADD(m, 0, GETDATE()))) and (pat.end_date is null)
THEN DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))
WHEN (datepart(month,pat.start_date) = datepart(month, getdate()) - 1) and (DATEPART(year, DATEADD(m, 0, GETDATE())) - DATEPART(year, pat.start_date) = 1) and (pat.end_date is null)
THEN DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))
WHEN (DATEPART(year, DATEADD(m, 0, GETDATE()))) <> DATEPART(year, pat.start_date) and (datepart(month,pat.start_date) <> datepart(month, getdate()) - 1) and (DATEPART(year, DATEADD(m, 0, GETDATE()))) !< DATEPART(year, pat.start_date) and (pat.end_date is null)
THEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
when (DATEPART(year, DATEADD(m, 0, GETDATE()))) = DATEPART(year, pat.start_date) and (datepart(month, getdate()) - datepart(month,pat.start_date) > 1) and (DATEPART(year, DATEADD(m, 0, GETDATE()))) !< DATEPART(year, pat.start_date) and (pat.end_date is null)
THEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
-- End_Date is Previous Month of Same Year
WHEN (datepart(month,pat.end_date) = datepart(month, getdate()) - 1) and (DATEPART(year, pat.end_date) = DATEPART(year, DATEADD(m, 0, GETDATE()))) and (pat.end_date is not null)
THEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
-- End_Date is Previous Month of Previous Year
WHEN (datepart(month,pat.end_date) = datepart(month, getdate()) - 1) and (DATEPART(year, DATEADD(m, 0, GETDATE())) - DATEPART(year, pat.end_date) = 1) and (pat.end_date is not null)
THEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
WHEN (datepart(month,pat.end_date) >= datepart(month, getdate()))
THEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
END


GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23789 Visits: 9730
There are easier ways to get "last month" and such type dates.

Take a look at this:

SELECT  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)   AS FirstOfCurrentMonth,
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) AS FirstOfPriorMonth,
DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AS FirstOfCurrentYear



What it does is use DateDiff to get the number of months from "date zero" to today, and then adds that number of months to "date zero". In a default SQL Server installation, "date zero" is 1 Jan 1900, but it doesn't really matter what date it is set to, since the difference and addition will still get the same result. The second one gets the prior month by simply subtracting 1 from the difference, and the last one uses years instead of months to get the first day of the current year. Yes, getting the first day of the year is trivial, this is just meant to show how to extend the technique.

So, if you need to check if a date is from last month, use the second calculation to get the first date, and the first calculation to get the last date of that range, and then use the usual greater-than-or-equal-to for the first, and less-than for the end, and you have it.

Because of the way SQL Server stores dates (as numbers), and because of the way DateAdd and DateDiff work, this technique is much faster than anything else I've seen for this kind of thing.

Because it's simple math once you know what the functions do, it's easy to get it to generate ranges like "last 6 months" (just use -6 instead of -1 in the "last month" version), or "last quarter" (use quarters instead of months/years in the functions), or whatever you need date-wise.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
gofrancesc
gofrancesc
Right there with Babe
Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)

Group: General Forum Members
Points: 767 Visits: 812
Hello upstart,
your use of datepart here is chaotic for me and I think there is a easier way to do it. You are interested only in compare the year/month, not the day, so using DATEDIFF(MONTH, startDate, endDate) should cover your needs.
I think you can code your query like that,
SELECT lr.*
FROM lab_results lr, patient pat
WHERE lr.patient_id = pat.patient_id
and lr.sample_date >= CASE datediff(month, pat.start, getdate())
WHEN 0 THEN '2199-01-01'
WHEN 1 THEN dateadd(month, datediff(month, 0, GETDATE()) - 5, 0)
ELSE DATEADD(month, DATEDIFF(month, 0, pat.start), 0)
END



Francesc
MMartin1
MMartin1
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2799 Visits: 2031
I think the reply from frfernan is your best bet. To add to that:

You need not code an exact date, You can do like

 
SELECT lr.*
FROM lab_results lr
,patient pat
WHERE lr.patient_id = pat.patient_id and lr.sample_date >=
case datediff(month,pat.start_Date, getdate())
when 0 then lr.sample_date +1
when 1 then dateadd(month,datediff(month,0,getdate())-6,0) --- or dateadd(month, -6, getdate()) ...based on your exact requirement
else DATEADD(month, DATEDIFF(month, 0, pat.start), 0)
END



Plus it is more readable and you dont have to worry about patients with tests going back years or when January is the current month like you do with your initial approach.

Mark

----------------------------------------------------
How to post forum questions to get the best help
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search