How to Calculate Incentives on the basis of Reporting Matrix?

Question

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. 

Answer 1

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:

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.