Reid McCamish Reid McCamish - 3 months ago 11
SQL Question

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?

Answer

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
Comments