## Forum Stats

• 3,759,945 Users
• 2,251,619 Discussions

Discussions

# Split amount with minimum amount per month

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 ?

• Hi, @Prokopis

I am using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit

Then LATERAL or CROSS APPLY won't help you; those were new features in Oracle 12.1.

But supposing a minimum amount 500 and fixing decimals to the last dose

I want to get

Okay, now I see what you want. Here's one way to do it:

```WITH  data (contract_num, num_of_doses, amount, iter, month_amount, amount_left)   AS
(
SELECT  contract_num, num_of_doses , amount, 0
, 	GREATEST ( ROUND (amount / num_of_doses, 2)
, :min_amount
)
,       amount
FROM 	base_query
UNION ALL
SELECT  contract_num, num_of_doses, amount, iter + 1
,	month_amount
,	amount_left - month_amount
FROM 	data
WHERE 	iter + 1 <= num_of_doses
)
SELECT   contract_num
, 	 num_of_doses
, 	 iter		 AS contract_dose_iter
, iter
)    AS month_date
,       amount   	 AS total_amount
, 	 CASE
WHEN amount_left > 0
AND iter    < num_of_doses
THEN month_amount
ELSE amount_left + month_amount
END		 AS month_amount
--,	 amount_left   -- FOR DEBUGGING ONLY
FROM 	 data
WHERE	 iter                       >  0
AND	 amount_left + month_amount >= 0
ORDER BY contract_num
,   	 contract_dose_iter
;
```

The query above uses a bind variable (:min_amount). You could use any other technique to get the appropriate value into the query. Since it's only used in one place, you could hard-code it.

• Hi, @Prokopis

Thanks for posting the sample data. Don't forget to post the exact results you want from the given sample data, an explain, step by step, how you get those results. Always give your full Oracle version (e.g. 18.4.0.0.0).

I want to split amounts to 36 doses.

Does that mean the 3 rows of sample data will produce 3 * 36 = 108 row of results? To avoid posting all 108 rows, you may want to use an example where you only need to split amounts into 3 doses. Just make it clear that you need 36 doses in your real problem; you'll get answers that can easily be changed.

I am using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit

I give below a sample with 3 doses

```WITH BASE_QUERY(CONTRACT_NUM, NUM_OF_DOSES, AMOUNT) AS (
SELECT '0423423423442', 3, 1300.12 FROM DUAL UNION ALL
SELECT   '04238423442', 3,  412.22 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;
```

I get as result

But supposing a minimum amount 500 and fixing decimals to the last dose

I want to get

• ```WITH BASE_QUERY(CONTRACT_NUM, NUM_OF_DOSES, AMOUNT)
AS (
SELECT '0423423423442',36,1300.12 FROM DUAL
)
SELECT  CONTRACT_NUM,
NUM_OF_DOSES,
AMOUNT,
ITER,
CASE ITER
WHEN NUM_OF_DOSES THEN AMOUNT - ROUND(AMOUNT/NUM_OF_DOSES,2) * (NUM_OF_DOSES - 1)
ELSE ROUND(AMOUNT/NUM_OF_DOSES,2)
END MONTH_AMOUNT
FROM  BASE_QUERY,
LATERAL(
SELECT  LEVEL ITER
FROM  DUAL
CONNECT BY LEVEL <= NUM_OF_DOSES
)
/

CONTRACT_NUM  NUM_OF_DOSES     AMOUNT       ITER MONTH_AMOUNT
------------- ------------ ---------- ---------- ------------
0423423423442           36    1300.12          1        36.11
0423423423442           36    1300.12          2        36.11
0423423423442           36    1300.12          3        36.11
0423423423442           36    1300.12          4        36.11
0423423423442           36    1300.12          5        36.11
0423423423442           36    1300.12          6        36.11
0423423423442           36    1300.12          7        36.11
0423423423442           36    1300.12          8        36.11
0423423423442           36    1300.12          9        36.11
0423423423442           36    1300.12         10        36.11
0423423423442           36    1300.12         11        36.11
0423423423442           36    1300.12         12        36.11
0423423423442           36    1300.12         13        36.11
0423423423442           36    1300.12         14        36.11
0423423423442           36    1300.12         15        36.11
0423423423442           36    1300.12         16        36.11
0423423423442           36    1300.12         17        36.11
0423423423442           36    1300.12         18        36.11
0423423423442           36    1300.12         19        36.11
0423423423442           36    1300.12         20        36.11
0423423423442           36    1300.12         21        36.11
0423423423442           36    1300.12         22        36.11
0423423423442           36    1300.12         23        36.11
0423423423442           36    1300.12         24        36.11
0423423423442           36    1300.12         25        36.11
0423423423442           36    1300.12         26        36.11
0423423423442           36    1300.12         27        36.11
0423423423442           36    1300.12         28        36.11
0423423423442           36    1300.12         29        36.11
0423423423442           36    1300.12         30        36.11
0423423423442           36    1300.12         31        36.11
0423423423442           36    1300.12         32        36.11
0423423423442           36    1300.12         33        36.11
0423423423442           36    1300.12         34        36.11
0423423423442           36    1300.12         35        36.11
0423423423442           36    1300.12         36        36.27

36 rows selected.

SQL>
```

SY.

• Hi, @Prokopis

This happens due to rounding. Is it possible to assign the difference to the last dose ?

So, for all iters except the last one, you want month_amount to be  ROUND (amount / num_of_doses, 2), For the highest value of iter, you want month_amount to be whatever it takes to make the total of all month_amounts to be exactly amount; is that right?

Here's one way to do that:

```WITH  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
, 	CASE
WHEN iter + 1 < num_of_doses
THEN month_amount
ELSE amount - ( (num_of_doses - 1)
* month_amount
)
END
FROM 	data
WHERE 	iter + 1 <= num_of_doses
)
SELECT   contract_num
, 	 num_of_doses
, 	 iter		 AS contract_dose_iter
, iter
)    AS month_date
,        amount   	 AS total_amount
, 	 month_amount
FROM 	 data
ORDER BY contract_num
,   	 contract_dose_iter
;
```

Results (with num_of_doses changed to 3 on all rows):

```
CONTRACT_NUM NUM_OF_DOSES CONTRACT_DOSE_ITER MONTH_DATE   TOTAL_AMOUNT MONTH_AMOUN
------------- ------------ ------------------ ----------- ------------ ------------
0423423423442            3                  1 25-Oct-2021      1300.12       433.37
0423423423442            3                  2 25-Nov-2021      1300.12       433.37
0423423423442            3                  3 25-Dec-2021      1300.12       433.38
04238411111              3                  1 25-Oct-2021       120.69        40.23
04238411111              3                  2 25-Nov-2021       120.69        40.23
04238411111              3                  3 25-Dec-2021       120.69        40.23
04238423442              3                  1 25-Oct-2021       412.22       137.41
04238423442              3                  2 25-Nov-2021       412.22       137.41
04238423442              3                  3 25-Dec-2021       412.22        137.4
```

• Here is how I would do it. Note that this is a direct computation in closed form, rather than an iterative computation; so I can use a hierarchical (`CONNECT BY`) query instead of a recursive one. Also using the `LATERAL` join syntax, available since Oracle 12.

The condition on `LEVEL` in the lateral subquery could be written more compactly (dividing `AMOUNT` by `PRELIM_MONTH_AMOUNT` ) but that may cause rounding errors and the wrong number of doses.

I changed the number of doses to 12 so the output is not too long. I allow the number of doses and the minimum amount per month to depend on the contract (neither 12 nor 100 are hard-coded); if instead they should be constant across all rows (but still user inputs - like 36 and 110 for all contracts, instead of 36 and 100, or 12 and 100), you will have to modify this slightly to accept those as inputs, instead of being part of the table data.

```with
base_query (contract_num, num_of_doses, amount, min_amount) as (
select '0423423423442', 12, 1300.12, 100 from dual union all
select '04238423442'  , 12,  412.22, 100 from dual union all
select '04238411111'  , 12,  120.69, 100 from dual
)
, prep (contract_num, num_of_doses, amount, min_amount, prelim_month_amount) as (
select contract_num, num_of_doses, amount, min_amount,
greatest(100, round(amount / num_of_doses, 2))
from   base_query
)
select p.contract_num, l.iter,
case l.isleaf
when 0 then p.prelim_month_amount
else p.amount - (l.iter - 1) * p.prelim_month_amount end as month_amount
from   prep p cross join lateral
(
select  level as iter, connect_by_isleaf as isleaf
from    dual
connect by (level - 1) * p.prelim_month_amount <= p.amount
and level <= p.num_of_doses
) l
;

CONTRACT_NUM        ITER MONTH_AMOUNT
------------- ---------- ------------
0423423423442          1       108.34
0423423423442          2       108.34
0423423423442          3       108.34
0423423423442          4       108.34
0423423423442          5       108.34
0423423423442          6       108.34
0423423423442          7       108.34
0423423423442          8       108.34
0423423423442          9       108.34
0423423423442         10       108.34
0423423423442         11       108.34
0423423423442         12       108.38
04238423442            1          100
04238423442            2          100
04238423442            3          100
04238423442            4          100
04238423442            5        12.22
04238411111            1          100
04238411111            2        20.69

```

• While I was working on the problem, you posted a Reply in which you said your Oracle version is 11.

Please include your version with all your questions - it's much more relevant than most people think.

The `LATERAL` clause is not supported in your version. So you will need to modify the query I offered as shown below.

```select  contract_num, level as iter,
case connect_by_isleaf
when 0 then prelim_month_amount
else amount - (level - 1) * prelim_month_amount end as month_amount
from    prep
connect by (level - 1) * prelim_month_amount <= amount
and level <= num_of_doses
and prior contract_num = contract_num
and prior sys_guid() is not null
;
```
• I am using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit

OUCH! That is completely out of support.

• Hi, @Prokopis

I am using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit

Then LATERAL or CROSS APPLY won't help you; those were new features in Oracle 12.1.

But supposing a minimum amount 500 and fixing decimals to the last dose

I want to get

Okay, now I see what you want. Here's one way to do it:

```WITH  data (contract_num, num_of_doses, amount, iter, month_amount, amount_left)   AS
(
SELECT  contract_num, num_of_doses , amount, 0
, 	GREATEST ( ROUND (amount / num_of_doses, 2)
, :min_amount
)
,       amount
FROM 	base_query
UNION ALL
SELECT  contract_num, num_of_doses, amount, iter + 1
,	month_amount
,	amount_left - month_amount
FROM 	data
WHERE 	iter + 1 <= num_of_doses
)
SELECT   contract_num
, 	 num_of_doses
, 	 iter		 AS contract_dose_iter
, iter
)    AS month_date
,       amount   	 AS total_amount
, 	 CASE
WHEN amount_left > 0
AND iter    < num_of_doses
THEN month_amount
ELSE amount_left + month_amount
END		 AS month_amount
--,	 amount_left   -- FOR DEBUGGING ONLY
FROM 	 data
WHERE	 iter                       >  0
AND	 amount_left + month_amount >= 0
ORDER BY contract_num
,   	 contract_dose_iter
;
```

The query above uses a bind variable (:min_amount). You could use any other technique to get the appropriate value into the query. Since it's only used in one place, you could hard-code it.

• Thanks a lot for all you answers !!

I did not know about Lateral function. I will search it.

Thanks again

• I see that you chose an answer based on recursive `WITH` clause as the "accepted solution".

In my replies, I suggested a solution using `CONNECT BY`, which does all the computations in closed form rather than relying on recursive calculations. This should result in faster execution times.

I set out to see if that's true - and if it is, how much faster (since small differences may not matter). For testing I used my second solution, where I am not using the `LATERAL` clause. It is the solution that you can use in Oracle 11.2.

The answer is - if you have only 1000 contracts to work on, the `CONNECT BY` query is 2-3 times faster, but that may not matter to you, since execution times are short: about 0.15 to 0.2 seconds for `CONNECT BY` vs. 0.5 seconds even for the slower, recursive `WITH` clause approach.

As expected, the difference becomes greater with more data. When I tested on a table with 10,000 rows (do you have that many contracts?) the `CONNECT BY` query takes 0.5 seconds, while the recursive `WITH` solution that you accepted takes 9 seconds. That (as anyone can calculate) is 18 times slower. Is that OK for your purposes?

Obviously, the best way to decide, if you are going to spend time on this, is to test on your own data.

For what it's worth, the `CONNECT BY` + `LATERAL` solution (available only from version 12.1 on) runs in less than 0.3 seconds for the same 10,000 contracts. Faster, but still the same order of magnitude. Going from recursive `WITH` to `CONNECT BY`, even without `LATERAL`, is what will make by far the biggest difference.

As an aside, as I was testing, I noticed that Mr. Kulash's query isn't 100% correct. When the amount is an exact multiple of the "minimum amount" (let's say 100), and smaller than that minimum amount times the number of doses (example: min amount 100, num of doses 36, but total amount 1200), the query will produce one extra row, for an unneeded period (month), with a monthly amount of 0. Of course, that can be fixed easily; just letting you know so you can take a look at it and fix it.