By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select *
into t
from (values (1, 10, convert(date, '2020-01-01'), 'False'),
(1, 20, convert(date, '2020-01-01'), 'False'),
(1, 10, convert(date, '2020-05-01'), 'False'),
(1, 50, convert(date, '2020-06-01'), 'True'),
(1, 50, convert(date, '2020-06-01'), 'True'),
(1, 50, convert(date, '2020-06-01'), 'False'),
(2, 70, convert(date, '2020-06-01'), 'True'),
(3, 10, convert(date, '2020-07-01'), 'False')
) v(vehicleid, distance, date, alert)
8 rows affected
select t.*,
sum(distance) over (partition by vehicleid, grp
order by date
rows between unbounded preceding and current row
)
from (select t.*,
sum(case when alert = 'True' then 1 else 0 end) over
(partition by vehicleid
order by date
rows between unbounded preceding and current row
) as grp
from t
) t;
| vehicleid | distance | date | alert | grp | (No column name) |
|---|---|---|---|---|---|
| 1 | 10 | 2020-01-01 | False | 0 | 10 |
| 1 | 20 | 2020-01-01 | False | 0 | 30 |
| 1 | 10 | 2020-05-01 | False | 0 | 40 |
| 1 | 50 | 2020-06-01 | True | 1 | 50 |
| 1 | 50 | 2020-06-01 | True | 2 | 50 |
| 1 | 50 | 2020-06-01 | False | 2 | 100 |
| 2 | 70 | 2020-06-01 | True | 1 | 70 |
| 3 | 10 | 2020-07-01 | False | 0 | 10 |