Home » SQL & PL/SQL » SQL & PL/SQL » Rescheduling installments by distributing amounts (12.1.0.1.0)
| Rescheduling installments by distributing amounts [message #687469] |
Mon, 13 March 2023 01:39  |
 |
OraFerro
Messages: 419
Registered: July 2011
|
Senior Member |
|
|
Hi All,
I have the following four schedules, each consists of a id, installments, due date, and amount. each id has 2 installments due dates per year that are 6 months apart. I need to do a rescheduling based on the following rules:
- installments that take place in 2022 and 2023 are going to be rescheduled as long as the are not FULLY Repaid (just kept the name instead of an ID for easier reading).
- scheduled installments amount will be 0.
- rescheduling takes place by distributing the sum of the scheduled installments on the remaining installments so that the first installment carries the fractions to the nearest 1000 as show in the result below belonging to ids 3 and 4.
- in case the scheduled installment(s) are the last installment(s), a new installment with an amount equal to the sum of scheduled installments is added with the closest due data in 2024 (so in case the id has installments in June and Dec each year, the first available installment in 2024 is June 2024, if the due dates were for example Feb and August the first available date in 2024 for this id is Feb 2024).
Here is my case and expected results:
create table test_sched
(
agr_id number(6),
installment_no number(3),
Due_date date,
amount number(15,3),
status varchar(20)
);
insert all
into TEST_SCHED values (1,1,to_date('01-03-2014','dd-mm-yyyy'), 258000, 'FULLY Repaid')
into TEST_SCHED values (1,2,to_date('01-09-2014','dd-mm-yyyy'), 258000, 'FULLY Repaid')
into TEST_SCHED values (1,3,to_date('01-03-2015','dd-mm-yyyy'), 258000, 'FULLY Repaid')
into TEST_SCHED values (1,4,to_date('01-09-2015','dd-mm-yyyy'), 258000, 'FULLY Repaid')
into TEST_SCHED values (1,5,to_date('01-03-2016','dd-mm-yyyy'), 258000, 'FULLY Repaid')
into TEST_SCHED values (1,6,to_date('01-09-2016','dd-mm-yyyy'), 258000, 'FULLY Repaid')
into TEST_SCHED values (1,7,to_date('01-03-2017','dd-mm-yyyy'), 258000, 'FULLY Repaid')
into TEST_SCHED values (1,8,to_date('01-09-2017','dd-mm-yyyy'), 258000, 'FULLY Repaid')
into TEST_SCHED values (1,9,to_date('01-03-2018','dd-mm-yyyy'), 258000, 'FULLY Repaid')
into TEST_SCHED values (1,10,to_date('01-09-2018','dd-mm-yyyy'), 258000, 'FULLY Repaid')
into TEST_SCHED values (1,11,to_date('01-03-2019','dd-mm-yyyy'), 258000, 'FULLY Repaid')
into TEST_SCHED values (1,12,to_date('01-09-2019','dd-mm-yyyy'), 258000, 'FULLY Repaid')
into TEST_SCHED values (1,13,to_date('01-03-2020','dd-mm-yyyy'), 258000, 'FULLY Repaid')
into TEST_SCHED values (1,14,to_date('01-09-2020','dd-mm-yyyy'), 258000, 'FULLY Repaid')
into TEST_SCHED values (1,15,to_date('01-03-2021','dd-mm-yyyy'), 258000, 'FULLY Repaid')
into TEST_SCHED values (1,16,to_date('01-09-2021','dd-mm-yyyy'), 258000, 'FULLY Repaid')
into TEST_SCHED values (1,17,to_date('01-03-2022','dd-mm-yyyy'), 258000, 'FULLY Repaid')
into TEST_SCHED values (1,18,to_date('01-09-2022','dd-mm-yyyy'), 258000, '')
into TEST_SCHED values (1,19,to_date('01-03-2023','dd-mm-yyyy'), 258000, '')
into TEST_SCHED values (2,1,to_date('01-12-2013','dd-mm-yyyy'), 660000, 'FULLY Repaid')
into TEST_SCHED values (2,2,to_date('01-06-2014','dd-mm-yyyy'), 660000, 'FULLY Repaid')
into TEST_SCHED values (2,3,to_date('01-12-2014','dd-mm-yyyy'), 660000, 'FULLY Repaid')
into TEST_SCHED values (2,4,to_date('01-06-2015','dd-mm-yyyy'), 660000, 'FULLY Repaid')
into TEST_SCHED values (2,5,to_date('01-12-2015','dd-mm-yyyy'), 660000, 'FULLY Repaid')
into TEST_SCHED values (2,6,to_date('01-06-2016','dd-mm-yyyy'), 660000, 'FULLY Repaid')
into TEST_SCHED values (2,7,to_date('01-12-2016','dd-mm-yyyy'), 660000, 'FULLY Repaid')
into TEST_SCHED values (2,8,to_date('01-06-2017','dd-mm-yyyy'), 660000, 'FULLY Repaid')
into TEST_SCHED values (2,9,to_date('01-12-2017','dd-mm-yyyy'), 660000, 'FULLY Repaid')
into TEST_SCHED values (2,10,to_date('01-06-2018','dd-mm-yyyy'), 660000, 'FULLY Repaid')
into TEST_SCHED values (2,11,to_date('01-12-2018','dd-mm-yyyy'), 660000, 'FULLY Repaid')
into TEST_SCHED values (2,12,to_date('01-06-2019','dd-mm-yyyy'), 660000, 'FULLY Repaid')
into TEST_SCHED values (2,13,to_date('01-12-2019','dd-mm-yyyy'), 660000, 'FULLY Repaid')
into TEST_SCHED values (2,14,to_date('01-06-2020','dd-mm-yyyy'), 660000, 'FULLY Repaid')
into TEST_SCHED values (2,15,to_date('01-12-2020','dd-mm-yyyy'), 660000, 'FULLY Repaid')
into TEST_SCHED values (2,16,to_date('01-06-2021','dd-mm-yyyy'), 660000, 'FULLY Repaid')
into TEST_SCHED values (2,17,to_date('01-12-2021','dd-mm-yyyy'), 660000, 'FULLY Repaid')
into TEST_SCHED values (2,18,to_date('01-06-2022','dd-mm-yyyy'), 660000, 'FULLY Repaid')
into TEST_SCHED values (2,19,to_date('01-12-2022','dd-mm-yyyy'), 500000, '')
into TEST_SCHED values (3,1,to_date('01-08-2017','dd-mm-yyyy'), 116000, 'FULLY Repaid')
into TEST_SCHED values (3,2,to_date('01-02-2018','dd-mm-yyyy'), 116000, 'FULLY Repaid')
into TEST_SCHED values (3,3,to_date('01-08-2018','dd-mm-yyyy'), 116000, 'FULLY Repaid')
into TEST_SCHED values (3,4,to_date('01-02-2019','dd-mm-yyyy'), 116000, 'FULLY Repaid')
into TEST_SCHED values (3,5,to_date('01-08-2019','dd-mm-yyyy'), 116000, 'FULLY Repaid')
into TEST_SCHED values (3,6,to_date('01-02-2020','dd-mm-yyyy'), 116000, 'FULLY Repaid')
into TEST_SCHED values (3,7,to_date('01-08-2020','dd-mm-yyyy'), 116000, 'FULLY Repaid')
into TEST_SCHED values (3,8,to_date('01-02-2021','dd-mm-yyyy'), 116000, 'FULLY Repaid')
into TEST_SCHED values (3,9,to_date('01-08-2021','dd-mm-yyyy'), 116000, 'FULLY Repaid')
into TEST_SCHED values (3,10,to_date('01-02-2022','dd-mm-yyyy'), 116000, 'FULLY Repaid')
into TEST_SCHED values (3,11,to_date('01-08-2022','dd-mm-yyyy'), 116000, '')
into TEST_SCHED values (3,12,to_date('01-02-2023','dd-mm-yyyy'), 116000, '')
into TEST_SCHED values (3,13,to_date('01-08-2023','dd-mm-yyyy'), 116000, '')
into TEST_SCHED values (3,14,to_date('01-02-2024','dd-mm-yyyy'), 116000, '')
into TEST_SCHED values (3,15,to_date('01-08-2024','dd-mm-yyyy'), 116000, '')
into TEST_SCHED values (3,16,to_date('01-02-2025','dd-mm-yyyy'), 116000, '')
into TEST_SCHED values (3,17,to_date('01-08-2025','dd-mm-yyyy'), 116000, '')
into TEST_SCHED values (3,18,to_date('01-02-2026','dd-mm-yyyy'), 116000, '')
into TEST_SCHED values (3,19,to_date('01-08-2026','dd-mm-yyyy'), 116000, '')
into TEST_SCHED values (3,20,to_date('01-02-2027','dd-mm-yyyy'), 116000, '')
into TEST_SCHED values (4,1,to_date('01-02-2022','dd-mm-yyyy'), 190000, '')
into TEST_SCHED values (4,2,to_date('01-08-2022','dd-mm-yyyy'), 190000, '')
into TEST_SCHED values (4,3,to_date('01-02-2023','dd-mm-yyyy'), 190000, '')
into TEST_SCHED values (4,4,to_date('01-08-2023','dd-mm-yyyy'), 190000, '')
into TEST_SCHED values (4,5,to_date('01-02-2024','dd-mm-yyyy'), 190000, '')
into TEST_SCHED values (4,6,to_date('01-08-2024','dd-mm-yyyy'), 190000, '')
into TEST_SCHED values (4,7,to_date('01-02-2025','dd-mm-yyyy'), 190000, '')
into TEST_SCHED values (4,8,to_date('01-08-2025','dd-mm-yyyy'), 190000, '')
into TEST_SCHED values (4,9,to_date('01-02-2026','dd-mm-yyyy'), 190000, '')
into TEST_SCHED values (4,10,to_date('01-08-2026','dd-mm-yyyy'), 190000, '')
into TEST_SCHED values (4,11,to_date('01-02-2027','dd-mm-yyyy'), 190000, '')
into TEST_SCHED values (4,12,to_date('01-08-2027','dd-mm-yyyy'), 190000, '')
into TEST_SCHED values (4,13,to_date('01-02-2028','dd-mm-yyyy'), 190000, '')
into TEST_SCHED values (4,14,to_date('01-08-2028','dd-mm-yyyy'), 190000, '')
into TEST_SCHED values (4,15,to_date('01-02-2029','dd-mm-yyyy'), 190000, '')
into TEST_SCHED values (4,16,to_date('01-08-2029','dd-mm-yyyy'), 190000, '')
into TEST_SCHED values (4,17,to_date('01-02-2030','dd-mm-yyyy'), 190000, '')
into TEST_SCHED values (4,18,to_date('01-08-2030','dd-mm-yyyy'), 190000, '')
select * from dual;
AG_ID Inst_No Due_Date Amount
1 1 01-03-2014 258000
1 2 01-09-2014 258000
1 3 01-03-2015 258000
1 4 01-09-2015 258000
1 5 01-03-2016 258000
1 6 01-09-2016 258000
1 7 01-03-2017 258000
1 8 01-09-2017 258000
1 9 01-03-2018 258000
1 10 01-09-2018 258000
1 11 01-03-2019 258000
1 12 01-09-2019 258000
1 13 01-03-2020 258000
1 14 01-09-2020 258000
1 15 01-03-2021 258000
1 16 01-09-2021 258000
1 17 01-03-2022 258000
1 18 01-09-2022 0
1 19 01-03-2023 0
1 20 01-03-2024 516000--both installment 18 and 19 are now worth 0 and their sum was added as newly inserted installment 20
2 1 01-12-2013 660000
2 2 01-06-2014 660000
2 3 01-12-2014 660000
2 4 01-06-2015 660000
2 5 01-12-2015 660000
2 6 01-06-2016 660000
2 7 01-12-2016 660000
2 8 01-06-2017 660000
2 9 01-12-2017 660000
2 10 01-06-2018 660000
2 11 01-12-2018 660000
2 12 01-06-2019 660000
2 13 01-12-2019 660000
2 14 01-06-2020 660000
2 15 01-12-2020 660000
2 16 01-06-2021 660000
2 17 01-12-2021 660000
2 18 01-06-2022 660000
2 19 01-12-2022 0
2 20 01-06-22 500000 --only installment 19 amount was modified to 0 and its amount is inserted as a new installment 20
3 1 01-08-2017 116000
3 2 01-02-2018 116000
3 3 01-08-2018 116000
3 4 01-02-2019 116000
3 5 01-08-2019 116000
3 6 01-02-2020 116000
3 7 01-08-2020 116000
3 8 01-02-2021 116000
3 9 01-08-2021 116000
3 10 01-02-2022 116000
3 11 01-08-2022 0
3 12 01-02-2023 0
3 13 01-08-2023 0
3 14 01-02-2024 170000--because installment 10 is FULLY Repaid, only 11-13 were turned into 0 and the sum was distributed to the remaining installments keeping the fraction to the closest 1000 into installment 14.
3 15 01-08-2024 165000
3 16 01-02-2025 165000
3 17 01-08-2025 165000
3 18 01-02-2026 165000
3 19 01-08-2026 165000
3 20 01-02-2027 165000
4 1 01-02-2022 0
4 2 01-08-2022 0
4 3 01-02-2023 0
4 4 01-08-2023 0
4 5 01-02-2024 215000--installments 1-4 are not FULLY Repaid and their sum was distributed on the remaining installments the same way as id 3 above.
4 6 01-08-2024 205000
4 7 01-02-2025 205000
4 8 01-08-2025 205000
4 9 01-02-2026 205000
4 10 01-08-2026 205000
4 11 01-02-2027 205000
4 12 01-08-2027 205000
4 13 01-02-2028 205000
4 14 01-08-2028 205000
4 15 01-02-2029 205000
4 16 01-08-2029 205000
4 17 01-02-2030 205000
4 18 01-08-2030 205000
Ferro
|
|
|
|
|
|
|
|
| Re: Rescheduling installments by distributing amounts [message #687472 is a reply to message #687471] |
Mon, 13 March 2023 11:20  |
 |
mathguy
Messages: 77
Registered: January 2023
|
Member |
|
|
Here is a query that I believe does everything you need. Note that your math for ID = 4 is just wrong; the skipped installments have a total amount of 4 * 190,000 = 760,000, spread over the remaining 14 payments, meaning the remaining 14 payments must increase by 760,000 / 14 which is a little over 54,000; if you increase 190,000 by 54,000 you get 244,000, I have no idea what math you applied in your "desired output" to get your values.
Also the example for ID = 3, which is mathematically correct, suggests that in your vocabulary, "nearest 1000" doesn't actually mean "nearest" (ROUND), but it means truncate to the nearest 1000. Or, if you really meant "nearest" (which may be rounding up in some cases), then your math is wrong for ID = 3 also. I chose to assume that your math is correct, and you meant TRUNC for "nearest" thousand.
(Note: big PLUS PLUS to you for posting test data, and for the statements being 100% correct - and also for stating the problem very clearly. MINUS to you for sloppiness; I found myself spending a good amount of time figuring out why various things didn't seem to work, only to discover that you were being inconsistent, or that your math is just plain wrong, etc.)
So - here is the query that does what you need, and agrees with your output except for the future amounts for ID = 4. Please test on more data to make sure it's correct (no point in investigating it further if it isn't). If it seems correct, you may want to read through the query to understand what it does. Please write back with questions if there are parts you don't understand.
with
skip_amounts (agr_id, s_installment_no, s_due_date, s_amount) as (
select agr_id, max(installment_no), max(due_date), sum(amount)
from test_sched
where due_date >= date '2022-01-01' and due_date < date '2024-01-01'
and lnnvl(status = 'FULLY Repaid')
group by agr_id
)
, future_dates (agr_id, installment_no, due_date, amount, ct, rn) as (
select agr_id, installment_no, due_date, amount, count(*) over (partition by agr_id),
row_number() over (partition by agr_id order by installment_no)
from test_sched
where due_date >= date '2024-01-01'
)
select agr_id, installment_no, due_date,
case when due_date >= date '2022-01-01' and lnnvl(status = 'FULLY Repaid')
then 0 else amount end as amount
from test_sched
where due_date < date '2024-01-01'
UNION ALL
select s.agr_id,
nvl(f.installment_no, s.s_installment_no + 1) as installment_no,
nvl(f.due_date,
add_months(s.s_due_date,
6 * ceil(months_between(date '2024-01-01', s.s_due_date)/6))) as due_date,
case when f.agr_id is null then s.s_amount
else f.amount + trunc(s.s_amount / f.ct, -3)
+ case rn when 1 then s.s_amount - f.ct * trunc(s.s_amount / f.ct, -3)
else 0
end
end as amount
from skip_amounts s left outer join future_dates f on s.agr_id = f.agr_id
order by agr_id, installment_no
;
AGR_ID INSTALLMENT_NO DUE_DATE AMOUNT
------ -------------- ---------- ----------
1 1 01-03-2014 258000
1 2 01-09-2014 258000
1 3 01-03-2015 258000
1 4 01-09-2015 258000
1 5 01-03-2016 258000
1 6 01-09-2016 258000
1 7 01-03-2017 258000
1 8 01-09-2017 258000
1 9 01-03-2018 258000
1 10 01-09-2018 258000
1 11 01-03-2019 258000
1 12 01-09-2019 258000
1 13 01-03-2020 258000
1 14 01-09-2020 258000
1 15 01-03-2021 258000
1 16 01-09-2021 258000
1 17 01-03-2022 258000
1 18 01-09-2022 0
1 19 01-03-2023 0
1 20 01-03-2024 516000
2 1 01-12-2013 660000
2 2 01-06-2014 660000
2 3 01-12-2014 660000
2 4 01-06-2015 660000
2 5 01-12-2015 660000
2 6 01-06-2016 660000
2 7 01-12-2016 660000
2 8 01-06-2017 660000
2 9 01-12-2017 660000
2 10 01-06-2018 660000
2 11 01-12-2018 660000
2 12 01-06-2019 660000
2 13 01-12-2019 660000
2 14 01-06-2020 660000
2 15 01-12-2020 660000
2 16 01-06-2021 660000
2 17 01-12-2021 660000
2 18 01-06-2022 660000
2 19 01-12-2022 0
2 20 01-06-2024 500000
3 1 01-08-2017 116000
3 2 01-02-2018 116000
3 3 01-08-2018 116000
3 4 01-02-2019 116000
3 5 01-08-2019 116000
3 6 01-02-2020 116000
3 7 01-08-2020 116000
3 8 01-02-2021 116000
3 9 01-08-2021 116000
3 10 01-02-2022 116000
3 11 01-08-2022 0
3 12 01-02-2023 0
3 13 01-08-2023 0
3 14 01-02-2024 170000
3 15 01-08-2024 165000
3 16 01-02-2025 165000
3 17 01-08-2025 165000
3 18 01-02-2026 165000
3 19 01-08-2026 165000
3 20 01-02-2027 165000
4 1 01-02-2022 0
4 2 01-08-2022 0
4 3 01-02-2023 0
4 4 01-08-2023 0
4 5 01-02-2024 248000
4 6 01-08-2024 244000
4 7 01-02-2025 244000
4 8 01-08-2025 244000
4 9 01-02-2026 244000
4 10 01-08-2026 244000
4 11 01-02-2027 244000
4 12 01-08-2027 244000
4 13 01-02-2028 244000
4 14 01-08-2028 244000
4 15 01-02-2029 244000
4 16 01-08-2029 244000
4 17 01-02-2030 244000
4 18 01-08-2030 244000 |
|
|
|
|
|
|
|
|
|
| Re: Rescheduling installments by distributing amounts [message #687479 is a reply to message #687477] |
Tue, 14 March 2023 14:05 |
|
mathguy
Messages: 77
Registered: January 2023
|
Member |
|
|
If you need to update the base table (update unpaid amounts for 2022-2023 to 0, update amounts for 2024 and later by adding the amounts owed for 2022-2023, and add - insert - new "last payment" for schedules that don't have scheduled payments beyond 2023), you could use pretty much the same technique, but in a MERGE statement. Something like this:
merge into
( select agr_id, installment_no, due_date, amount
from test_sched
where due_date >= date '2022-01-01'
) tgt
using
(
with
skip_rows (agr_id, installment_no, due_date, amount) as(
select agr_id, installment_no, due_date, amount
from test_sched
where due_date >= date '2022-01-01' and due_date < date '2024-01-01'
and lnnvl(status = 'FULLY Repaid')
)
, skip_amounts (agr_id, s_installment_no, s_due_date, s_amount) as (
select agr_id, max(installment_no), max(due_date), sum(amount)
from skip_rows
group by agr_id
)
, future_dates (agr_id, installment_no, due_date, amount, ct, rn) as (
select agr_id, installment_no, due_date, amount, count(*) over (partition by agr_id),
row_number() over (partition by agr_id order by installment_no)
from test_sched
where agr_id in (select agr_id from skip_amounts) and due_date >= date '2024-01-01'
)
select agr_id, installment_no, due_date, 0 as adj_amount
from skip_rows
UNION ALL
select s.agr_id, nvl(f.installment_no, s.s_installment_no + 1),
nvl(f.due_date, add_months(s.s_due_date,
6 * ceil(months_between(date '2024-01-01', s.s_due_date)/6))),
case when f.agr_id is null then s.s_amount
else f.amount + trunc(s.s_amount / f.ct, -3)
+ case rn when 1 then s.s_amount - f.ct * trunc(s.s_amount / f.ct, -3)
else 0
end
end
from skip_amounts s left outer join future_dates f on s.agr_id = f.agr_id
) src
on (tgt.agr_id = src.agr_id and tgt.installment_no = src.installment_no)
when matched then update
set tgt.amount = src.adj_amount
when not matched then insert
values (src.agr_id, src.installment_no, src.due_date, src.adj_amount)
; |
|
|
|
|
|
Goto Forum:
Current Time: Wed Sep 27 09:08:41 CDT 2023
|
] 
Blog
Search
Help
Members
Register
Login
Home
