November 29, 2011 at 10:44 am
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 '>'.
November 29, 2011 at 10:54 am
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
November 29, 2011 at 2:34 pm
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
November 30, 2011 at 6:40 am
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
December 1, 2011 at 2:47 am
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
December 2, 2011 at 4:56 pm
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
----------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply