Home » SQL & PL/SQL » SQL & PL/SQL » Generating Installment rows (12.1.0.1)
Generating Installment rows [message #683994] Tue, 16 March 2021 13:47 Go to next message
OraFerro
Messages: 398
Registered: July 2011
Senior Member
Hi All,

I have the following case:

create table test_l_amounts
  (
    id number(2) primary key,
    amount number(15,3),
    expiry_date date,
    first_due_Date date
  );

insert all
  into TEST_L_AMOUNTS (id, amount, expiry_date,first_due_Date  ) values (1, 1000, to_date('01-01-2030','dd-mm-yyyy'), to_date('01-06-2021','dd-mm-yyyy'))
  into TEST_L_AMOUNTS (id, amount, expiry_date,first_due_Date  ) values (2, 750, to_date('01-01-2023','dd-mm-yyyy'), to_date('01-01-2022','dd-mm-yyyy'))
  into TEST_L_AMOUNTS (id, amount, expiry_date,first_due_Date  ) values (3, 500, to_date('01-05-2023','dd-mm-yyyy'), to_date('01-05-2021','dd-mm-yyyy'))
select * from dual;
what I need is to generate equal bi-anual installments based on the amount, expiry date, and first due date of each recod according to the following rules:
1- the default is having 10 equal installments that span 5 years from the first_due_Date as long as this period is covered by the expiry_date. (case of id 1)
2- In case the expiry_date limits the span of the default 5 years, the number of installments and the installment amount is divided to fill the period form first_due_Date until expiry_date with equal bi-annual installments. (case of id 2)
3- The span of installments is inclusive to expiry_date, so the last installment can be equal to expiry_date. (case of id 2 and 3)

The desired output should be like:

ID installment amount date
1 1 100 01-06-2021
1 2 100 01-12-2021
1 3 100 01-06-2022
1 4 100 01-12-2022
1 5 100 01-06-2023
1 6 100 01-12-2023
1 7 100 01-06-2024
1 8 100 01-12-2024
1 9 100 01-06-2025
1 10 100 01-12-2025
2 1 250 01-01-2022
2 2 250 01-07-2022
2 3 250 01-01-2023
3 1 100 01-05-2021
3 2 100 01-11-2021
3 3 100 01-05-2022
3 4 100 01-11-2022
3 5 100 01-05-2023

Thanks,
Ferro

[Updated on: Tue, 16 March 2021 13:49]

Report message to a moderator

Re: Generating Installment rows [message #683996 is a reply to message #683994] Tue, 16 March 2021 23:28 Go to previous message
OraFerro
Messages: 398
Registered: July 2011
Senior Member
Hi
I managed to achieve the required result.
here is my trial using Lateral join, please tell me in case of better suggestion or enhancement.

           SELECT DTA.ID, row_number() over (partition by ID ORDER BY NULL) INS_NUMBER, 
                  AMOUNT/NUM_INSTALLMENTS INST_AMOUNT,
                  ADD_MONTHS(DTA.FIRST_DUE_DATE, (row_number() over (partition by ID ORDER BY NULL) -1)*6) INST_DUE_DATE
              FROM (
                      SELECT ID, AMOUNT, EXPIRY_DATE, FIRST_DUE_DATE, FLOOR(MONTHS_BETWEEN(EXPIRY_DATE, FIRST_DUE_DATE) /6) + 1 INST_MONTHS,
                             CASE WHEN (FLOOR(MONTHS_BETWEEN(EXPIRY_DATE, FIRST_DUE_DATE) /6) + 1) > 10
                              THEN 10
                              ELSE (FLOOR(MONTHS_BETWEEN(EXPIRY_DATE, FIRST_DUE_DATE) /6) + 1) 
                             END NUM_INSTALLMENTS
                        FROM TEST_L_AMOUNTS
                    ) DTA,
                    LATERAL (SELECT 1 FROM DUAL CONNECT BY LEVEL <= NUM_INSTALLMENTS);
Thanks,
Previous Topic: Beginner question. Update query with another SQL
Next Topic: Generate SQL via SQL
Goto Forum:
  


Current Time: Thu Jun 24 23:43:20 CDT 2021