Calculate bonus pay similar to compound interest

Jonk

    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
          Jonk

          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 位用户发现它有用
            • 4. Re: Calculate bonus pay similar to compound interest
              Jonk

              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 位用户发现它有用
                • 6. Re: Calculate bonus pay similar to compound interest
                  Jonk

                  Hi Paulzip,

                   

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

                   

                  Jon

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

                    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
                        Jonk

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