SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select *
into t
from (values ( 1, 0),
             ( 2, 0),
             ( 3, 2),
             ( 4, 0),
             ( 5, 0),
             ( 6, 3),
             ( 7, 0),
             ( 8, 2),
             ( 9, 0),
             (10, 0),
             (11, 0),
             (12, 0)
     ) v(id, value)
12 rows affected

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


id
value
grp
my_value


1
0
0
0

2
0
0
0

3
2
1
2

4
0
1
1

5
0
1
0

6
3
2
3

7
0
2
2

8
2
3
2

9
0
3
1

10
0
3
0

11
0
3
0

12
0
3
0


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


id
value
seqnum
s


1
0
1
0

2
0
2
0

3
2
3
2

4
0
4
1

5
0
5
0

6
3
6
3

7
0
7
2

8
2
8
3

9
0
9
2

10
0
10
1

11
0
11
0

12
0
12
0