Running / Accumulative Sum with Reset - Oracle

I am trying to accumulate a sum, but the sum accumulated cannot be positive. So far i got the following:

```
Select t.caccserno,t.Reportingmonthyear,t.Amount,t.cycleage
from temp_UnpaidInterest t where caccserno = 1725

+-----------+-----------------------+----------+---------+
| CACCSERNO | REPORTINGMONTHYEAR | AMOUNT | CYCLEAGE|
+-----------+-----------------------+----------+---------+
| 1725 | 1/30/2015 10:40:39 PM | -154.910 | 1 |
| 1725 | 1/16/2015 5:12:23 PM | 300.000 | 2 |
| 1725 | 3/16/2015 6:03:36 PM | 300.000 | 3 |
| 1725 | 2/16/2015 5:58:02 PM | 300.000 | 4 |
| 1725 | 2/27/2015 10:42:55 PM | -143.040 | 5 |
| 1725 | 4/16/2015 5:22:21 PM | 300.000 | 6 |
| 1725 | 4/30/2015 10:41:49 PM | -144.420 | 7 |
| 1725 | 4/1/2015 10:42:21 PM | -166.320 | 8 |
| 1725 | 6/19/2015 5:16:32 PM | 300.000 | 9 |
| 1725 | 5/18/2015 5:28:46 PM | 300.000 | 10 |
| 1725 | 6/1/2015 10:38:42 PM | -157.410 | 11 |
| 1725 | 8/20/2015 11:38:54 AM | -255.940 | 12 |
| 1725 | 7/1/2015 10:38:18 PM | -146.200 | 13 |
| 1725 | 7/31/2015 10:36:24 AM | -28.980 | 14 |
| 1725 | 10/6/2015 2:33:46 PM | -171.860 | 15 |
+-----------+-----------------------+----------+---------+

select caccserno, cycleage, Amount,
sum(decode(sign(Amount),-1,Amount,null))
over (partition by Caccserno, max_cycle order by cycleage) running
from (
select Caccserno, cycleage, Amount,
max( decode(sign(Amount),1,cycleage,decode(cycleage,1,1,null)) )
over (partition by Caccserno order by cycleage) max_cycle
from temp_UnpaidInterest where caccserno =1725)

+-----------+---------+----------+---------+
| CACCSERNO | CYCLEAGE| AMOUNT | RUNNING |
+-----------+---------+----------+---------+
| 1725 | 1 | -154.910 | -154.91 |
| 1725 | 2 | 300.000 | |
|

A solution using recursive CTE. It assumes that the CYCLEAGE values start from 1 and increase by 1, without gaps:

with cte (CYCLEAGE, AMOUNT, RUNNING) as
  ( select 
        CYCLEAGE, AMOUNT,
        least(0, AMOUNT)
    from TEMP_UNPAIDINTEREST
    where CACCSERNO = 1725
      and CYCLEAGE = 1
  union all
    select 
        t.CYCLEAGE, t.AMOUNT,
        least(0, t.AMOUNT + cte.RUNNING)
    from cte
      join TEMP_UNPAIDINTEREST  t
        on  t.CACCSERNO = cte.CACCSERNO
        and t.CYCLEAGE = cte.CYCLEAGE + 1  
  )
select *
from cte
order by cycleage ;

Tested at SQLfiddle.

StackExchange Database Administrators Q#123306, answer score: 2