Running Total - Date difference

Question

This is what the table looks like:

create table IncomeTest (SubjectId int, Date_Value date, debit number, credit number);

insert into IncomeTest values (1, '7-SEP-2017', 11000, 0);
insert into IncomeTest values (1, '7-DEC-2017', 6000, 0);
insert into IncomeTest values (1, '9-JAN-2018', 0, 16110);
insert into IncomeTest values (1, '9-JUL-2018', 0, 619.6);
insert into IncomeTest values (1, '23-JUL-2018', 0, 270.4);

commit;

Debit represent cash out, credit represent cash in. After insert into table you get this data:

SubjectID | Date_value | Debit |  Credit 
    1       9/7/2017     11000         0
    1       12/7/2017     6000         0
    1       1/9/2018         0   16110.0
    1       7/9/2018         0     619.6
    1       7/23/2018        0     270.4

My problem here is that I have to calculate when the user paid off his loan and how much he/she was late with payment, which means the first debit of 11000 was paid in full on 1/9/2018: here I calculate date diff between these two dates and that's how much payment was late, but then I need to take that 11000 away from credit of 1/9/2018 and move on to the next debit and then calculate when that next debit was paid in full.

The result I am hoping to have at the end is:

SubjectID | Date_value | Debit | Credit | PaymentLate |
    1        9/7/2017    11000        0           124
    1        12/7/2017    6000        0           228

Because 1st debit was paid in full on 1/9/2018 and 2nd debit was paid in full on 7/23/2018 ... PaymentLate is the date difference between debit date_value and the date when the running total of credit reached enough money.

I did try with running total and sums, but I have problem when I start to take away debit from credit when it was paid in full ... in other words, I didn't get far.

I'm using Oracle 11.1.0.7.0 Enterprise Edition.

score 6 · Accepted Answer · answered Aug 30 '18 at 08:29

The following statement should do the trick:

WITH sum_data AS (SELECT subject_id
                       , date_value
                       , debit
                       , credit
                       , SUM(debit) over(PARTITION BY subject_id 
                                         ORDER BY date_value
                                         ROWS UNBOUNDED PRECEDING) SUM_debit
                       , SUM(credit) over(PARTITION BY subject_id 
                                          ORDER BY date_value
                                          ROWS UNBOUNDED PRECEDING) SUM_credit
                   FROM IncomeTest )
SELECT d.subject_id
     , d.date_value
     , d.debit
     , d.credit
     , GREATEST( (SELECT MIN(date_value) 
                    FROM sum_data d2
                   WHERE d2.SUM_credit >= d.sum_debit
                     AND d2.subject_id  = d.subject_id) - date_value
               ,0) PaymentLate 
  FROM sum_data d
 WHERE debit != 0

Perhaps you want to ad an additional nvl(.,sysdate) around the inner select otherwise payments that are not paid until today will be shown as NULL values... — Radagast81, Aug 30 '18 at 08:36
Impressive analytic skills and way of thinking man, thanks a million — Veljko89, Aug 30 '18 at 08:54

Running Total - Date difference

1 Answers1