9 Replies Latest reply on Dec 13, 2018 7:07 AM by user13058610

    Calculate bonus pay similar to compound interest

    user13058610

      Hi Community Members,

       

      I'm running on Oracle 12c. I need a way to calculate pay bonuses using the compound method. I want to do it in SQL and not PL/SQL. Please show me the way.

       

      1st bonus amount: 1000 (base pay) * 0.03 (1st bonus) = 30

      2nd bonus amount: 1030 * 0.035 = 36.05

      3rd bonus amount: 1066.05 * 0.04 = 42.642

       

      Here's the expected results:

      EMPL_NOPAY_TYPEMONTHLY_PAY_AMOUNTBONUS_PERCENTEXPECTED_AMOUNTcalculation
      e1base pay1000010001000
      e1bonus 100.03301000 * 0.03
      e1bonus 200.03536.051030 * 0.035
      e1bonus 300.0442.6421066.05 * .04

       

      Here's the sample data:

      select 'e1' as empl_no, 'base pay' as pay_type, 1000 as monthly_pay_amount, 0 as bonus_percent from dual

      union all

      select 'e1', 'bonus 1', 0, 0.03 from dual

      union all

      select 'e1', 'bonus 2', 0, 0.035 from dual

      union all

      select 'e1', 'bonus 3', 0, 0.04 from dual

       

      Thanks,

      Jon

        • 1. Re: Calculate bonus pay similar to compound interest
          mathguy

          Don't you also have to do this for different months, not just for different employees?

           

          I wrote the code so it's easy to modify if you have more than three bonuses for an employee, or - much better! - if there is an additional "priority" column similar to the one I calculate below, aliased as RN.

           

          with

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

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

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

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

              select 'e1', 'bonus 3' ,    0, 0.04  from dual

            )

          , prep(empl_no, pay_type, monthly_pay_amount, bonus_percent, rn) as (

              select empl_no, pay_type, monthly_pay_amount, bonus_percent,

                     case pay_type when 'base pay' then 0

                                   when 'bonus 1'  then 1

                                   when 'bonus 2'  then 2

                                   when 'bonus 3'  then 3 end

              from   sample_data

            )

          , cumulative_bonus(empl_no, pay_type, monthly_pay_amount, bonus_percent, rn, c_bonus) as (

              select empl_no, pay_type, monthly_pay_amount, bonus_percent, rn,

                     min(monthly_pay_amount) keep (dense_rank first order by rn) over (partition by empl_no)

                     *

                     exp(sum(ln(1+bonus_percent)) over (partition by empl_no order by rn))

              from   prep

            )

          select empl_no, pay_type, monthly_pay_amount, bonus_percent,

                 round(c_bonus - lag(c_bonus, 1, 0) over (partition by empl_no order by rn), 2) as expected_amount

          from   cumulative_bonus

          -- order by <whatever> (perhaps empl_no, rn)

          ;

           

          EMPL_NO PAY_TYPE MONTHLY_PAY_AMOUNT BONUS_PERCENT EXPECTED_AMOUNT

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

          e1      base pay               1000             0            1000

          e1      bonus 1                   0           .03              30

          e1      bonus 2                   0          .035           36.05

          e1      bonus 3                   0           .04           42.64

           

           

          As you may notice, too, I did something funny with the "base amount". I wrote the query in such a way that it picks up the amount from the BASE PAY row, and it ignores anything in the MONTHLY_PAY_AMOUNT column in the BONUS rows. It's not even clear why the MONTHLY_PAY_AMOUNT is a column in THIS table - it should be in a different, smaller table.

          • 2. Re: Calculate bonus pay similar to compound interest
            user13058610

            Hi Mr. Mathguy,

             

            You're right. I forgot about having to do it for different months because bonuses could have been expired from one month to the next. In that case, I can add the month into the 'partition by' clause, right? You're also right on "priority" as certain category of bonuses have to go first. Wow, you're amazing!

             

            You solution does exactly what I am looking for. Oracle analytic function can do amazing stuffs. Thanks for your wisdom.

             

            Jon

            • 3. Re: Calculate bonus pay similar to compound interest
              Frank Kulash

              Hi, Jon,

               

              Here's another way.

              Calculating the expected_amount on the 1st row is easy.

              Calculating the expected amount on row N (N > 1) would be pretty easy if you knew what the expected_amount on row N-1 was.

              That sounds like a job for a recursive WITH clause, like this:

              WITH    got_rn    AS

              (

                  SELECT  empl_no, pay_type, monthly_pay_amount, bonus_percent

                  ,       ROW_NUMBER () OVER ( PARTITION BY  empl_no

                                               ORDER BY      pay_type  -- or whatever

                                             )  AS rn

                  FROM    sample_data

              )

              ,    r ( empl_no, pay_type, monthly_pay_amount, bonus_percent

                     , rn, net_amount

                     )   AS

              (

                  SELECT  empl_no, pay_type, monthly_pay_amount, bonus_percent, rn

                  ,       monthly_pay_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_percent, gr.rn

                  ,       r.net_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_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

              ;

              This assumes you have some way of putting the rows in order.  As posted above, that is just pay_type, which happens to work for this sample data, but wouldn't work if pay_type was 'starting amount' instead of 'base pay', or 'first bonus' instead of 'bonus 1'.  You may have some column in your real table that determines the order; if not, you should create one.  It doesn't need to be consecutive integers, it doesn't need to be integers, it doesn't even need to be numbers; it just has to be unique for each empl_no (and month, if you're partitioning by month, too).

              1 person found this helpful
              • 4. Re: Calculate bonus pay similar to compound interest
                user13058610

                Hi Mr. Kulash,

                 

                Your method works too. I love this community. So many knowledgeable members willing to help.

                 

                Thanks,

                Jon

                • 5. Re: Calculate bonus pay similar to compound interest
                  Paulzip

                  Yet you marked your own reply as the correct answer...

                  1 person found this helpful
                  • 6. Re: Calculate bonus pay similar to compound interest
                    user13058610

                    Hi Paulzip,

                     

                    My bad. It's now fixed. Thanks for pointing it out.

                     

                    Jon

                    • 7. Re: Calculate bonus pay similar to compound interest
                      user13058610

                      Hi Community Members,

                       

                      In my initial post, I was not aware that some bonuses are fixed dollar-based. My scenario was only for percent-based bonuses. Mr. Mathguy gave me a solution and it worked perfectly until I came across employees that had both percent-based and fixed dollar-based bonuses. I need help with the following revised scenario. The last line 'Total Gross Pay' is just to show that ultimately I would sum up the expected dollar amount to get the total gross pay. I don't need it to be displayed in the solution.

                             

                      EMPL_NO
                      PAY_TYPEPriorityMONTHLY_PAY_AMOUNTBONUS AMOUNTBONUS_PERCENTEXPECTED_AMOUNTcalculation
                      e1base pay 1000 010001000
                      e1bonus 110 0.03301000 * 0.03
                      e1bonus 220 0.03536.051030 * 0.035
                      e1bonus 330 0.0442.6421066.05 * .04 
                      e1bonus 44020 201108.69
                      e1bonus 550 0.04550.791051128.69 * 0.045
                      e1bonus 66030 30
                      -----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,   40, null  from dual

                        )

                       

                      Thanks,

                      Jon

                      • 8. Re: Calculate bonus pay similar to compound interest
                        mathguy

                        That's not how it works. The question you asked initially made sense, and it got good answers. If you have a different question, start a new thread. If you must refer to your older question (the one in this thread), as you no doubt do in this case, you can say so in your new question, and provide a link to the old one.

                        • 9. Re: Calculate bonus pay similar to compound interest
                          user13058610

                          Thanks, Mr. Mathguy. I'm new on this board. I'll mark your answer as correct and will start a new thread.