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-08-31', 'unit1', 1),
('2023-09-30', 'unit1', 1),
('2023-10-31', 'unit1', 1),
('2023-11-30', 'unit1', 1),
('2023-12-30', 'unit1', 1),
('2024-01-30', 'unit1', 1);
8 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
)
SELECT TOP 1 unit,
StartDate = MIN([date]),
EndDate = MAX([date]),
streak = COUNT(*)
FROM CTE
GROUP BY unit, GroupingSet
ORDER BY StartDate;
| unit | StartDate | EndDate | streak |
|---|---|---|---|
| unit1 | 2023-08-31 | 2024-01-30 | 6 |