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

Time:11-02

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.

CodePudding user response:

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
  • Related