Running / Accumulative Sum with Reset - Oracle

Question 1

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);

Question 2

Should it be 0 when/If the amount or if the cumulative sum (carryover) becomes positive?

Question 3

When the cumulative sum becomes positive.

Question 4

do you have some create tables with test data so we can run your sample and tweak things? Please ? Pretty please? :)

Question 5

Modified original post. Please see above.

Question 6

Try this and see if it makes sense,

with x as (select CACCSERNO, AMOUNT, REPORTINGMONTHYEAR, COUNTER, case when amount < 0 then 0 else 1 end grp ,SUM(amount) over(partition by case when amount < 0 then 0 else 1 end order by counter) tot from TEMP_UNPAIDINTEREST) select CACCSERNO, AMOUNT, REPORTINGMONTHYEAR, COUNTER, case when grp = 0 then tot else 0 end carryover from x;

Question 7

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.

Question 8

@user3651825 Note that in the fiddle I modified your INSERT values a bit because they contradicted the displayed values in the question.

ypercubeTM ypercubeTM 99.7k13 gold badges217 silver badges306 bronze badges · Accepted Answer · 2015-12-09 20:49:38Z

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.

@user3651825 Note that in the fiddle I modified your INSERT values a bit because they contradicted the displayed values in the question.

Stack Exchange Network

Running / Accumulative Sum with Reset - Oracle

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Hot Network Questions

Running / Accumulative Sum with Reset - Oracle

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Related

Hot Network Questions