By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table mytable (
[date] date,
unit varchar(20),
status int
);
insert into mytable values
('2023-04-30', 'unit1', 1),
('2023-05-31', 'unit1', 1),
('2023-06-30', 'unit1', 1),
('2023-08-30', 'unit1', 1),
('2023-09-30', 'unit1', 1),
('2023-12-30', 'unit2', 1),
('2024-01-30', 'unit2', 1);
7 rows affected
with cte as (
SELECT *, GroupingSet = FORMAT(DATEADD(
MONTH, - ROW_NUMBER() OVER(PARTITION BY unit ORDER BY [date]),
[date]
), 'yyyy-MM-01')
FROM mytable
WHERE [date] > EOMONTH('2023-05-15') AND [status] = 1
),
cte2 as (
SELECT unit,
StartDate = MIN([date]),
EndDate = MAX([date]),
streak = COUNT(*)
FROM CTE
GROUP BY unit, GroupingSet
),
cte3 as (
select *, row_number() over (partition by unit order by streak desc) as rn
from cte2
)
select unit, StartDate, EndDate, streak
from cte3
where rn = 1
| unit | StartDate | EndDate | streak |
|---|---|---|---|
| unit1 | 2023-08-30 | 2023-09-30 | 2 |
| unit2 | 2023-12-30 | 2024-01-30 | 2 |