TSQL - how can I sum values except absolutes

Question

I would like to sum values in my table, except the ones that are absolute (field absolute, value = 1). If that's the case, the summing should reset. Example:

Date            Value      Absolute
1-1-2020        4          0    
1-2-2020        7          1    
1-3-2020        3          0

Regular SUM() would return (4+7+3=) 14. But in this example it should reset at value 7, which makes a sum of (7+3=) 10.

How can I make this work?

Answer 1

You seem to want a window sum that resets everytime absolute is 1. If so, you can do:

select t.*, sum(value) over(partition by grp order by date) sum_value
from (
    select t.*, sum(absolute) over(order by date) grp 
    from mytable t
) t

The subquery uses a window sum of absolute to define the groups, then the outer query performs the sums value over each group.