CODEWITHSUNDEEP
sqlsql-serverdatetimecommon-table-expressionrecursive-query
oke_8992
oke_8992

Reputation: 21

How to select rows based on a rolling 30 day window SQL

My question involves how to identify an index discharge.

The index discharge is the earliest discharge. On that date, the 30 day window starts. Any admissions during that time period are considered readmissions, and they should be ignored. Once the 30 day window is over, then any subsequent discharge is considered an index and the 30 day window begins again.

I can't seem to work out the logic for this. I've tried different windowing functions, I've tried cross joins and cross applies. The issue I keep encountering is that a readmission cannot be an index admission. It must be excluded.

I have successfully written a while loop to solve this problem, but I'd really like to get this in a set based format, if it's possible. I haven't been successful so far.

Ultimate goal is this -

id AdmitDate DischargeDate MedicalRecordNumber IndexYN
1 2021-03-03 00:00:00.000 2021-03-09 13:20:00.000 X0090362 1
4 2021-03-05 00:00:00.000 2021-03-10 16:00:00.000 X0012614 1
6 2021-05-18 00:00:00.000 2021-05-21 22:20:00.000 X0012614 1
7 2021-06-21 00:00:00.000 2021-07-08 13:30:00.000 X0012614 1
8 2021-02-03 00:00:00.000 2021-02-09 17:00:00.000 X0019655 1
10 2021-03-23 00:00:00.000 2021-03-26 16:40:00.000 X0019655 1
11 2021-03-15 00:00:00.000 2021-03-18 15:53:00.000 X4135958 1
13 2021-05-17 00:00:00.000 2021-05-23 14:55:00.000 X4135958 1
15 2021-06-24 00:00:00.000 2021-07-13 15:06:00.000 X4135958 1

Sample code is below.

CREATE TABLE #Admissions
  (
     [id]                INT,     
     [AdmitDate]         DATETIME,
     [DischargeDateTime] DATETIME,
     [UnitNumber]        VARCHAR(20),
     [IndexYN]           INT
  ) 

  INSERT INTO #Admissions
  VALUES( 1  ,'2021-03-03'  ,'2021-03-09 13:20:00.000'  ,'X0090362', NULL)
        ,(2  ,'2021-03-27'  ,'2021-03-30 19:59:00.000'  ,'X0090362', NULL)
        ,(3  ,'2021-03-31'  ,'2021-04-04 05:57:00.000'  ,'X0090362', NULL)
        ,(4  ,'2021-03-05'  ,'2021-03-10 16:00:00.000'  ,'X0012614', NULL)
        ,(5  ,'2021-03-28'  ,'2021-04-16 13:55:00.000'  ,'X0012614', NULL)
        ,(6  ,'2021-05-18'  ,'2021-05-21 22:20:00.000'  ,'X0012614', NULL)
        ,(7  ,'2021-06-21'  ,'2021-07-08 13:30:00.000'  ,'X0012614', NULL)
        ,(8  ,'2021-02-03'  ,'2021-02-09 17:00:00.000'  ,'X0019655', NULL)
        ,(9  ,'2021-02-17'  ,'2021-02-22 17:25:00.000'  ,'X0019655', NULL)
        ,(10 ,'2021-03-23'  ,'2021-03-26 16:40:00.000'  ,'X0019655', NULL)
        ,(11 ,'2021-03-15'  ,'2021-03-18 15:53:00.000'  ,'X4135958', NULL)
        ,(12 ,'2021-04-08'  ,'2021-04-13 19:42:00.000'  ,'X4135958', NULL)
        ,(13 ,'2021-05-17'  ,'2021-05-23 14:55:00.000'  ,'X4135958', NULL)
        ,(14 ,'2021-06-09'  ,'2021-06-14 12:45:00.000'  ,'X4135958', NULL)
        ,(15 ,'2021-06-24'  ,'2021-07-13 15:06:00.000'  ,'X4135958', NULL)

Upvotes: 0

Views: 362

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270493

You can use a recursive CTE to identify all rows associated with each "index" discharge:

with a as (
      select a.*, row_number() over (order by dischargedatetime) as seqnum
      from admissions a
     ),
     cte as (
      select id, admitdate, dischargedatetime, unitnumber, seqnum, dischargedatetime as index_dischargedatetime
      from a
      where seqnum = 1
      union all
      select a.id, a.admitdate, a.dischargedatetime, a.unitnumber, a.seqnum,
             (case when a.dischargedatetime > dateadd(day, 30, cte.index_dischargedatetime)
                   then a.dischargedatetime else cte.index_dischargedatetime
              end) as index_dischargedatetime
      from cte join
           a
           on a.seqnum = cte.seqnum + 1
     )
select *
from cte;

You can then incorporate this into an update:

update admissions
    set indexyn = (case when admissions.dischargedatetime = cte.index_dischargedatetime then 'Y' else 'N' end)
    from cte
    where cte.id = admissions.id;

Here is a db<>fiddle. Note that I changed the type of IndexYN to a character to assign 'Y'/'N', which makes sense given the column name.

Upvotes: 1

Related Questions