Finding a range of date within a range of date

  • rclazarito

    SSC Enthusiast

    Points: 114

    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

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

  • scdecade

    SSC Eights!

    Points: 807

    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;

     

  • scdecade

    SSC Eights!

    Points: 807

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

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

  • scdecade

    SSC Eights!

    Points: 807

    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;
  • ScottPletcher

    SSC Guru

    Points: 98434

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • scdecade

    SSC Eights!

    Points: 807

    That's nice!

  • rclazarito

    SSC Enthusiast

    Points: 114

    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 8 (of 8 total)

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