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