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

# Calculate fixed dollar and percent-based pay bonuses cumulatively

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_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,   30, null  from dual

) select * from sample_data

Thanks,

Jon

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

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_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, 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

;

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.

• ###### 2. Re: Calculate fixed dollar and percent-based pay bonuses cumulatively

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

• ###### 3. Re: Calculate fixed dollar and percent-based pay bonuses cumulatively

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

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

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

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

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