SUM OVER RESET on the previous row's SUM OVER result

Database

SUM OVER RESET on the previous row's SUM OVER result

I have a table called TEST which has the following data:

ID                    Income_Loss       Incm_Year

1,001                    50                    2011

1,001                    -100                 2012

1,001                    30                    2013

1,002                    -20                   2011

1,002                    50                    2012

1,002                    -100                 2014

1,002                    -10                   2015

1,003                    50                    2011

1,003                    100                  2015

1,007                    10,000             2000

1,007                    50                    2001

1,007                    -100                 2002

1,007                    80                    2003

1,007                    70                    2004

1,007                    100                  2005

1,007                    -200                 2006

1,007                    -300                 2007

1,007                    50                    2008

1,007                    60                    2009

1,007                    100                  2010

1,007                    110                  2011

I want to create a cumulative total of the income_loss (ordered by year) for each ID that resets when the cumulative total is positive. So the result would look something like this:

ID                    Income_Loss      Incm_Year         Cum_Total

1,001                    50                    2011                    50

1,001                    -100                 2012                    -100

1,001                    30                    2013                    -70

1,002                    -20                   2011                    -20

1,002                    50                    2012                    30

1,002                    -100                 2014                    -100

1,002                    -10                   2015                    -110

1,003                    50                    2011                    50

1,003                    100                  2015                    100

1,007                    10,000             2000                    10,000

1,007                    50                    2001                    50

1,007                    -100                 2002                    -100

1,007                    80                    2003                    -20

1,007                    70                    2004                    50

1,007                    100                  2005                    100

1,007                    -200                 2006                    -200

1,007                    -300                 2007                    -500

1,007                    50                    2008                    -450

1,007                    60                    2009                    -390

1,007                    100                  2010                    -290

1,007                    110                  2011                    -180

I've tried different things and the code below was what I thought could work except it came back with the error: SELECT Failed.  [3896] Statement caused parser stack overflow.  Any ideas would be much appreciated.

SELECT ID
, Income_Loss
, Incm_Year
, SUM(Income_Loss) OVER
(
PARTITION BY ID
ORDER BY Incm_Year
RESET WHEN
SUM(Cum_Total) OVER
(
PARTITION BY ID
ORDER BY Incm_Year
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
) > 0
ROWS UNBOUNDED PRECEDING
) AS Cum_Total
FROM TEST;
2 REPLIES
N/A

Re: SUM OVER RESET on the previous row's SUM OVER result

I don't think you can do this with a simple OLAP function, you need recursion for it (your RESET WHEN already tries a recursive definition, SUM(Cum_Total), which is no valid syntax and seems to confuse the parser).

with recursive cte as 
(
SELECT ID
, Income_Loss
, Incm_Year
, Income_Loss as Cum_Total
from TEST
qualify ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Incm_Year) = 1

union all

SELECT t.ID
, t.Income_Loss
, t.Incm_Year
, case when cte.Cum_Total < 0
then t.Income_Loss + cte.Cum_Total
else t.Income_Loss
end
from TEST as t join cte
on t.ID = cte.ID
and t.Incm_Year = cte.Incm_Year + 1
)
select * from cte

This assumes there's no gap between Incm_Years, otherwise you need to create a Volatile Table with a ROW_NUMBER and use this instead.

Re: SUM OVER RESET on the previous row's SUM OVER result

Thanks dnoeth, that worked like a charm!