Oracle – Running Accumulative Sum with Reset

oracleoracle-11g-r2

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  |         |
|      1725 |       3 | 300.000  |         |
|      1725 |       4 | 300.000  |         |
|      1725 |       5 | -143.040 | -143.04 |
|      1725 |       6 | 300.000  |         |
|      1725 |       7 | -144.420 | -144.42 |
|      1725 |       8 | -166.320 | -310.74 |
|      1725 |       9 | 300.000  |         |
|      1725 |      10 | 300.000  |         |
|      1725 |      11 | -157.410 | -157.41 |
|      1725 |      12 | -255.940 | -413.35 |
|      1725 |      13 | -146.200 | -559.55 |
|      1725 |      14 | -28.980  | -588.53 |
|      1725 |      15 | -171.860 | -760.39 |
+-----------+---------+----------+---------+

The problem With this Query is that it does cater for the running sum of previous row.
This is what I want to achieve: 

+-----------+---------+----------+---------+
| CACCSERNO | CYCLEAGE|  AMOUNT  | RUNNING |
+-----------+---------+----------+---------+
|      1725 |       1 | -154.910 | -154.91 |
|      1725 |       2 | 300.000  | 0       |
|      1725 |       3 | 300.000  | 0       |
|      1725 |       4 | 300.000  | 0       |
|      1725 |       5 | -143.040 | -143.04 |
|      1725 |       6 | 300.000  | 0       |
|      1725 |       7 | -144.420 | -144.42 |
|      1725 |       8 | -166.320 | -310.74 |
|      1725 |       9 | 300.000  | -10.74  |
|      1725 |      10 | 300.000  | 0       |
|      1725 |      11 | -157.410 | -157.41 |
|      1725 |      12 | -255.940 | -413.35 |
|      1725 |      13 | -146.200 | -559.55 |
|      1725 |      14 | -28.980  | -588.53 |
|      1725 |      15 | -171.860 | -760.39 |
+-----------+---------+----------+---------+

As you can see, row 9 is now set a -10.74 (300+(-310.74)
Anyone have any good advice?
Thanks!

Add:

Sample to replicate:

     -- Create table
create table TEMP_UNPAIDINTEREST
(
  Trxnserno          NUMBER(10), 
  caccserno          NUMBER(10),
  trxngroup          VARCHAR2(50),
  reportingmonthyear DATE,
  reportingyear      NUMBER(4),
  amount             NUMBER(16,3),
  Cycleage           NUMBER(3)
)

  insert into TEMP_UNPAIDINTEREST (trxnserno, caccserno, trxngroup, reportingmonthyear, reportingyear, amount, cycleage)
values (34946377, 1725, 'Renter              ', to_date('30-01-2015 22:40:39', 'dd-mm-yyyy hh24:mi:ss'), 2015, -154.91, 1);
insert into TEMP_UNPAIDINTEREST (trxnserno, caccserno, trxngroup, reportingmonthyear, reportingyear, amount, cycleage)
values (34244868, 1725, 'Innbetalinger       ', to_date('16-01-2015 17:12:23', 'dd-mm-yyyy hh24:mi:ss'), 2015, 300, 2);
insert into TEMP_UNPAIDINTEREST (trxnserno, caccserno, trxngroup, reportingmonthyear, reportingyear, amount, cycleage)
values (35861740, 1725, 'Innbetalinger       ', to_date('16-02-2015 17:58:02', 'dd-mm-yyyy hh24:mi:ss'), 2015, 300, 3);
insert into TEMP_UNPAIDINTEREST (trxnserno, caccserno, trxngroup, reportingmonthyear, reportingyear, amount, cycleage)
values (36509671, 1725, 'Renter              ', to_date('27-02-2015 22:42:55', 'dd-mm-yyyy hh24:mi:ss'), 2015, -143.04, 4);
insert into TEMP_UNPAIDINTEREST (trxnserno, caccserno, trxngroup, reportingmonthyear, reportingyear, amount, cycleage)
values (37399685, 1725, 'Innbetalinger       ', to_date('16-03-2015 18:03:36', 'dd-mm-yyyy hh24:mi:ss'), 2015, 300, 5);
insert into TEMP_UNPAIDINTEREST (trxnserno, caccserno, trxngroup, reportingmonthyear, reportingyear, amount, cycleage)
values (38433101, 1725, 'Renter              ', to_date('01-04-2015 22:42:21', 'dd-mm-yyyy hh24:mi:ss'), 2015, -166.32, 6);
insert into TEMP_UNPAIDINTEREST (trxnserno, caccserno, trxngroup, reportingmonthyear, reportingyear, amount, cycleage)
values (39309923, 1725, 'Innbetalinger       ', to_date('16-04-2015 17:22:21', 'dd-mm-yyyy hh24:mi:ss'), 2015, 150, 7);
insert into TEMP_UNPAIDINTEREST (trxnserno, caccserno, trxngroup, reportingmonthyear, reportingyear, amount, cycleage)
values (40163913, 1725, 'Renter              ', to_date('30-04-2015 22:41:49', 'dd-mm-yyyy hh24:mi:ss'), 2015, -144.42, 8);
insert into TEMP_UNPAIDINTEREST (trxnserno, caccserno, trxngroup, reportingmonthyear, reportingyear, amount, cycleage)
values (41244044, 1725, 'Innbetalinger       ', to_date('18-05-2015 17:28:46', 'dd-mm-yyyy hh24:mi:ss'), 2015, 300, 9);
insert into TEMP_UNPAIDINTEREST (trxnserno, caccserno, trxngroup, reportingmonthyear, reportingyear, amount, cycleage)
values (43464530, 1725, 'Innbetalinger       ', to_date('19-06-2015 17:16:32', 'dd-mm-yyyy hh24:mi:ss'), 2015, 300, 10);
insert into TEMP_UNPAIDINTEREST (trxnserno, caccserno, trxngroup, reportingmonthyear, reportingyear, amount, cycleage)
values (42188123, 1725, 'Renter              ', to_date('01-06-2015 22:38:42', 'dd-mm-yyyy hh24:mi:ss'), 2015, -157.41, 11);
insert into TEMP_UNPAIDINTEREST (trxnserno, caccserno, trxngroup, reportingmonthyear, reportingyear, amount, cycleage)
values (44647431, 1725, 'Renter              ', to_date('31-07-2015 10:36:24', 'dd-mm-yyyy hh24:mi:ss'), 2015, -28.98, 12);
insert into TEMP_UNPAIDINTEREST (trxnserno, caccserno, trxngroup, reportingmonthyear, reportingyear, amount, cycleage)
values (44210730, 1725, 'Renter              ', to_date('01-07-2015 22:38:18', 'dd-mm-yyyy hh24:mi:ss'), 2015, -146.2, 13);
insert into TEMP_UNPAIDINTEREST (trxnserno, caccserno, trxngroup, reportingmonthyear, reportingyear, amount, cycleage)
values (45040040, 1725, 'Renter              ', to_date('20-08-2015 11:38:54', 'dd-mm-yyyy hh24:mi:ss'), 2015, -255.94, 14);
insert into TEMP_UNPAIDINTEREST (trxnserno, caccserno, trxngroup, reportingmonthyear, reportingyear, amount, cycleage)
values (45521821, 1725, 'Renter              ', to_date('06-10-2015 14:33:46', 'dd-mm-yyyy hh24:mi:ss'), 2015, -171.86, 15);

Best Answer

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.

Related Question