Split amount with minimum amount per month

Prokopis · Sep 23, 2021 1:54PM

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

Frank Kulash · Sep 23, 2021 3:15PM

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
, 	 ADD_MONTHS ( TO_DATE ('25/09/2021','dd/mm/yyyy')
		    , 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.

Frank Kulash · Sep 23, 2021 2:01PM

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.

Prokopis · Sep 23, 2021 2:24PM

Thanks for your comment

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

Solomon Yakobson · Sep 23, 2021 2:27PM

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.

Frank Kulash · Sep 23, 2021 2:32PM

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
, 	 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
;

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

mathguy · Sep 23, 2021 2:36PM

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

mathguy · Sep 23, 2021 2:55PM

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
;

EdStevens · Sep 23, 2021 2:56PM

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

OUCH! That is completely out of support.

Frank Kulash · Sep 23, 2021 3:15PM

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
, 	 ADD_MONTHS ( TO_DATE ('25/09/2021','dd/mm/yyyy')
		    , 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.

Prokopis · Sep 23, 2021 6:08PM

Thanks a lot for all you answers !!

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

Thanks again

mathguy · Sep 23, 2021 8:08PM

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.

Forum Stats

Quick Links

Categories

Split amount with minimum amount per month

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Split amount with minimum amount per month

Best Answer

Answers