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-01', 0),
(117, '2024-02-04', 0),
(117, '2024-02-11', 0),
(117, '2024-03-04', 1),
(118, '2024-01-02', 1),
(118, '2024-01-28', 0),
(118, '2024-02-04', 1),
(118, '2024-02-18', 0),
(118, '2024-03-11', 1),
(118, '2024-06-05', 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-01 | 0 | 0 |
| 117 | 2024-02-04 | 0 | 0 |
| 117 | 2024-02-11 | 0 | 0 |
| 117 | 2024-03-04 | 1 | 1 |
| 118 | 2024-01-02 | 1 | 1 |
| 118 | 2024-01-28 | 0 | 0 |
| 118 | 2024-02-04 | 1 | 1 |
| 118 | 2024-02-18 | 0 | 0 |
| 118 | 2024-03-11 | 1 | 1 |
| 118 | 2024-06-05 | 1 | 1 |
Warning: Null value is eliminated by an aggregate or other SET operation.