By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select *
into t
from (values
(1, convert(date, '2020/12/30'), convert(date, '2021/07/14')),
(1, convert(date, '2021/01/02'), convert(date, '2021/07/14')),
(1, convert(date, '2021/06/16'), convert(date, '2021/07/14')),
(2, convert(date, '2021/03/04'), convert(date, '2021/03/25')),
(2, convert(date, '2021/05/01'), convert(date, '2021/05/10')),
(3, convert(date, '2021/06/01'), convert(date, '2021/06/05'))
) v(ID, Admit_Dt, Discharge_Dt )
6 rows affected
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;
| id | (No column name) | (No column name) |
|---|---|---|
| 1 | 2020-12-30 | 2021-07-14 |
| 2 | 2021-03-04 | 2021-03-25 |
| 3 | 2021-06-01 | 2021-06-05 |
| 2 | 2021-05-01 | 2021-05-10 |