Get nth result in Partition if n-1, n-2.. results are within 30 days of n

+----------+-----------+-----------+-----------+----------+
| ClientID | EpisodeID | StartDT   | EndDT     | Location |
+----------+-----------+-----------+-----------+----------+
| 1        | 1         | 3/1/2019  | 3/14/2019 | A        |
+----------+-----------+-----------+-----------+----------+
| 1        | 2         | 6/5/2019  | 6/18/2019 | B        |
+----------+-----------+-----------+-----------+----------+
| 1        | 3         | 6/21/2019 | 6/25/2019 | C        |
+----------+-----------+-----------+-----------+----------+
| 2        | 5         | 4/13/2019 | 4/19/2019 | A        |
+----------+-----------+-----------+-----------+----------+
| 2        | 6         | 4/25/2019 | 5/2/2019  | A        |
+----------+-----------+-----------+-----------+----------+
| 3        | 10        | 8/1/2019  | 8/18/2019 | E        |
+----------+-----------+-----------+-----------+----------+
| 3        | 11        | 10/1/2019 | 10/9/2019 | F        |
+----------+-----------+-----------+-----------+----------+

Looking for output to only have the latest episode row for each client if there are multiple episodes or a readmission in a 30 day window (30 days between end date of preceding episode(s) and start date of following episode), while also keeping episodes of that client if there was not a follow-up episode 30 days after EndDT.

Desired Output:

+----------+-----------+-----------+-----------+----------+
| ClientID | EpisodeID | StartDT   | EndDT     | Location |
+----------+-----------+-----------+-----------+----------+
| 1        | 1         | 3/1/2019  | 3/14/2019 | A        |
+----------+-----------+-----------+-----------+----------+
| 1        | 3         | 6/21/2019 | 6/25/2019 | C        |
+----------+-----------+-----------+-----------+----------+
| 2        | 6         | 4/25/2019 | 5/2/2019  | A        |
+----------+-----------+-----------+-----------+----------+
| 3        | 10        | 8/1/2019  | 8/18/2019 | E        |
+----------+-----------+-----------+-----------+----------+
| 3        | 11        | 10/1/2019 | 10/9/2019 | F        |
+----------+-----------+-----------+-----------+----------+

Simplified Code attempted:

WITH CTE AS
(
SELECT
    ClientID
    ,EpisodeID
    ,StartDT
    ,EndDT
    ,Location

FROM DeNormalized DN)

SELECT 
    CTE.*

FROM CTE
INNER JOIN CTE CTE2
    ON CTE.ClientID=CTE2.ClientID
WHERE 1=1
    AND DATEDIFF(day,CTE2.EndDT,CTE.StartDT) <= 30

I also don’t have DDL permissions except temp objects since this is a hosted T-SQL database

Answer

I think you can use lead():

select dn.*
from (select dn.*, lead(startdt) over (partition by clientId order by startdt) as next_startdt
      from denormalized dn
     ) dn
where next_startdt is null or
      next_startdt > dateadd(day, 30, enddt)

Advertisement

Answer