Compute a Decreasing Cumulative Sum in SQL Server

What I’m trying to achieve is a cumulative sum on a non-negative column where it decreases by 1 on every row, however the result must also be non-negative.

For example, for the following table, summing over the “VALUE” column ordered by the “ID” column:

| ID | VALUE    |
-----------------
|  1 |        0 |
|  2 |        0 |
|  3 |        2 |
|  4 |        0 |
|  5 |        0 |
|  6 |        3 |
|  7 |        0 |
|  8 |        2 |
|  9 |        0 |
| 10 |        0 |
| 11 |        0 |
| 12 |        0 |

I expect:

| ID | VALUE    | SUM   |
-------------------------
|  1 |        0 |     0 |
|  2 |        0 |     0 |
|  3 |        2 |     2 |
|  4 |        0 |     1 |
|  5 |        0 |     0 |
|  6 |        3 |     3 |
|  7 |        0 |     2 |
|  8 |        2 |     3 |
|  9 |        0 |     2 |
| 10 |        0 |     1 |
| 11 |        0 |     0 |
| 12 |        0 |     0 |

Answer

Your question is not very well described. My best interpretation is that you want to count down from positive value, starting over when you hit the next one.

You can define the groups with a cumulative sum of the non-zero values. Then use a cumulative sum on the groups:

select t.*,
       (case when max(value) over (partition by grp) < row_number() over (partition by grp order by id) - 1
             then 0
             else (max(value) over (partition by grp) - 
                   (row_number() over (partition by grp order by id) - 1)
                  )
        end) as my_value
from (select t.*,
             sum(case when value <> 0 then 1 else 0 end) over (order by id) as grp
      from t
     ) t

Here is a db<>fiddle.

EDIT:

It strikes me that you might want to keep all the “positive” values and count down — remembering if they don’t go down to zero. Alas, I think the simplest method is a recursive CTE in this case:

with tn as (
      select t.id, t.value, row_number() over (order by id) as seqnum
      from t
     ),
     cte as (
      select tn.id, tn.value, tn.seqnum, tn.value as s
      from tn
      where id = 1
      union all
      select tn.id, tn.value, tn.seqnum,
             (case when cte.s = 0
                   then tn.value
                   when tn.value = 0 and cte.s > 0
                   then cte.s - 1
                   --  when tn.value > 0 and cte.value > 0
                   else tn.value + cte.s - 1
              end)
      from cte join
           tn 
           on tn.seqnum = cte.seqnum + 1
     )
select *
from cte;

The db<>fiddle has both solutions.