Comparing two running total and changing the value of one in sql

Question

I have looked into running total problems/questions asked here but could not find the similar one. Im using sql developer, and have select statement privileges, do not have access to cursors or loops, or creating functions.

I have a table with two columns requiring running total:

with my_table as
(
select 673 as customer, to_date('30.06.2021','dd.mm.yyyy') as report_date,210 as fee,210 as commission from dual union all
select 673 as customer, to_date('31.07.2021','dd.mm.yyyy') as report_date,210 as fee,0 as commission from dual union all
select 673 as customer, to_date('31.08.2021','dd.mm.yyyy') as report_date,210 as fee,210 as commission from dual union all
select 673 as customer, to_date('31.10.2021','dd.mm.yyyy') as report_date,210 as fee,310 as commission from dual union all
select 673 as customer, to_date('30.11.2021','dd.mm.yyyy') as report_date,210 as fee,210 as commission from dual union all
select 673 as customer, to_date('31.12.2021','dd.mm.yyyy') as report_date,210 as fee,0  as commission from dual union all
select 673 as customer, to_date('31.01.2022','dd.mm.yyyy') as report_date,210 as fee, 943.08 as commission from dual union all
select 673 as customer, to_date('28.02.2022','dd.mm.yyyy') as report_date,320 as fee,236.6 as commission from dual union all
select 673 as customer, to_date('31.03.2022','dd.mm.yyyy') as report_date,320 as fee,0 as commission from dual union all
select 673 as customer, to_date('30.04.2022','dd.mm.yyyy') as report_date,320 as fee,0 as commission from dual union all
select 673 as customer, to_date('31.05.2022','dd.mm.yyyy') as report_date,320 as fee,0 as commission from dual union all
select 673 as customer, to_date('30.06.2022','dd.mm.yyyy') as report_date,320 as fee,0 as commission from dual union all
select 673 as customer, to_date('31.07.2022','dd.mm.yyyy') as report_date,320 as fee,0 as commission from dual union all
select 673 as customer, to_date('31.08.2022','dd.mm.yyyy') as report_date,320 as fee,0 as commission from dual
)

I have to calculate running total for both fee and commission columns. For fee column, there is no rule or condition. The basic sum over with partition function is adequate. However when it comes to commission, I have to look out for running total of fee.

Every running_com value must be compared to the that of running_fee. If running_com exceed running_fee it should be replaced with running_fee on that row, and for the next row, cumulative total for commission should start with that value. here is the table and expected result:

Customer	Report_date	Fee	Commission	Running_fee	Running_com
673	30.06.2021	210	210	210	210
673	31.07.2021	210	0	420	210
673	31.08.2021	210	210	630	420
673	31.10.2021	210	310	840	730
673	30.11.2021	210	210	1050	940
673	31.12.2021	210	0	1260	940
673	31.01.2022	210	943.08	1470	1470
673	28.02.2022	320	236.6	1790	1706.6
673	31.03.2022	320	0	2110	1706.6
673	30.04.2022	320	0	2430	1706.6
673	31.05.2022	320	0	2750	1706.6
673	36.06.2022	320	0	3070	1706.6
673	31.07.2022	320	0	3390	1706.6
673	31.08.2022	320	0	3710	1706.6

I have put the previous commission value using lag, then try to sum commission value and previous commisssion but could not manage to, so to say looping part. it just summed up without running part.

Thanks for help.

Answer 1

I don't think that this can be done with window functions only. One way or another, we need some kind of iteration over the dataset, so we can take the proper decision as regard to the cumulative commission.

In SQL, this is usually done with a recursive query. Here is the logic we could follow:

with  
    dat (customer, report_date, fee, commission, seq, r_fee) as (
        select customer, report_date, fee, commission,
            row_number() over(partition by customer order by report_date),
            sum(fee)     over(partition by customer order by report_date)
        from my_table
    ),
    rec (customer, report_date, fee, commission, seq, r_fee, r_commission) as (
        select d.customer, d.report_date, d.fee, d.commission, d.seq, d.r_fee, commission 
        from dat d 
        where seq = 1
        union all
        select d.customer, d.report_date, d.fee, d.commission, d.seq, d.r_fee, 
            least(r.r_commission + d.commission, d.r_fee) 
        from rec r
        inner join dat d on d.customer = r.customer and d.seq = r.seq + 1 
    )
select * from rec order by customer, report_date

The first common table expression (dat) just enumerates the rows of each customer (seq), and directly computes the running fee (r_fee).

The second CTE, rec, does the iteration, using the seq to jump from one step to the next ; at each step, the running commission is updated according to the business rule.

Here is a demo on DB Fiddle with your sample data, which yields:

CUSTOMER	REPORT_DATE	FEE	COMMISSION	SEQ	R_FEE	R_COMMISSION
673	30-JUN-21	210	210	1	210	210
673	31-JUL-21	210	0	2	420	210
673	31-AUG-21	210	210	3	630	420
673	31-OCT-21	210	310	4	840	730
673	30-NOV-21	210	210	5	1050	940
673	31-DEC-21	210	0	6	1260	940
673	31-JAN-22	210	943.08	7	1470	1470
673	28-FEB-22	320	236.6	8	1790	1706.6
673	31-MAR-22	320	0	9	2110	1706.6
673	30-APR-22	320	0	10	2430	1706.6
673	31-MAY-22	320	0	11	2750	1706.6
673	30-JUN-22	320	0	12	3070	1706.6
673	31-JUL-22	320	0	13	3390	1706.6
673	31-AUG-22	320	0	14	3710	1706.6