January 11, 2015 at 5:52 pm
This example has been simplified so I can explain the concept I’m trying to figure out. The actual task I have will have several invoices and the compensation plan will have more levels.
Company XYZ has a bonus plan for its sales force. If the volume of the sale is between 1 and 10000 there is a $10 bonus per unit; between 10001 and 20000 $20 a unit and 20001 and above $30 a unit.
I have a sample calculation of how a single row’s bonus would be calculated but I’m confused how this would be automated to do all the rows at once.
Any suggestions would be appreciated. Thanks!
--INVOICE DATA
IF OBJECT_ID('tempdb..#RawData') > 0 DROP TABLE #RawData
CREATE TABLE #RawData (Invoice varchar(7), Quantity int, Cost numeric(11,2))
INSERT INTO #RawData
SELECT '8000001', 200, 30000 UNION ALL
SELECT '8000002', 250, 12000 UNION ALL
SELECT '8000003', 220, 10000 UNION ALL
SELECT '8000004', 280, 18000
--COMPENSATION PLAN
IF OBJECT_ID('tempdb..#CompPlan') > 0 DROP TABLE #CompPlan
CREATE TABLE #CompPlan (Tier int, LowVal numeric(11,2), HighVal numeric(11,2), Bonus numeric(11,2))
INSERT INTO #CompPlan
SELECT 1, .01, 10000, 10 UNION ALL
SELECT 2, 10000.01, 20000, 20 UNION ALL
SELECT 3, 20000.01, 99999, 30
--INVOICE 8000001’S CALCUATION
DECLARE @Cost numeric(11,2)= 30000
DECLARE @Quantity int = 200
SELECT Tier, @Quantity * Bonus *
((CASE WHEN @Cost >= LowVal AND @Cost > HighVal THEN (HighVal - LowVal + .01)
WHEN @Cost >= LowVal AND @Cost <= HighVal THEN (@Cost - LowVal + .01) ELSE 0 END) / @Cost)
FROM #CompPlan
January 11, 2015 at 6:25 pm
This is the logical equivalent of a V/HLOOKUP in Excel. Something like this should work:
DECLARE @SalePrice SMALLMONEY = 250.00;
SELECT MAX(Bonus)
FROM #CompPlan
WHERE HighVal<@SalePrice;
-- sorry, I guess I left one thing out. If you create a table that has the bonus scales, then this becomes just stupid easy. If you have to modify the bonus structure, if it's in a table it's pretty simple.
January 12, 2015 at 12:55 am
If I do a join I'm not getting the expected result because the calculation needs to cycle through every row. I'm trying to calculate the bonus on all invoices at once.
--THIS DOES NOT GIVE DESIRED RESULT
SELECT Invoice, Quantity, Cost,
((CASE WHEN Cost >= LowVal AND Cost > HighVal THEN (HighVal - LowVal + .01)
WHEN Cost >= LowVal AND Cost <= HighVal THEN (Cost - LowVal + .01) ELSE 0 END) / Cost)
FROM #RawData A
LEFT JOIN #CompPlan B
ON A.Cost BETWEEN LowVal AND HighVal
January 12, 2015 at 1:03 am
Not Correct Result (right column)
8000001200$30,000.00 0.3333
8000002250$12,000.00 0.1667
8000003220$10,000.00 1
8000004280$18,000.00 0.4444
Correct Result (right column)
8000001200$30,000.00 $4,000.00
8000002250$12,000.00 $2,916.66
8000003220$10,000.00 $2,200.00
8000004280$18,000.00 $4,044
Example of Result Needed by Level for Invoice 8000001 is $4,000.00 (sum levels 1,2,3)
1$666.67
2$1,333.33
3$2,000.00
So the lookup to the Compensation Plan table needs to go through each level, this is a waterfall type method.
January 12, 2015 at 4:13 am
Quick simple solution, note that the output is not formatted but that's easy
😎
USE tempdb;
GO
SET NOCOUNT ON;
--INVOICE DATA
IF OBJECT_ID('tempdb..#RawData') IS NOT NULL DROP TABLE #RawData;
CREATE TABLE #RawData (Invoice varchar(7), Quantity int, Cost numeric(11,2));
INSERT INTO #RawData
SELECT '8000001', 200, 30000 UNION ALL
SELECT '8000002', 250, 12000 UNION ALL
SELECT '8000003', 220, 10000 UNION ALL
SELECT '8000004', 280, 18000;
--COMPENSATION PLAN
IF OBJECT_ID('tempdb..#CompPlan') IS NOT NULL DROP TABLE #CompPlan;
CREATE TABLE #CompPlan (Tier int, LowVal numeric(11,2), HighVal numeric(11,2), Bonus numeric(11,2));
INSERT INTO #CompPlan
SELECT 1, .01, 10000, 10 UNION ALL
SELECT 2, 10000.01, 20000, 20 UNION ALL
SELECT 3, 20000.01, 99999, 30 ;
SELECT
RD.Invoice
,RD.Quantity
,RD.Cost
,SUM((CASE
WHEN RD.Cost > CP.HighVal THEN (CP.HighVal - CP.LowVal + 0.01)
WHEN RD.Cost BETWEEN CP.LowVal AND CP.HighVal THEN (RD.Cost - CP.LowVal + 0.01)
ELSE 0
END / RD.Cost) * RD.Quantity * CP.Bonus) AS BONUS
FROM #RawData RD
OUTER APPLY #CompPlan CP
GROUP BY RD.Invoice
,RD.Quantity
,RD.Cost;
Results
Invoice Quantity Cost BONUS
------- ---------- ---------- ------------
8000001 200 30000.00 4000.000000
8000002 250 12000.00 2916.666666
8000003 220 10000.00 2200.000000
8000004 280 18000.00 4044.444445
January 12, 2015 at 5:24 am
wow thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply