November 21, 2023 at 10:04 pm
Hi
Having an issue trying to figure out how to get my sample population where there are two date ranges to compare.
Student has an Admit,Discharge,Open And Close date
I need to get all students who where "active" between say '7/1/23' and '7/31/23'
They record is valid if it meets the either Admit/discharge or Open/close
I'm using
WHERE
(
(
([Admission Date] between '7/1/23' and '7/31/23')
or
([Admission Date]<'7/1/23' and [Discharge Date] is null)
or
([Admission Date]<'7/1/23' and [Discharge Date] >'7/1/23')
)
or
(
([Open Date] between'7/1/23' and '7/31/23')
or
([Open Date]<'7/1/23' and [Close Date]is null)
or
([Close Date] < '7/1/23' and [Close Date] >'7/1/23')
)
)
but picking up records that have an open and close date AND ONLY an AdmitDate, even if they are out of range because the first part of the where clause is true . I cant use AND because they can have admit/discharge OR open/close.
here are a couple samples:
ClientA : OpenDate 7/1/18, close date 7/19/18, Admit 7/1/18, discharge date is null
ClientA gets picked up because Discharge date is null. But shouldn't because they have a close date outside the range
Same would be true for Admit/Discharge
Hope I'm not too vague
Thanks
November 22, 2023 at 1:53 pm
The date columns are of data type DATE? It's not confirmed by question. For this answer the dates must be stored as DATE.
;with example_cte as (
select cast('7/1/18' as date) [Open Date], cast('7/19/18' as date) [Close Date],
cast('7/1/18' as date) [Admission Date], cast(null as date) [Discharge Date])
select *
from example_cte
WHERE
(([Admission Date] between '7/1/23' and '7/31/23') or ([Discharge Date] between '7/1/23' and '7/31/23'))
or
(([Admission Date]<'7/1/23' and [Discharge Date] is null) and (([Open Date] between'7/1/23' and '7/31/23') or ([Close Date] between '7/1/23' and '7/31/23')))
or
(([Open Date] between'7/1/23' and '7/31/23') or ([Close Date] between '7/1/23' and '7/31/23'))
or
(([Open Date]<'7/1/23' and [Close Date] is null) and ([Admission Date] between '7/1/23' and '7/31/23') or ([Discharge Date] between '7/1/23' and '7/31/23'));
WHERE conditions are predicates which must be TRUE for the rows returned by the query and so they need to be explicit and complete. I updated the code to what I think it's intended to be and asked ChatGPT-4 to explain it:
The user's original WHERE clause was not correctly filtering out records where the student was not active during the specified date range. The issue was that the conditions for the Admission Date and Open Date were not properly linked to the conditions for the Discharge Date and Close Date. This resulted in records being returned where the student was admitted or opened before the date range, but not necessarily active during it.
The corrected code addresses this by ensuring that the conditions for the Admission Date and Discharge Date, as well as the Open Date and Close Date, are evaluated together. This is done by grouping the conditions for each pair of dates together using parentheses, and using the OR operator to ensure that a record is returned if either pair of dates falls within the specified date range.
Additionally, the corrected code includes conditions to check if the Discharge Date or Close Date fall within the specified date range. This ensures that students who were discharged or closed during the date range are also returned, even if they were admitted or opened before the date range.
Finally, the corrected code includes conditions to handle cases where the Discharge Date or Close Date are null. These conditions ensure that students who were admitted or opened before the date range, but have not yet been discharged or closed, are also returned.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
November 22, 2023 at 5:03 pm
You're making the two most common mistakes when working with intervals.
The tendency when comparing two intervals is to compare the starting values with starting values and ending values with ending values. This leads to overly complicated code. To understand this, it's easiest to start with the converse: did one end before the other started? In code:
a.end_dt < b.start_dt OR b.end_dt < a.start_dt
So, taking the converse shows when two intervals overlap:
b.start_dt <= a.end_dt AND a.start_dt <= b.end_dt
The other mistake is allowing NULL values in your interval. NULL values can be difficult to work with. This is even more true with intervals with NULL end points. Since you've only posted one sample record (and in the incorrect format) it's hard to test, but I think the following is what you are looking for. (I'm using variables instead of your hard-coded values for the test range.)
admission_dt <= @end_dt AND @start_dt <= COALESCE(discharge_dt, close_dt, '99990101')
OR
open_dt <= @end_dt AND @start_dt <= COALESCE(close_dt, discharge_d, '99990101')
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply