By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE #t (PackedStr VARCHAR(100), Ident INT, BeginDate DATE, EndDate DATE);
INSERT #t
VALUES ('A,B,C,D,E', 86, '2019-03-18', '2019-03-27')
, ('A,B,C,D,E', 87, '2019-03-28', '2019-04-09')
, ('A,B,C,D,E,F,G', 88, '2019-04-10', '2019-04-15')
, ('A,B,C,D,E', 89, '2019-04-16', '2019-04-24')
, ('A,B,C,D,E', 90, '2019-04-25', '2019-05-14');
SELECT *
, ROW_NUMBER() OVER (PARTITION BY [PackedStr] ORDER BY [Ident]) RowNumber
, MIN( BeginDate ) OVER (PARTITION BY [PackedStr] ORDER BY [Ident]) FirstDate
, MAX( EndDate ) OVER (PARTITION BY [PackedStr] ORDER BY [Ident] DESC) LastDate
FROM [#t]
ORDER BY [Ident];
| PackedStr | Ident | BeginDate | EndDate | RowNumber | FirstDate | LastDate |
|---|---|---|---|---|---|---|
| A,B,C,D,E | 86 | 2019-03-18 | 2019-03-27 | 1 | 2019-03-18 | 2019-05-14 |
| A,B,C,D,E | 87 | 2019-03-28 | 2019-04-09 | 2 | 2019-03-18 | 2019-05-14 |
| A,B,C,D,E,F,G | 88 | 2019-04-10 | 2019-04-15 | 1 | 2019-04-10 | 2019-04-15 |
| A,B,C,D,E | 89 | 2019-04-16 | 2019-04-24 | 3 | 2019-03-18 | 2019-05-14 |
| A,B,C,D,E | 90 | 2019-04-25 | 2019-05-14 | 4 | 2019-03-18 | 2019-05-14 |
select *
,row_number()over(partition by gapN order by Ident) rn
,MIN( BeginDate ) OVER (PARTITION BY gapN) FirstDate
,MAX( EndDate ) OVER (PARTITION BY gapN) LastDate
from(
select * ,sum(isGap)over(order by Ident)gapN
from(
select *
,case when PackedStr=lag(PackedStr)over(order by Ident) then 0 else 1 end as isGap
from #t
)a
)b
| PackedStr | Ident | BeginDate | EndDate | isGap | gapN | rn | FirstDate | LastDate |
|---|---|---|---|---|---|---|---|---|
| A,B,C,D,E | 86 | 2019-03-18 | 2019-03-27 | 1 | 1 | 1 | 2019-03-18 | 2019-04-09 |
| A,B,C,D,E | 87 | 2019-03-28 | 2019-04-09 | 0 | 1 | 2 | 2019-03-18 | 2019-04-09 |
| A,B,C,D,E,F,G | 88 | 2019-04-10 | 2019-04-15 | 1 | 2 | 1 | 2019-04-10 | 2019-04-15 |
| A,B,C,D,E | 89 | 2019-04-16 | 2019-04-24 | 1 | 3 | 1 | 2019-04-16 | 2019-05-14 |
| A,B,C,D,E | 90 | 2019-04-25 | 2019-05-14 | 0 | 3 | 2 | 2019-04-16 | 2019-05-14 |