Home » SQL & PL/SQL » SQL & PL/SQL » Distribute payments over due dates and amounts (11.2.1.0)
Distribute payments over due dates and amounts [message #645269] Tue, 01 December 2015 23:40 Go to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Hi All,

I have a table of dues and a table of payments. Payments can take place any time (before or after the due) and with any amount (more or less than the due balance).
I need to combine dues and payments so that the result shows the number of payment that contributed in completing each due as follows:
create table inst_due
  (due_date date,
   due_amount number
  );

create table inst_payment
  (payment_date date,
   paymnet_amount number
  );

insert all 
  into inst_due values(to_date('01-01-2010','DD-MM-YYYY'), 100)
  into inst_due values(to_date('01-06-2010','DD-MM-YYYY'), 100)
  into inst_due values(to_date('01-01-2011','DD-MM-YYYY'), 100)
  into inst_due values(to_date('01-06-2011','DD-MM-YYYY'), 100)
  into inst_due values(to_date('01-01-2012','DD-MM-YYYY'), 100)
  into inst_due values(to_date('01-06-2012','DD-MM-YYYY'), 100)
    into inst_payment values(to_date('02-01-2010','DD-MM-YYYY'), 100)
    into inst_payment values(to_date('01-06-2010','DD-MM-YYYY'), 10)
    into inst_payment values(to_date('02-07-2010','DD-MM-YYYY'), 80)
    into inst_payment values(to_date('05-08-2010','DD-MM-YYYY'), 10)
    into inst_payment values(to_date('01-01-2011','DD-MM-YYYY'), 10) 
    into inst_payment values(to_date('02-01-2011','DD-MM-YYYY'), 50)
    into inst_payment values(to_date('02-06-2011','DD-MM-YYYY'), 140)
    into inst_payment values(to_date('01-01-2012','DD-MM-YYYY'), 100)
    into inst_payment values(to_date('02-01-2012','DD-MM-YYYY'), 10)
    into inst_payment values(to_date('02-06-2012','DD-MM-YYYY'), 90)
    into inst_payment values(to_date('03-06-2012','DD-MM-YYYY'), 10)
select * from dual;


--The desired output is:
due_date    due_amount   payment_date   payment_amount status
01-01-2010  100          02-01-2010     100            FULL
01-06-2010  100          01-06-2010     10             PARTIAL
01-06-2010  100          02-07-2010     80             PARTIAL
01-06-2010  100          05-08-2010     10             BALANCE
01-01-2011  100          01-01-2011     10             PARTIAL
01-01-2011  100          01-01-2011     10             PARTIAL
01-01-2011  100          02-01-2011     50             PARTIAL
01-01-2011  100          02-06-2011     40             BALANCE
01-06-2011  100          02-06-2011     100            FULL
01-01-2012  100          01-01-2012     100            FULL
01-06-2012  100          02-01-2012     10             PARTIAL
01-06-2012  100          02-06-2012     90             BALANCE
                         03-06-2012     10             ADVANCED



Many thanks,
Ferro

Report message to a moderator

Re: Distribute payments over due dates and amounts [message #645270 is a reply to message #645269] Tue, 01 December 2015 23:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

How do you related a due and a payment?
Assume there are 2 dues ad an amount comes, which due the payment is related to?
If a payment exceeds a due or rest of a due, how do you treat it?
Can a payment be split to satisfy 2 payments or rests of payments?
...

Report message to a moderator

Re: Distribute payments over due dates and amounts [message #645272 is a reply to message #645270] Wed, 02 December 2015 00:12 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Hi Michel,

Quote:

How do you related a due and a payment?
Assume there are 2 dues ad an amount comes, which due the payment is related to?

By date and amount: A payment is related to a due that has an equal or earlier due date and still not fully paid. The payment goes to the first not fully paid due first (by due date) in case of two outstanding dues.

Quote:

Can a payment be split to satisfy 2 payments or rests of payments?

Yes,
- satisfy 2 payments: case of payment on '02-06-2011', the amount 140 pays for an unsettled previous due and fully pays the later due
- or rests of payments: yes this is the case of status = BALANCE

Many thanks,
Ferro

Report message to a moderator

Re: Distribute payments over due dates and amounts [message #645316 is a reply to message #645272] Thu, 03 December 2015 02:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

I think your duplicate following lines is a copy and paste error and there should be only one:
01-01-2011  100          01-01-2011     10             PARTIAL
01-01-2011  100          01-01-2011     10             PARTIAL


SQL> select * from inst_due order by due_date;
DUE_DATE    DUE_AMOUNT
----------- ----------
01-JAN-2010        100
01-JUN-2010        100
01-JAN-2011        100
01-JUN-2011        100
01-JAN-2012        100
01-JUN-2012        100

6 rows selected.

SQL> select * from inst_payment order by payment_date;
PAYMENT_DAT PAYMENT_AMOUNT
----------- --------------
02-JAN-2010            100
01-JUN-2010             10
02-JUL-2010             80
05-AUG-2010             10
01-JAN-2011             10
02-JAN-2011             50
02-JUN-2011            140
01-JAN-2012            100
02-JAN-2012             10
02-JUN-2012             90
03-JUN-2012             10

11 rows selected.

SQL> with
  2    due as (
  3      select due_date, due_amount,
  4             nvl(sum(due_amount) over (
  5                   order by due_date
  6                 rows between unbounded preceding and 1 preceding)
  7                , 0) prev_sum,
  8             sum(due_amount) over (order by due_date) sum_due
  9      from inst_due
 10    ),
 11    pay as (
 12      select payment_date, payment_amount,
 13             nvl(sum(payment_amount) over (
 14                   order by payment_date
 15                 rows between unbounded preceding and 1 preceding)
 16                , 0) prev_sum,
 17             sum(payment_amount) over (order by payment_date) sum_pay
 18      from inst_payment
 19    )
 20  select due_date, due_amount, payment_date, payment_amount,
 21         case
 22           when pay.sum_pay >= due.sum_due and pay.prev_sum > due.prev_sum
 23             -- BALANCE
 24             then due.sum_due - pay.prev_sum
 25           when pay.sum_pay >= due.sum_due and pay.prev_sum <= due.prev_sum
 26             -- FULL
 27             then due.sum_due - due.prev_sum
 28           else payment_amount
 29         end payment_taken,
 30         case
 31           when due.due_date is null         then 'ADVANCE'
 32           when pay.sum_pay < due.sum_due    then 'PARTIAL'
 33           when pay.prev_sum > due.prev_sum  then 'BALANCE'
 34           when pay.payment_date is not null then 'FULL'
 35           else                                   'WAITING'
 36         end status
 37  from due full outer join pay
 38       on     pay.prev_sum < due.sum_due
 39          and (   pay.prev_sum >= due.prev_sum
 40               or pay.sum_pay >= due.sum_due )
 41  order by due_date, payment_date
 42  /
DUE_DATE    DUE_AMOUNT PAYMENT_DAT PAYMENT_AMOUNT PAYMENT_TAKEN STATUS
----------- ---------- ----------- -------------- ------------- -------
01-JAN-2010        100 02-JAN-2010            100           100 FULL
01-JUN-2010        100 01-JUN-2010             10            10 PARTIAL
01-JUN-2010        100 02-JUL-2010             80            80 PARTIAL
01-JUN-2010        100 05-AUG-2010             10            10 BALANCE
01-JAN-2011        100 01-JAN-2011             10            10 PARTIAL
01-JAN-2011        100 02-JAN-2011             50            50 PARTIAL
01-JAN-2011        100 02-JUN-2011            140            40 BALANCE
01-JUN-2011        100 02-JUN-2011            140           100 FULL
01-JAN-2012        100 01-JAN-2012            100           100 FULL
01-JUN-2012        100 02-JAN-2012             10            10 PARTIAL
01-JUN-2012        100 02-JUN-2012             90            90 BALANCE
                       03-JUN-2012             10            10 ADVANCE

12 rows selected.

[Updated on: Thu, 03 December 2015 02:30]

Report message to a moderator

Re: Distribute payments over due dates and amounts [message #645318 is a reply to message #645316] Thu, 03 December 2015 02:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Note that if the last due is partially paid the query does not mention the amount still due; for example removing the last 2 payments:
SQL> delete inst_payment where payment_date>=to_date('02-JUN-2012');

2 rows deleted.

SQL> with
...
 42  /
DUE_DATE    DUE_AMOUNT PAYMENT_DAT PAYMENT_AMOUNT PAYMENT_TAKEN STATUS
----------- ---------- ----------- -------------- ------------- -------
01-JAN-2010        100 02-JAN-2010            100           100 FULL
01-JUN-2010        100 01-JUN-2010             10            10 PARTIAL
01-JUN-2010        100 02-JUL-2010             80            80 PARTIAL
01-JUN-2010        100 05-AUG-2010             10            10 BALANCE
01-JAN-2011        100 01-JAN-2011             10            10 PARTIAL
01-JAN-2011        100 02-JAN-2011             50            50 PARTIAL
01-JAN-2011        100 02-JUN-2011            140            40 BALANCE
01-JUN-2011        100 02-JUN-2011            140           100 FULL
01-JAN-2012        100 01-JAN-2012            100           100 FULL
01-JUN-2012        100 02-JAN-2012             10            10 PARTIAL

10 rows selected.

But if a due is still completely due, its status is "WAITING"; for example removing the now last payment:
SQL> delete inst_payment where payment_date=to_date('02-JAN-2012');

1 row deleted.

SQL> with
...
 42  /
DUE_DATE    DUE_AMOUNT PAYMENT_DAT PAYMENT_AMOUNT PAYMENT_TAKEN STATUS
----------- ---------- ----------- -------------- ------------- -------
01-JAN-2010        100 02-JAN-2010            100           100 FULL
01-JUN-2010        100 01-JUN-2010             10            10 PARTIAL
01-JUN-2010        100 02-JUL-2010             80            80 PARTIAL
01-JUN-2010        100 05-AUG-2010             10            10 BALANCE
01-JAN-2011        100 01-JAN-2011             10            10 PARTIAL
01-JAN-2011        100 02-JAN-2011             50            50 PARTIAL
01-JAN-2011        100 02-JUN-2011            140            40 BALANCE
01-JUN-2011        100 02-JUN-2011            140           100 FULL
01-JAN-2012        100 01-JAN-2012            100           100 FULL
01-JUN-2012        100                                          WAITING

10 rows selected.

[Updated on: Thu, 03 December 2015 07:35]

Report message to a moderator

Re: Distribute payments over due dates and amounts [message #645422 is a reply to message #645318] Sat, 05 December 2015 21:56 Go to previous message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Dear Michel,

Quote:

I think your duplicate following lines is a copy and paste error and there should be only one


Right your are and very sorry for the confusion, thanks a lot for your feedback I really learn an new technique every time I place a question in this forum.
The only solution I could manage to solve this problem with was PL/SQL, so thanks a lot.

Ferro

Report message to a moderator

Previous Topic: Data export to flat file (merged 3)
Next Topic: Regular Expression
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Jul 17 13:29:02 CDT 2026
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.