Progressive account calculations (report) need advice - LAG func (?)

Question

with account as 
    (
    select 'client1' as client, to_date('09.2009' ,'MM.YYYY') as months, '09_1' as bill_num, 100 as BF_sum, 400 as Payed_SUM from dual
    union 
    select 'client1' as client, to_date('09.2009' ,'MM.YYYY') as months, '09_2' as bill_num, 150 as BF_sum, 50 as Payed_SUM  from dual 
    union
    select 'client1' as client, to_date('10.2009' ,'MM.YYYY') as months, '10_1' as bill_num, 150 as BF_sum, 50 as Payed_SUM  from dual
    union
    select 'client1' as client, to_date('11.2009' ,'MM.YYYY') as months, '11_1' as bill_num, 150 as BF_sum, 0 as Payed_SUM  from dual
    union
    select 'client1' as client, to_date('12.2009' ,'MM.YYYY') as months, '12_1' as bill_num, 150 as BF_sum, 100 as Payed_SUM  from dual
    )
    select client, months, BF_sum, Payed_SUM, KREDIT, KREDIT+lag( KREDIT,1, 0 ) over (partition by client order by months) as lead_val
    from (
    select client, months, sum(BF_sum) as BF_sum, sum(Payed_SUM) as Payed_SUM, ( sum(Payed_SUM)  - sum(BF_sum)) as KREDIT 
    from account 
    group by client, months
    )
    order by client, months

As You see I got the following if using LAG func:

CLIENT  MONTHS              BF_SUM PAYED_SUM KREDIT LEAD_VAL
client1 01.09.2009 0:00:00 250 450 200 200
client1 01.10.2009 0:00:00 150 50 -100 100
client1 01.11.2009 0:00:00 150 0 -150 -250
client1 01.12.2009 0:00:00 150 100 -50 -200

And I need to use SUM from 09.2009 (Kredit = 200 ) to pay for 10.2009 Bills and other bills if Money still left. so i'd like the result:

client   months    BF_SUM  PAYED_SUM  KREDIT     NEW_KREDIT_MONTHS
client1 '09.2009'   250    450          200       0
client1 '10.2009'   150    50           -100      0  
client1 '11.2009'   150    0            -150      -50
client1 '12.2009'   150    100          -50      -50

Perhaps I need Connect by to "cycle" through all not payed sums until Kredit is over???

Any thoughts, guys?

Answer 1

I'm not entirely sure I understand your business logic. This gives the result in your example though.

with account as 
    (
    select 'client1' as client, to_date('09.2009' ,'MM.YYYY') as months, '09_1' as bill_num, 100 as BF_sum, 400 as Payed_SUM from dual
    union 
    select 'client1' as client, to_date('09.2009' ,'MM.YYYY') as months, '09_2' as bill_num, 150 as BF_sum, 50 as Payed_SUM  from dual 
    union
    select 'client1' as client, to_date('10.2009' ,'MM.YYYY') as months, '10_1' as bill_num, 150 as BF_sum, 50 as Payed_SUM  from dual
    union
    select 'client1' as client, to_date('11.2009' ,'MM.YYYY') as months, '11_1' as bill_num, 150 as BF_sum, 0 as Payed_SUM  from dual
    union
    select 'client1' as client, to_date('12.2009' ,'MM.YYYY') as months, '12_1' as bill_num, 150 as BF_sum, 100 as Payed_SUM  from dual
    )
select client, months, kredit, amount_short - lag(amount_short,1,0) over (partition by client order by months) new_kredit_months
from (
select client, months, kredit, least(cumulative_kredit,0) amount_short
from (
select client, months,kredit,sum(kredit) over (partition by client order by months) cumulative_kredit
from (
    select client, months, sum(BF_sum) as BF_sum, sum(Payed_SUM) as Payed_SUM, ( sum(Payed_SUM)  - sum(BF_sum)) as KREDIT 
    from account 
    group by client, months
    )
   )
  )
    order by client, months

First I get a cumulative running total of kredit. Then replace positive values with 0 because I think you are only interested in seeing the negative numbers. Then calculate the amount by which the total has changed one month to the next, which is my best guess as to what you are looking for