By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @T TABLE (ID INT, [Date] DATE, expected INT)
INSERT @T VALUES (117, '2023-11-14', 1),
(117, '2024-01-25', 1),
(117, '2024-02-16', 0),
(117, '2024-03-04', 1),
(117, '2024-03-26', 0),
(117, '2024-04-04', 1);
;with cte as
(
select id, date, expected, datediff(dd,lag(date) over(partition by id order by date), date) as datedif
from @t
)
, cte2 as
(
select *
, sum(datedif) over(partition by id order by date) as dd_sum
, isnull(floor(sum(datedif) over(partition by id order by date) / 30.0),0) as counter
from cte
)
select id, date, expected
, case when counter <> prior_counter then 1 else 0 end as flag
from (
select *
, isnull(lag(counter) over(partition by id order by date),-1) as prior_counter
from cte2
) a
| id | date | expected | flag |
|---|---|---|---|
| 117 | 2023-11-14 | 1 | 1 |
| 117 | 2024-01-25 | 1 | 1 |
| 117 | 2024-02-16 | 0 | 1 |
| 117 | 2024-03-04 | 1 | 0 |
| 117 | 2024-03-26 | 0 | 1 |
| 117 | 2024-04-04 | 1 | 0 |
Warning: Null value is eliminated by an aggregate or other SET operation.