I have the below setup which is just used to explain the problem
Here Car is the default vehicle bought if no other vehicle is bought
The logic is if there is a bulk payment then it should be split across the vehicles under Debit Customer
The actual transaction happens like the below
bought date Bought Credit_Acc Debit Customer paid_date
----------- ------ ---------- -------------- ----------
1-jan-2019 Bike 10k 0 03-Jan-2019
2-jan-2019 cycle 20k 0 03-Jan-2019
3-jan-2019 Car 30k 60k 03-Jan-2019
But the Customer wants the financial report to be like the below
bought date Bought Credit_Acc Debit Customer paid_date
----------- ------ ---------- -------------- ----------
1-jan-2019 Bike 10k 10k 03-Jan-2019
2-jan-2019 cycle 20k 20k 03-Jan-2019
3-jan-2019 Car 30k 30k 03-Jan-2019
Also sometimes if he pays the only 15k which is recorded under Debit Customer for 03-jan-2019 bought date then the report should be
bought date Bought Credit_Acc Debit Customer paid_date
----------- ------ ---------- -------------- ----------
1-jan-2019 Bike 10k 10k 03-Jan-2019
2-jan-2019 cycle 20k 5k 03-Jan-2019
3-jan-2019 Car 30k 0(15k actual data) 03-Jan-2019
So after this 15k payment, another 15k payment is done on 04-Jan-2019 then 30k is recorded in Debit Customer base table but the report should show the below
bought date Bought Credit_Acc Debit Customer paid_date
----------- ------ ---------- -------------- ----------
1-jan-2019 Bike 10k 10k 04-Jan-2019
2-jan-2019 cycle 20k 20k 04-Jan-2019
3-jan-2019 Car 30k 0(30k actual data) 04-Jan-2019
Then after this payment, another 30k is made on 05-Jan-2019 then 60k is recorded under Debit Customer base table but the report should show the below
bought date Bought Credit_Acc Debit Customer paid_date
----------- ------ ---------- -------------- ----------
1-jan-2019 Bike 10k 10k 05-Jan-2019
2-jan-2019 cycle 20k 20k 05-Jan-2019
3-jan-2019 Car 30k 30k(60k actual data)05-Jan-2019
TABLE STRUCTURE
VALUE DATE (bought date/paid date)
ITEM (Bought)
Debit_Entry (Debit Customer)
Credit_Entry (Credit_Acc)
It's hard to tell from your question as the base table data still isn't shown, but it looks like you want something like:
select value_date, item, credit_entry, item_paid
from (
select value_date, item, credit_entry, debit_entry,
greatest(0, least(credit_entry, nvl(sum(debit_entry) over (), 0)
- nvl(sum(credit_entry) over (order by value_date
rows between unbounded preceding and 1 preceding), 0))) as item_paid
from your_table
)
where item is not null;
For each row in the table, it sums up the total debit amount, and subtracts the total of the credit amounts up to (but not including) that row's. If that total payment is greater that the current row's credit then the credit value is used as-is; if it's negative (because the current item hasn't been paid off at all) then zero is used instead; otherwise the calculated value is used, which is the amount of that item's credit that has been paid off - after all previous items have been considered.