x
+----------+-----------+-----------+-----------+----------+
| 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
Advertisement
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)