Finding a range of date within a range of date

Question

Finding a range of date within a range of date

rclazarito

SSC Enthusiast

Points: 114
More actions
April 1, 2020 at 10:30 am
Go to Answer
#3739568

I have a project that requires to display a list of patients within a specific period of dates but having problems building my statement the following is the sample data set.
CaseNo     DateAdmitted   DateDischarge
1                 12/01/2019        12/02/2019
2                12/01/2019         12/03/2019
3                12/02/2019        NULL                   - not yet discharge
Scenario 1: getting patients from 12/02/2019 to 12/03/2019
Case 1, 2, and 3
Scenario 2: patients from 12/03/2019 to 12/03/2019
Case 2 and 3
Thank in advance.

rclazarito

SSC Enthusiast

Points: 114
More actions
April 2, 2020 at 3:48 am
Answer
#3739889
Thank you for all your inputs the following is my final statement that solved my problem following ScottPletcher
```
SELECT *
FROM hospital_cases
WHERE ((date_admitted <= @EndDate) AND (date_discharge >= @StartDate)) OR
 ((date_admitted <= @ EndDate) AND (date_discharge IS NULL))
```

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

rclazarito SSC Enthusiast Points: 114 More actions · Answer 1

Found partial solution https://www.sqlservercentral.com/forums/topic/check-if-date-ranges-overlap. This solve if the DateDischarge is not NULL.

Steve Collins SSCrazy Points: 2157 More actions · Answer 2

declare
  @start_dt              datetime='2019-12-03',
  @end_dt                datetime='2019-12-03';

;with 
hosp_cte(CaseNo, DateAdmitted, DateDischarge) as (
    select 1,  '12/01/2019', '12/02/2019'
    union all
    select 2,  '12/01/2019', '12/03/2019'
    union all
    select 3,  '12/02/2019', null),
stay_cte(CaseNo, stay_dt) as (
    select hc.CaseNo, cast(d.[value] as date)
from
  hosp_cte hc
 cross apply
  dbo.daterange(cast(hc.DateAdmitted as date), isnull(cast(hc.DateDischarge as date), getdate()), 'dd' , 1) d)
select distinct 
  CaseNo
from
  stay_cte
where
  stay_dt>=@start_dt
  and stay_dt<=@end_dt
order by
  CaseNo;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Steve Collins SSCrazy Points: 2157 More actions · Answer 3

The dbo.daterange function, which is awesome, is described here:

https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Steve Collins SSCrazy Points: 2157 More actions · Answer 4

This is simpler

declare
  @start_dt              datetime='2019-12-02',
  @end_dt                datetime='2019-12-03';

;with 
hosp_cte(CaseNo, DateAdmitted, DateDischarge) as (
    select 1,  '12/01/2019', '12/02/2019'
    union all
    select 2,  '12/01/2019', '12/03/2019'
    union all
    select 3,  '12/02/2019', null)
select distinct 
  hc.CaseNo
from
  hosp_cte hc
 cross apply
  dbo.daterange(cast(hc.DateAdmitted as date), isnull(cast(hc.DateDischarge as date), getdate()), 'dd' , 1) d
where
  cast(d.[value] as date)>=@start_dt
  and cast(d.[value] as date)<=@end_dt
order by
  CaseNo;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

ScottPletcher SSC Guru Points: 101095 More actions · Answer 5

I prefer this method:

declare
  @start_dt              datetime='2019-12-02',
  @end_dt                datetime='2019-12-03';

;with 
hosp_cte(CaseNo, DateAdmitted, DateDischarge) as (
    select 1,  CAST('12/01/2019' AS datetime), CAST('12/02/2019' AS datetime)
    union all
    select 2,  '12/01/2019', '12/03/2019'
    union all
    select 3,  '12/02/2019', null
)
SELECT *
FROM hosp_cte
WHERE DateAdmitted <= @end_dt AND ISNULL(DateDischarge, GETDATE()) >= @start_dt

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Steve Collins SSCrazy Points: 2157 More actions · Answer 6

That's nice!

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können