SQL consolidate overlapping dates based on criteria

I’m trying to merge overlapping dates between Admit and discharge dates of patients. There are a few edge cases which I couldn’t cover in the query.

Input

+----+------------+--------------+
| ID | Admit_Dt   | Discharge_Dt |
+----+------------+--------------+
| 1  | 12/30/2020 | 07/14/2021   |
+----+------------+--------------+
| 1  | 01/02/2021 | 07/14/2021   |
+----+------------+--------------+
| 1  | 06/16/2021 | 07/14/2021   |
+----+------------+--------------+
| 2  | 03/04/2021 | 03/25/2021   |
+----+------------+--------------+
| 2  | 05/01/2021 | 05/10/2021   |
+----+------------+--------------+
| 3  | 06/01/2021 | 06/05/2021   |
+----+------------+--------------+

Expected Output

+----+------------+--------------+
| ID | Admit_dt   | Discharge_dt |
+----+------------+--------------+
| 1  | 12/30/2020 | 07/14/2021   |
+----+------------+--------------+
| 2  | 03/04/2021 | 03/25/2021   |
+----+------------+--------------+
| 2  | 05/01/2021 | 05/10/2021   |
+----+------------+--------------+
| 3  | 06/01/2021 | 06/05/2021   |
+----+------------+--------------+

Query I used the logic that was here But this doesn’t cover the edge case for ID 2 and 3. Also the subquery is slower when the data is huge. Is it possible to tackle this problem using LAG?

SELECT dr1.* FROM Member_Discharges dr1
INNER JOIN Member_Discharges dr2
ON dr2.ADMIT_DT> dr1.ADMIT_DT 
  and dr2.ADMIT_DT< dr1.DISCHARGE_DT

Answer

This is a type of gaps-and-islands problem. I would suggest using a cumulative max to determine when an “island” starts and then aggregate:

select id, min(admit_dt), max(discharge_dt)
from (select t.*,
             sum(case when prev_Discharge_dt >= Admit_Dt then 0 else 1 end) over (partition by id order by admit_dt, discharge_dt) as grp
      from (select t.*,
                   max(Discharge_dt) over (partition by id
                                           order by Admit_Dt, Discharge_dt
                                           rows between unbounded preceding and 1 preceding) as prev_Discharge_dt
            from t
           ) t
     ) t
group by id, grp;

Here is a db<>fiddle.

The innermost subquery is retrieving the maximum discharge date before each row. This allows you to check for an overlap. The middle subquery counts up the number of times there is no overlap — the beginning of a group. And the outer query aggregates.