
1. Re: Calculate fixed dollar and percentbased pay bonuses cumulatively
Frank Kulash Dec 13, 2018 12:27 PM (in response to user13058610)1 person found this helpfulHi, 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 percentbased and fixed dollarbased bonuses. The bonuses are to be calculated in the ascending priority order. Some bonuses are percentbased while others are fixed dollar based.
I initially posted this question for percentbased 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_NOPAY_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
;
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.

2. Re: Calculate fixed dollar and percentbased pay bonuses cumulatively
mathguy Dec 13, 2018 4:13 PM (in response to user13058610)1 person found this helpfulThis 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#DWHSGGUID538F78AA9BF346F293D139A8739B3237
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 (nonnull) amount AND a (nonnull) 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 percentbased pay bonuses cumulatively
user13058610 Dec 13, 2018 4:20 PM (in response to Frank Kulash)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 percentbased pay bonuses cumulatively
user13058610 Dec 13, 2018 4:37 PM (in response to mathguy)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 percentbased pay bonuses cumulatively
user13058610 Dec 13, 2018 4:54 PM (in response to mathguy)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 percentbased pay bonuses cumulatively
mathguy Dec 13, 2018 5:09 PM (in response to user13058610)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 percentbased pay bonuses cumulatively
user13058610 Dec 13, 2018 7:25 PM (in response to mathguy)Hi Mr. Mathguy and Mr. Kulash,
I confirmed that a bonus can only be either dollarbased or percentbased. Both of your solutions work as intended. Thank you so very much for your help.
Jon