7 Replies Latest reply on Dec 13, 2018 7:25 PM by user13058610

    Calculate fixed dollar and percent-based pay bonuses cumulatively

    user13058610

      Hi Community Members,

       

      I'm using Oracle 12c. I need your help in solving a problem using SQL and not PL/SQL. I need a solution to calculate gross pay starting from base pay and cumulatively adding in the various percent-based and fixed dollar-based bonuses. The bonuses are to be calculated in the ascending priority order. Some bonuses are percent-based while others are fixed dollar based.

       

      I initially posted this question for percent-based only. Mr. Mathguy and Mr. Kulash gave me a solution for it and they both did exactly as I asked for. Here's a link to that post, https://community.oracle.com/thread/4189022. However, I found out that some bonuses are fixed dollar and their solutions do not work in those cases. Here's the revised scenario. The 'Total Gross Pay' line is just to show what I need to get to at the end, which is the sum of the Expected Amount column. It does not have to be accounted for in your SQL solution.

                                                                                                                                                    

      EMPL_NO
      PAY_TYPEPriorityMONTHLY_PAY_AMOUNTBONUS AMOUNTBONUS_PERCENTEXPECTED_AMOUNTcalculation
      e1base pay1000010001000
      e1bonus 1100.03301000 * 0.03
      e1bonus 2200.03536.051030 * 0.035
      e1bonus 3300.0442.6421066.05 * .04  
      e1bonus 44020201108.69
      e1bonus 5500.04550.791051128.69 * 0.045
      e1bonus 6603030
      -----Total Gross Pay:1209.48305-

       

      Here's the sample data:

       

      with

        sample_data(empl_no, pay_type, bonus_priority, monthly_pay_amount, bonus_amount, bonus_percent) as (

          select 'e1', 'base pay', null, 1000, null, 0     from dual union all

          select 'e1', 'bonus 1' ,    1,    0, null, 0.03  from dual union all

          select 'e1', 'bonus 2' ,    2,    0, null, 0.035 from dual union all

          select 'e1', 'bonus 3' ,    3,    0, null, 0.04  from dual union all

          select 'e1', 'bonus 4' ,    4,    0,   20, null  from dual union all

          select 'e1', 'bonus 5' ,    5,    0, null, 0.045 from dual union all

          select 'e1', 'bonus 6' ,    6,    0,   30, null  from dual

        ) select * from sample_data

       

      Thanks,

      Jon

        • 1. Re: Calculate fixed dollar and percent-based pay bonuses cumulatively
          Frank Kulash

          Hi, Jon,

          user13058610 wrote:

           

          Hi Community Members,

           

          I'm using Oracle 12c. I need your help in solving a problem using SQL and not PL/SQL. I need a solution to calculate gross pay starting from base pay and cumulatively adding in the various percent-based and fixed dollar-based bonuses. The bonuses are to be calculated in the ascending priority order. Some bonuses are percent-based while others are fixed dollar based.

           

          I initially posted this question for percent-based only. Mr. Mathguy and Mr. Kulash gave me a solution for it and they both did exactly as I asked for. Here's a link to that post, https://community.oracle.com/thread/4189022. However, I found out that some bonuses are fixed dollar and their solutions do not work in those cases. Here's the revised scenario. The 'Total Gross Pay' line is just to show what I need to get to at the end, which is the sum of the Expected Amount column. It does not have to be accounted for in your SQL solution.

           

          EMPL_NO
          PAY_TYPEPriorityMONTHLY_PAY_AMOUNTBONUS AMOUNTBONUS_PERCENTEXPECTED_AMOUNTcalculation
          e1base pay1000010001000
          e1bonus 1100.03301000 * 0.03
          e1bonus 2200.03536.051030 * 0.035
          e1bonus 3300.0442.6421066.05 * .04
          e1bonus 44020201108.69
          e1bonus 5500.04550.791051128.69 * 0.045
          e1bonus 6603030
          -----Total Gross Pay:1209.48305-

           

          Here's the sample data:

           

          with

          sample_data(empl_no, pay_type, bonus_priority, monthly_pay_amount, bonus_amount, bonus_percent) as (

          select 'e1', 'base pay', null, 1000, null, 0 from dual union all

          select 'e1', 'bonus 1' , 1, 0, null, 0.03 from dual union all

          select 'e1', 'bonus 2' , 2, 0, null, 0.035 from dual union all

          select 'e1', 'bonus 3' , 3, 0, null, 0.04 from dual union all

          select 'e1', 'bonus 4' , 4, 0, 20, null from dual union all

          select 'e1', 'bonus 5' , 5, 0, null, 0.045 from dual union all

          select 'e1', 'bonus 6' , 6, 0, 30, null from dual

          ) select * from sample_data

           

          Thanks,

          Jon

          So, in the earlier problem, you wanted multiply the amount at every step.

          Now, you may want to add or multiply (or, perhaps, even both) at every step.

          You can modify the recursive WITH solution from the earlier thread like this:

          WITH    got_rn    AS

          (

              SELECT  empl_no, pay_type, monthly_pay_amount

              ,       NVL (bonus_amount, 0)    AS bonus_amount

              ,       NVL ( bonus_percent, 0)  AS bonus_percent

              ,       ROW_NUMBER () OVER ( PARTITION BY  empl_no

                                           ORDER BY      bonus_priority  NULLS FIRST

                                         )     AS rn

              FROM    sample_data

          )

          ,    r ( empl_no, pay_type, monthly_pay_amount, bonus_amount, bonus_percent

                 , rn, net_amount

                 )   AS

          (

              SELECT  empl_no, pay_type, monthly_pay_amount, bonus_amount, bonus_percent, rn

              ,       (monthly_pay_amount + bonus_amount) * (1 + bonus_percent)  AS net_amount

              FROM    got_rn  ge

              WHERE   rn  = 1

          UNION ALL

              SELECT  gr.empl_no, gr.pay_type, gr.monthly_pay_amount, gr.bonus_amount, gr.bonus_percent, gr.rn

              ,       (r.net_amount + gr.bonus_amount) * (1 + gr.bonus_percent)  AS net_amount

              FROM    got_rn  gr

              JOIN    r  ON   gr.empl_no  = r.empl_no

                         AND  gr.rn       = r.rn  + 1

          )

          SELECT    empl_no, pay_type, monthly_pay_amount, bonus_amount, bonus_percent

          ,         net_amount - LAG (net_amount, 1, 0) OVER ( PARTITION BY  empl_no

                                                               ORDER BY      rn

                                                             )  AS expected_amount

          FROM      r

          ORDER BY  empl_no, rn

          ;

          Output from your sample data:

          EM PAY_TYPE MONTHLY_PAY_AMOUNT BONUS_AMOUNT BONUS_PERCENT EXPECTED_AMOUNT

          -- -------- ------------------ ------------ ------------- ---------------

          e1 base pay               1000            0             0            1000

          e1 bonus 1                   0            0           .03              30

          e1 bonus 2                   0            0          .035           36.05

          e1 bonus 3                   0            0           .04          42.642

          e1 bonus 4                   0           20             0              20

          e1 bonus 5                   0            0          .045        50.79114

          e1 bonus 6                   0           30             0              30

          What results would you want if, on the sane row, both bonus_amount and bonus_percent had values?  The solution above adds before multiplying, but you could easily multiply first, if you wanted to.

          1 person found this helpful
          • 2. Re: Calculate fixed dollar and percent-based pay bonuses cumulatively
            mathguy

            This type of problem (which really can't be solved with analytic functions - or the similar tool for such computations, the MATCH_RECOGNIZE clause) is a perfect application of the MODEL clause, which has been around since Oracle 10 but is not much used (because MOST problems, but not this type specifically, can be solved in better ways).

             

            Recursive WITH clause solutions work too; Mr. Kulash showed how in Reply 1. It will be interesting if you can test BOTH solutions on your actual data and let us know if you notice any differences.

             

            Link to the documentation for the MODEL clause:  https://docs.oracle.com/database/121/DWHSG/sqlmodel.htm#DWHSG-GUID-538F78AA-9BF3-46F2-93D1-39A8739B3237

             

            The query is slightly complicated by the BONUS_PRIORITY values being NULL, 1, 2, 3, ...  -  it would be better if they were 0, 1, 2, 3, ...    Like Mr. Kulash, I allowed for the possibility that a single row may include both a (non-null) amount AND a (non-null) percentage bonus, in which case the percentage is applied first, and then the fixed amount is added. As in his answer, the order can be changed (or nothing needs to be touched if each row always has EITHER a fixed dollar amount OR a percentage, never both).

             

            The cumulative amount up to the end of each step is needed for the following step, if the following step is a percentage bonus. So it is easier to compute the cumulative amount, and then to compute the "expected" amount for each step. I show both columns in the output, since you probably need the final CUMULATIVE amount more than any of the other numbers calculated in the query.

             

            with

              sample_data(empl_no, pay_type, bonus_priority, monthly_pay_amount, bonus_amount, bonus_percent) as (

                select 'e1', 'base pay', null, 1000, null, 0     from dual union all

                select 'e1', 'bonus 1' ,    1,    0, null, 0.03  from dual union all

                select 'e1', 'bonus 2' ,    2,    0, null, 0.035 from dual union all

                select 'e1', 'bonus 3' ,    3,    0, null, 0.04  from dual union all

                select 'e1', 'bonus 4' ,    4,    0,   20, null  from dual union all

                select 'e1', 'bonus 5' ,    5,    0, null, 0.045 from dual union all

                select 'e1', 'bonus 6' ,    6,    0,   30, null  from dual

              )

            select empl_no, pay_type, p as bonus_priority, monthly_pay_amount, bonus_amount, bonus_percent,

                   e as expected_amount, c as cumulative_amount

            from   sample_data

            model

              partition by (empl_no )

              dimension by (bonus_priority as p)

              measures     (pay_type, monthly_pay_amount, bonus_amount, bonus_percent, cast(null as number) as c, cast(null as number) as e)

              rules        (c[null]   = monthly_pay_amount[null],

                            c[p >= 1] = c[nullif(cv(p)-1, 0)] * (1 + nvl(bonus_percent[cv(p)], 0)) + nvl(bonus_amount[cv(p)], 0),

                            e[p >= 1] = c[cv(p)] - c[nullif(cv(p)-1, 0)]

                           )

            order by empl_no, bonus_priority nulls first

            ;

             

            EMPL_NO PAY_TYPE BONUS_PRIORITY MONTHLY_PAY_AMOUNT BONUS_AMOUNT BONUS_PERCENT EXPECTED_AMOUNT CUMULATIVE_AMOUNT

            ------- -------- -------------- ------------------ ------------ ------------- --------------- -----------------

            e1      base pay                              1000                          0                              1000

            e1      bonus 1              1                   0                        .03              30              1030

            e1      bonus 2              2                   0                       .035           36.05           1066.05

            e1      bonus 3              3                   0                        .04          42.642          1108.692

            e1      bonus 4              4                   0           20                            20          1128.692

            e1      bonus 5              5                   0                       .045        50.79114        1179.48314

            e1      bonus 6              6                   0           30                            30        1209.48314

            1 person found this helpful
            • 3. Re: Calculate fixed dollar and percent-based pay bonuses cumulatively
              user13058610

              Hi Mr. Kulash,

               

              I applied your solution to the problem and got what I needed.

               

              Thank you so much,

              Jon

              • 4. Re: Calculate fixed dollar and percent-based pay bonuses cumulatively
                user13058610

                Hi Mr. Mathguy,

                 

                My eyes lit up when I saw your MODEL clause. I've been studying up on it but still do not know how to apply it to this case. I also have other cases where modeling would be very useful. I'll study both your solution and Mr. Kulash's solution.

                 

                Querying the database alone, I don't see a case where a bonus has both a fixed dollar amount and also percent, though I'll confirm that with the user manager.

                 

                As a novice in Oracle SQL, I cannot express my gratitude enough for the willingness of the Oracle community to help me. Mr. Kulash and Mr. Mathguy came through for me on my first problem and again on the revised problem. I deeply appreciate both of you. I'll keep on learning from your deep knowledge.

                 

                Jon

                • 5. Re: Calculate fixed dollar and percent-based pay bonuses cumulatively
                  user13058610

                  Hi Mr. Mathguy,

                   

                  How would your code change if the priority always have a value and base pay always has the lowest value like 0?

                   

                  Thanks,

                  Jon

                  • 6. Re: Calculate fixed dollar and percent-based pay bonuses cumulatively
                    mathguy

                    Suppose you changed NULL to 0 in the inputs. Then the RULES clause (within MODEL) can be written as

                     

                      rules ( c[0]      = monthly_pay_amount[0],

                              c[p >= 1] = c[cv(p)-1] * (1 + nvl(bonus_percent[cv(p)], 0)) + nvl(bonus_amount[cv(p)], 0),

                              e[p >= 1] = c[cv(p)] - c[cv(p)-1]

                            )

                     

                    And, of course, you wouldn't need to say "nulls first" in the ORDER BY clause (at the end of the query).

                    • 7. Re: Calculate fixed dollar and percent-based pay bonuses cumulatively
                      user13058610

                      Hi Mr. Mathguy and Mr. Kulash,

                       

                      I confirmed that a bonus can only be either dollar-based or percent-based. Both of your solutions work as intended. Thank you so very much for your help.

                       

                      Jon