Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Split amount with minimum amount per month

ProkopisSep 23 2021 — edited Sep 23 2021

Hi all, I have a table with a contract and it's payable amount.
I want to split amounts to 36 doses.

I am trying the following code :

WITH BASE_QUERY(CONTRACT_NUM, NUM_OF_DOSES, AMOUNT) AS (
SELECT '0423423423442', 36, 1300.12 FROM DUAL UNION ALL
SELECT '04238423442', 36, 412.22 FROM DUAL UNION ALL
SELECT '04238411111', 36, 120.69 FROM DUAL
)
,
DATA (CONTRACT_NUM, NUM_OF_DOSES, AMOUNT, ITER, MONTH_AMOUNT) 
 AS (SELECT CONTRACT_NUM, NUM_OF_DOSES , AMOUNT, 1, ROUND(AMOUNT/NUM_OF_DOSES,2)
    FROM BASE_QUERY
   UNION ALL
   SELECT CONTRACT_NUM, NUM_OF_DOSES, AMOUNT, ITER+1, MONTH_AMOUNT
    FROM DATA
    WHERE ITER+1 <= NUM_OF_DOSES)
SELECT CONTRACT_NUM, NUM_OF_DOSES, ITER AS CONTRACT_DOSE_ITER, ADD_MONTHS(TO_DATE('25/09/2021','dd/mm/yyyy'), ITER) AS MONTH_DATE, AMOUNT AS TOTAL_AMOUNT, MONTH_AMOUNT
FROM DATA
ORDER BY CONTRACT_NUM, CONTRACT_DOSE_ITER;

The problem I have is 
1. When I summary per contract I don't take as result the total_amount. For example for contract 0423423423442 I take as summary 1299,96 not 1300,12
This happens due to rounding. Is it possible to assign the difference to the last dose ?
2. Moreover, I want to have a minimum amount in each dose for example 100.
So, the doses in contract 04238411111 will be the first with 100 and the second with 20.69

Could you help me please ?
Thanks in advance

This post has been answered by Frank Kulash on Sep 23 2021
Jump to Answer

Comments

Processing

Post Details

Added on Sep 23 2021
10 comments
77 views