Forum Stats

  • 3,770,503 Users
  • 2,253,127 Discussions
  • 7,875,489 Comments

Discussions

Split amount with minimum amount per month

Prokopis
Prokopis Member Posts: 106 Red Ribbon
edited Sep 23, 2021 1:54PM in SQL & PL/SQL

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

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond
    Accepted Answer

    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.

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond

    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
    Prokopis Member Posts: 106 Red Ribbon

    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
    Solomon Yakobson Member Posts: 18,926 Red Diamond
    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
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond

    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
    mathguy Member Posts: 10,167 Blue Diamond

    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
    mathguy Member Posts: 10,167 Blue Diamond

    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
    ;
    
    User_WI23P
  • EdStevens
    EdStevens Member Posts: 28,536 Gold Crown

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

    OUCH! That is completely out of support.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond
    Accepted Answer

    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
    Prokopis Member Posts: 106 Red Ribbon

    Thanks a lot for all you answers !!


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

    Thanks again

  • mathguy
    mathguy Member Posts: 10,167 Blue Diamond

    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.