How to Calculate Incentives on the basis of Reporting Matrix?

Manish Papreja 61 Reputation points
2023-03-20T15:16:21.0466667+00:00
Dear Experts,
I need your expert help as my knowledge is limited w.r.t. following stated problem.  
I need to calculate incentives on given sales target depending on Reporting Matrix. 
A = > Highest Level of Designation 
B=> Middle Level of Designation
C=> Lowest Level of Designation

Target of A = Target Given to A + Target given to B + Target given to C
Target of B = Target Given to B + Target given to C
Target of C = Target Given to C

Total Incentive = 10% of given Target at each level. 

The total incentive has to be distributed on the basis of level of designation. 

Following is the table with Incentive Distribution Matrix. 

+--------------+--------+--------+----------------+-----------------+------------------------+-----+-----+
| Table Matrix |        |        |                |  10% of Target  | Incentive Distribution |     |     |
+--------------+--------+--------+----------------+-----------------+------------------------+-----+-----+
| SP           | Region | Target | Reporting Head | Total Incentive | A                      | B   | C   |
| A1           | North1 | 500    | A1             | 50              | 100%                   | 0   | 0   |
| B1           | North2 | 200    | A1             | 20              | 20%                    | 80% | 0   |
| C1           | North3 | 75     | B1             | 7.5             | 5%                     | 15% | 80% |
| B2           | East1  | 90     | A1             | 9               | 20%                    | 80% | 0   |
| C2           | East2  | 50     | B2             | 5               | 5%                     | 15% | 80% |
| A2           | Mah1   | 300    | A2             | 30              | 100%                   | 0%  | 0   |
| B3           | Mah2   | 250    | A2             | 25              | 20%                    | 80% | 0   |
| C3           | Mah3   | 150    | B3             | 15              | 5%                     | 15% | 80% |
+--------------+--------+--------+----------------+-----------------+------------------------+-----+-----+

Reporting Matrix is given below for your reference:-
Reporting Matrix	C1=>B1=>A1
		            C2=>B2=>A1
		            C3=>B3>A2


The Final Targets to be calculated on the basis of Formula Column as follows:-


+----+--------+--------------------+
| SP | Target | Formula For Target |
+----+--------+--------------------+
| A1 |    915 | C1+C2+B1+B2+A1     |
| B1 |    275 | C1+B1              |
| C1 |     75 | C1                 |
| B2 |    140 | C2+B2              |
| C2 |     50 | C2                 |
| A2 |    700 | C3+B3+A2           |
| B3 |    400 | B3+C3              |
| C3 |    150 | C3                 |
+----+--------+--------------------+


Following is the sql statement for creating the table

CREATE TABLE mytable(
   SP              VARCHAR(2) NOT NULL PRIMARY KEY
  ,Region          VARCHAR(6) NOT NULL
  ,Target          INTEGER  NOT NULL
  ,Reporting_Head  VARCHAR(2) NOT NULL
  ,Total_Incentive NUMERIC(3,1) NOT NULL
);
INSERT INTO mytable(SP,Region,Target,Reporting_Head,Total_Incentive) VALUES ('A1','North1',500,'A1',50);
INSERT INTO mytable(SP,Region,Target,Reporting_Head,Total_Incentive) VALUES ('B1','North2',200,'A1',20);
INSERT INTO mytable(SP,Region,Target,Reporting_Head,Total_Incentive) VALUES ('C1','North3',75,'B1',7.5);
INSERT INTO mytable(SP,Region,Target,Reporting_Head,Total_Incentive) VALUES ('B2','East1',90,'A1',9);
INSERT INTO mytable(SP,Region,Target,Reporting_Head,Total_Incentive) VALUES ('C2','East2',50,'B2',5);
INSERT INTO mytable(SP,Region,Target,Reporting_Head,Total_Incentive) VALUES ('A2','Mah1',300,'A2',30);
INSERT INTO mytable(SP,Region,Target,Reporting_Head,Total_Incentive) VALUES ('B3','Mah2',250,'A2',25);
INSERT INTO mytable(SP,Region,Target,Reporting_Head,Total_Incentive) VALUES ('C3','Mah3',150,'B3',15);


The output needed is as follows:-

+----+--------+------------------------+
| SP | Target | Final Incentive Amount |
+----+--------+------------------------+
| A1 |    915 |                 56.425 |
| B1 |    275 |                 17.125 |
| C1 |     75 |                      6 |
| B2 |    140 |                   7.95 |
| C2 |     50 |                      4 |
| A2 |    700 |                  35.75 |
| B3 |    400 |                  22.25 |
| C3 |    150 |                     12 |
+----+--------+------------------------+
Can u please help me achieving the above output? Thanks for your help in advance. 

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
3,065 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,705 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,344 questions
SQL Server | Other
{count} votes

Accepted answer
  1. Anonymous
    2023-03-21T07:02:10.5033333+00:00

    Hi @Manish Papreja

    It took me a long time to understand your thoughts. Eventually, I designed the following query that you can give it a try.

    ;with CTE as(
      select A.SP,max(A.Target) as Tar1,max(A.Total_Incentive) as Tot1,sum(B.Target) as Tar2,
        sum(B.Total_Incentive) as Tot2,sum(C.Target) as Tar3,sum(C.Total_Incentive) as Tot3
      from mytable as A left outer join mytable as B on A.SP = B.Reporting_Head and A.SP <> B.SP
                        left outer join mytable as C on B.SP = C.Reporting_Head group by A.SP)
    select SP,Tar1 + isnull(Tar2,0) + isnull(Tar3,0) as Target, 
       case when left(SP,1) = 'A' then Tot1 + cast(0.2 * Tot2 as decimal(10,3))+ cast(0.05 * Tot3 as decimal(10,3))
            when left(SP,1) = 'B' then 0.8 * Tot1 + 0.15 * Tot2
    		when left(SP,1) = 'C' then 0.8 * Tot1 end as 'Final Incentive Amount'
    from CTE;
    

    Output:

    User's image

    Best regards,

    Percy Tang


    If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.