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,
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
``````

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?

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
``````