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:

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: