CODEWITHSUNDEEP
sqlsql-servert-sql
Rominus
Rominus

Reputation: 1221

How do you find date intervals across rows when there are more than 2 rows?

I'm calculating patient readmission rates and need to find what patients have readmitted within a certain interval, and how often. I have admit data that looks like: 

Subscriber_id   New_Admission_date
01              2016-06-02 
02              2016-06-01 
03              2016-06-10 
04              2016-06-08 
02              2016-06-04 
02              2016-06-30 
03              2016-06-28

To find what patients have readmitted within 14 days and what the interval between admits was, I have this code:

select ra.Subscriber_id, DATEDIFF(d,ra.first_ad,ra.last_ad) as interval
from
(
    select j.Subscriber_ID, 
    min(j.New_admission_date) as first_ad, 
    max (j.New_Admission_Date) as last_ad
    from June_inpatients as j
    inner join
        (select j.Subscriber_ID, count(Subscriber_ID) as total
        from June_inpatients as j
        group by Subscriber_ID
        having count(Subscriber_ID) >1 ) as r
    on j.Subscriber_ID = r.Subscriber_ID
    group by j.Subscriber_ID
) as ra
where DATEDIFF(d,ra.first_ad,ra.last_ad) < 15

The problem is that some patients, like patient ID 02 in the example data, have more than 2 admits. My code misses any intermediary admits since it's using min() and max(). How would I find the interval between a patient's first admit and second admit when there are three admits, and then find the interval between the second admit and the third?

Upvotes: 0

Views: 174

Answers (3)

Sagar Shelke
Sagar Shelke

Reputation: 517

This will work without Lag function.

;WITH J
AS
(
    SELECT ROW_NUMBER() OVER(PARTITION BY J1.Subscriber_id ORDER BY J1.New_Admission_date ) ROW_ID ,*
    FROM June_inpatients  J1
)
SELECT J1.Subscriber_id, J1.New_Admission_date Previous_Admission_date ,
      J2.Subscriber_id, J2.New_Admission_date , DATEDIFF(DD,J1.New_Admission_date,J2.New_Admission_date) Interval
FROM J J1
INNER JOIN J J2 ON J1.Subscriber_id = J2.Subscriber_id AND J1.ROW_ID = J2.ROW_ID -1 
WHERE DATEDIFF(DD,J1.New_Admission_date,J2.New_Admission_date)<15

Upvotes: 1

jhilden
jhilden

Reputation: 12429

Assuming you're using at least SQL 2012 you can use the Lag function.

The idea with LAG/LEAD is that we can query data from the previous/next rows returned.

In my full example below I use LAG twice, once on subscriber and once on the date. Ordering by the subscriber and date guarantees that the previous/next rows will be in the correct order. I then limit my where clause to ensure:

  1. that the previous row is for the same subscriber
  2. that the dates are within 15 days
DECLARE @tbl TABLE (
pkey INT NOT NULL PRIMARY KEY IDENTITY,
subscriber INT NOT NULL,
dt DATETIME NOT NULL
);

INSERT INTO @tbl
        ( subscriber, dt )
VALUES  
( 1, '2016-06-02'),
( 2, '2016-06-01'),
(3, '2016-06-10'),
(4, '2016-06-08'),
(2, '2016-06-04'),
(2, '2016-06-30'),
(3, '2016-06-28');

SELECT *
FROM @tbl
ORDER BY subscriber, dt

; WITH tmp AS (
SELECT subscriber, dt, 
LAG(subscriber) OVER (ORDER BY subscriber, dt) previousSubscriber,
LAG(dt) OVER (ORDER BY subscriber, dt) previousDt
FROM @tbl
--ORDER BY subscriber, dt
)
SELECT tmp.*, DATEDIFF(DAY, previousDt, dt)
FROM tmp
WHERE tmp.subscriber = previousSubscriber
    AND DATEDIFF(DAY, previousDt, dt) < 15

Upvotes: 2

Code Different
Code Different

Reputation: 93151

If you are using SQL Server 2012 or later, try this:

;WITH
    cte As
    (
        SELECT      Subscriber_id,
                    LAG(New_Admission_Date, 1) OVER (PARTITION BY Subscriber_id ORDER BY New_Admission_Date)    AS PreviousAdmissionDate,
                    New_Admission_Date
        FROM        AdmissionTable
    )

SELECT      *
FROM        cte
WHERE       DATEDIFF(DAY, PreviousAdmissionDate, New_Admission_Date) <= 14

Upvotes: 1

Related Questions