Finding a range of date within a range of date

  • 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.

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

  • 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

  • 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

  • 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

  • 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."

  • That's nice!

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

  • 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