In SQL, can you write a where clause, if a date range falls within a date range-CodePudding

I work with patient information, which contains an admit date and a discharge date. I have a query that I need to add a WHERE clause that would only identify patients within a date range.

For instance, I want to pull all patients that where in the hospital from 5-1-2021 to 5-31-2021. This would include patients with an admit/discharge (and anything in between) at any point in May.

Hospital 1      
Patient Admit_Date  Disch_Date
1       4/5/2021    5/20/2021
2       5/6/2021    5/10/2021
3      4/10/2021    4/22/2021
4      4/19/2021    7/5/2021
5       6/1/2021    6/7/2021

I want to only include patients that were in Hospital 1 from 5/1/2021 to 5/31/2021, so it would yield this table:

Hospital 1      
Patient Admit_Date  Disch_Date
1       4/5/2021    5/20/2021
2       5/6/2021    5/10/2021
4      4/19/2021    7/5/2021

I tried using a BETWEEN clause, but it didn't include patients like patient #4 who were admitted before May and Discharge after May, but were in the hospital in May. Basically I'm trying to pull a census, so I can say there were XX number of patients on May 4th or May 22nd etc. but for all of May (or any month for that matter).

Thanks!!!

CodePudding user response：

Generally best to define spans of time as Hslf-Open, where the beginning is inclusive while the ending is exclusive. So the month of May starts with May 1 and runs up to, but does not include, the first of June.

Do not use the SQL command BETWEEN for this work. That command is Fully-Closed rather than Half-Open.

You want an overlaps test, where the row’s date range is neither entirely before, nor entirely after, the target date range. In other words, the two ranges share some part of the time-line.

So the logic is:

row start is before target end, AND
row end is after target beginning.

In SQL code:

SELECT *
FROM event_ 
WHERE start < 2021-06-01 
AND end > 2021-05-01
;

CodePudding user response：

For the duration of an admission to have fallen within the month of May the admission must have started before the end of May, and ended after the start of May. This "feels" off, but you can see the logic of it if you draw out the timelines yourself with a pen and paper.

The requisite query is then:

SELECT * FROM admissions
WHERE admit_date <= "2021-05-31" 
AND disch_date >= "2021-05-01";