SQL Script that will adjust the value of the next row when it finds a negative

Question

Is there a plain SQL script that will adjust the value from a table when it finds a negative until it consumes the negative values?

like for example>

0
,1952
,0
,0
,0
,0
,0
,98
,-333
,98
,98
,98
,165
,113
,113
,113
,138
,65
,65
,115

there is a -333 in the list so the result will be like this

0
,1952
,0
,0
,0
,0
,0
,98
,-333
,-235
,-137
,-39
,126
,113
,113
,113
,138
,65
,65
115

as you can see 165 is now 126, since it consumes the negative value, all the next value remain as-is

Thanks in advance.

Answer 1

Assuming you have some kind of sort column, you can use a recursive CTE to calculate the values:

WITH data AS (
SELECT  *
FROM
    (
    VALUES  (0, 1)
    ,   (1952, 2)
    ,   (0, 3)
    ,   (0, 4)
    ,   (0, 5)
    ,   (0, 6)
    ,   (0, 7)
    ,   (98, 8)
    ,   (-333, 9)
    ,   (98, 10)
    ,   (98, 11)
    ,   (98, 12)
    ,   (165, 13)
    ,   (113, 14)
    ,   (113, 15)
    ,   (113, 16)
    ,   (138, 17)
    ,   (65, 18)
    ,   (65, 19)
    ,   (115, 20)
) t (value,sort)
)
, agg AS (
    SELECT  value, sort, CASE WHEN value > 0 THEN 0 ELSE 1 END AS flag, value AS agg_value
    FROM    data
    WHERE   sort = 1
    UNION ALL
    SELECT  d.value, d.sort,CASE WHEN value_new > 0 THEN 0 ELSE 1 END 
    ,   value_new
    FROM    agg a
    INNER JOIN data d
        ON  d.sort = a.sort + 1
    CROSS apply (
        SELECT  CASE WHEN agg_value < 0 THEN a.agg_value + d.value ELSE d.value END AS value_new
        ) f
    )
SELECT  sort, value as orig, agg_value as new
FROM    agg

To keep track of things, i create a flag that changes depending if value is negative or positive, and then apply the strange logic you're using by adding the values until they pass 0.

The output becomes:

sort	orig	new
1	0	0
2	1952	1952
3	0	0
4	0	0
5	0	0
6	0	0
7	0	0
8	98	98
9	-333	-333
10	98	-235
11	98	-137
12	98	-39
13	165	126
14	113	113
15	113	113
16	113	113
17	138	138
18	65	65
19	65	65
20	115	115