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

# Calculate bonus pay similar to compound interest

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_NO PAY_TYPE MONTHLY_PAY_AMOUNT BONUS_PERCENT EXPECTED_AMOUNT calculation e1 base pay 1000 0 1000 1000 e1 bonus 1 0 0.03 30 1000 * 0.03 e1 bonus 2 0 0.035 36.05 1030 * 0.035 e1 bonus 3 0 0.04 42.642 1066.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

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

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

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

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

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

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

Hi Paulzip,

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

Jon

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

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_TYPE Priority MONTHLY_PAY_AMOUNT BONUS AMOUNT BONUS_PERCENT EXPECTED_AMOUNT calculation e1 base pay 1000 0 1000 1000 e1 bonus 1 1 0 0.03 30 1000 * 0.03 e1 bonus 2 2 0 0.035 36.05 1030 * 0.035 e1 bonus 3 3 0 0.04 42.642 1066.05 * .04 e1 bonus 4 4 0 20 20 1108.69 e1 bonus 5 5 0 0.045 50.79105 1128.69 * 0.045 e1 bonus 6 6 0 30 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

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

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