By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE #legal_data (
ClaimID VARCHAR(20)
,dim_legalstat_key int--dimensionkey
,[order_start_date] DATE
,[order_end_date] DATE
,[days_committed] int----days between order_start_date & order_end_date
);
INSERT INTO #legal_data VALUES
('1001','11','2022-05-11','2022-10-29','171')
,('1001','131','2022-07-15','2023-03-19','247')
,('1001','116','2023-03-14','2023-03-20','6')
,('1001','11','2023-03-20','2023-03-23','3')
,('1207','11','2022-09-13','2023-03-12','180')
,('1207','11','2023-03-10','2023-03-23','13')
,('1924','2','2021-12-18','2022-06-19','183')
,('1924','2','2022-06-19','2023-12-20','184')
,('1842','77','2021-02-20','2022-06-17','482')
,('1842','77','2022-06-18','2023-12-20','550')
,('2925','5','2022-12-10','2022-12-20','10')
,('2925','5','2022-12-15','2022-12-18','3')
,('2925','5','2022-12-19','2022-12-29','10')
;
------desired result
CREATE TABLE #legal_Result (
ClaimID VARCHAR(20)
,dim_legalstat_key int--dimensionkey
,[order_start_date] DATE
,[order_end_date] DATE
,[days_committed] int----days between order_start_date & order_end_date
);
INSERT INTO #legal_Result
VALUES
('1001','11','2022-05-11','2022-10-29','171')
,('1001','131','2022-07-15','2023-03-29','247')
,('1001','116','2023-03-14','2023-03-20','6')
,('1001','11','2023-03-20','2023-03-23','3')
| ClaimID | dim_legalstat_key | order_start_date | order_end_date | days_committed | rn |
|---|---|---|---|---|---|
| 1001 | 11 | 2022-05-11 | 2022-10-29 | 171 | 1 |
| 1001 | 131 | 2022-07-15 | 2023-03-19 | 247 | 2 |
| 1001 | 116 | 2023-03-14 | 2023-03-20 | 6 | 3 |
| 1001 | 11 | 2023-03-20 | 2023-03-23 | 3 | 4 |
| 1207 | 11 | 2022-09-13 | 2023-03-12 | 180 | 1 |
| 1207 | 11 | 2023-03-10 | 2023-03-23 | 13 | 2 |
| 1842 | 77 | 2021-02-20 | 2022-06-17 | 482 | 1 |
| 1842 | 77 | 2022-06-18 | 2023-12-20 | 550 | 2 |
| 1924 | 2 | 2021-12-18 | 2022-06-19 | 183 | 1 |
| 1924 | 2 | 2022-06-19 | 2023-12-20 | 184 | 2 |
| 2925 | 5 | 2022-12-10 | 2022-12-20 | 10 | 1 |
| 2925 | 5 | 2022-12-15 | 2022-12-18 | 3 | 2 |
| 2925 | 5 | 2022-12-19 | 2022-12-29 | 10 | 3 |
| ClaimID | dim_legalstat_key | order_start_date | order_end_date | days_committed |
|---|---|---|---|---|
| 1001 | 11 | 2022-05-11 | 2022-10-29 | 171 |
| 1001 | 131 | 2022-07-15 | 2023-03-29 | 247 |
| 1001 | 116 | 2023-03-14 | 2023-03-20 | 6 |
| 1001 | 11 | 2023-03-20 | 2023-03-23 | 3 |
| 1207 | 11 | 2022-09-13 | 2023-03-23 | 191 |
| 1924 | 2 | 2021-12-18 | 2023-12-20 | 732 |
| 1842 | 77 | 2021-02-20 | 2023-12-20 | 1033 |
with ndata as(
select ClaimID,dim_legalstat_key,order_start_date
,max(order_end_date)order_end_date
,datediff(d,order_start_date,max(order_end_date))days_committed
,row_number()over(partition by ClaimId order by order_start_date) rn
from #legal_data
group by ClaimID,dim_legalstat_key,order_start_date
)
,r as(
select rn headrow,rn,1 lvl,ClaimID,dim_legalstat_key
,order_start_date,order_end_date,days_committed
,cast(rn as varchar(1000)) rowlist
from ndata t1
where not exists(
select * from ndata t2
where t2.ClaimId=t1.ClaimId and t2.rn<>t1.rn
and t2.dim_legalstat_key=t1.dim_legalstat_key
and
(
( t2.order_start_date<=t1.order_start_date
and t2.order_end_date>=t1.order_start_date
)
or
(
dateadd(day,1,t2.order_end_date)=t1.order_start_date
)
)
)
union all
select r.headrow,t2.rn,r.lvl+1 lvl,r.ClaimID,t2.dim_legalstat_key
,case when r.order_start_date<t2.order_start_date then r.order_start_date
else t2.order_start_date end order_start_date
,case when r.order_end_date>t2.order_end_date then r.order_end_date
else t2.order_end_date end order_end_date
,r.days_committed
,cast(concat(r.rowlist,',',cast(t2.rn as varchar)) as varchar(1000))rowlist
| ClaimID | dim_legalstat_key | order_start_date | order_end_date | days_committed |
|---|---|---|---|---|
| 1001 | 11 | 2022-05-11 | 2022-10-29 | 171 |
| 1001 | 11 | 2023-03-20 | 2023-03-23 | 3 |
| 1001 | 116 | 2023-03-14 | 2023-03-20 | 6 |
| 1001 | 131 | 2022-07-15 | 2023-03-19 | 247 |
| 1207 | 11 | 2022-09-13 | 2023-03-23 | 191 |
| 1842 | 77 | 2021-02-20 | 2023-12-20 | 1033 |
| 1924 | 2 | 2021-12-18 | 2023-12-20 | 732 |
| 2925 | 5 | 2022-12-10 | 2022-12-29 | 19 |