Tiered Commission Plan

  • texpic

    SSCertifiable

    Points: 5882

    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

  • pietlinden

    SSC Guru

    Points: 62816

    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.

  • texpic

    SSCertifiable

    Points: 5882

    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

  • texpic

    SSCertifiable

    Points: 5882

    Not Correct Result (right column)

    8000001 200 $30,000.00 0.3333

    8000002 250 $12,000.00 0.1667

    8000003 220 $10,000.00 1

    8000004 280 $18,000.00 0.4444

    Correct Result (right column)

    8000001 200 $30,000.00 $4,000.00

    8000002 250 $12,000.00 $2,916.66

    8000003 220 $10,000.00 $2,200.00

    8000004 280 $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.

  • Eirikur Eiriksson

    SSC Guru

    Points: 182438

    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

  • texpic

    SSCertifiable

    Points: 5882

    wow thanks

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply