Targets as per Reporting Matrix

Question

Dear Experts,

I am having a Sales Data with assigned Sales Targets. Final Targets are to be calculated as per reporting matrix.

A=>Highest Level

B=>Middle Level

C=>Lowest Level

that means C is reporting to B and B inturn is reporting to A.

Target of A = Target of A + Target of B + Target of C

Target of B = Target of B + Target of C

Target of C = Target Assigned.

The sample table containing the data is as follows:-

+--------+--------+-------------------+

| SPName | Target | ReportingHeadName |

+--------+--------+-------------------+

| A | 200 | A |

| B1 | 100 | A |

| B2 | 300 | A |

| C1 | 50 | B1 |

| C2 | 75 | B1 |

| C3 | 60 | B2 |

+--------+--------+-------------------+

Following is query to create sample table i.e. mytable4

CREATE TABLE mytable4(
   SPName            VARCHAR(2) NOT NULL PRIMARY KEY
  ,Target            INTEGER  NOT NULL
  ,ReportingHeadName VARCHAR(2) NOT NULL
);
INSERT INTO mytable4(SPName,Target,ReportingHeadName) VALUES ('A',200,'A');
INSERT INTO mytable4(SPName,Target,ReportingHeadName) VALUES ('B1',100,'A');
INSERT INTO mytable4(SPName,Target,ReportingHeadName) VALUES ('B2',300,'A');
INSERT INTO mytable4(SPName,Target,ReportingHeadName) VALUES ('C1',50,'B1');
INSERT INTO mytable4(SPName,Target,ReportingHeadName) VALUES ('C2',75,'B1');
INSERT INTO mytable4(SPName,Target,ReportingHeadName) VALUES ('C3',60,'B2');

the desired output is as follows:-

+--------+-------------+

| SPName | FinalTarget |

+--------+-------------+

| A | 785 |

| B1 | 225 |

| B2 | 360 |

| C1 | 50 |

| C2 | 75 |

| C3 | 60 |

+--------+-------------+

As per the suggestion given to me by an helpful expert the following query was designed

With CTE 
As
(
	Select
		T1.SPName
		,MAX(T1.Target) as T1
		,SUM(T2.Target) as T2
		,sum(T3.Target) as T3
	From
		mytable4 as T1
		Left Outer Join mytable4 as T2 on T1.SPName = T2.ReportingHeadName AND T1.SPName <> T2.SPName
		Left Outer Join mytable4 as T3 on T2.SPName = T3.ReportingHeadName 
	GrouP by
		T1.SPName
)
Select
	C.SPName,C.T1+ISNULL(C.T2,0)+ISNULL(C.T3,0) as FinalTarget
From
	CTE as C

But the output I am getting is as follows:-

+--------+-------------+

| SPName | FinalTarget |

+--------+-------------+

| A | 885 |

| B1 | 225 |

| B2 | 360 |

| C1 | 50 |

| C2 | 75 |

| C3 | 60 |

+--------+-------------+

The only problem in the above output is Final Target Value of A which is 885 but actually it should have been 785. The target column cannot be totalled as it is for position A as there may multiple employees at Top Level i.e. A1, A2 A3 whose reporting matrix would be different from A (as above).

Can somebody help me to achieve the desired output by giving your valuable suggestion/advice?

Thanks in advance for your help.

Answer 1

Hi @Manish Papreja

I'm sorry. Because your table's reporting structure is different from the last question, the last query doesn't apply here.

If the report structure is different, you need to design the query according to the new structure.

For this report structure, you can try this query.

;with CTE1 as(
   Select
		T1.SPName as T1SPName,T2.SPName as T2SPName,max(T1.Target) as T1,max(T2.Target)as T2,sum(T3.Target) as T3
	From
		mytable4 as T1
		Left Outer Join mytable4 as T2 on T1.SPName = T2.ReportingHeadName AND T1.SPName <> T2.SPName
		Left Outer Join mytable4 as T3 on T2.SPName = T3.ReportingHeadName 
	group by T1.SPName,T2.SPName
),CTE2 as(
    Select T1SPName,MAX(T1) as T1,SUM(T2) as T2,SUM(T3) as T3 from CTE1 GROUP BY T1SPName)
Select T1SPName as SPName,T1+ISNULL(T2,0)+ISNULL(T3,0) as FinalTarget from CTE2;

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.

Answer 2

Hi @Manish Papreja

For your new needs, you can try this query.

;with CTE1 as(
  select SPName,Target,ReportingHeadName from mytable4
  union all
  select ReportingHeadName,Target,null from mytable4 as A
  where not exists (select SPName from mytable4 as B  where B.SPName = A.ReportingHeadName)
),CTE2 as(
  select 
	T1.SPName as T1SPName,T2.SPName as T2SPName,max(T1.Target) as T1,
	max(T2.Target)as T2,sum(T3.Target) as T3
  from CTE1 as T1
	   Left Outer Join CTE1 as T2 on T1.SPName = T2.ReportingHeadName 
	   AND T1.SPName <> T2.SPName AND T1.ReportingHeadName is not null
	   Left Outer Join CTE1 as T3 on T2.SPName = T3.ReportingHeadName 
  group by T1.SPName,T1.ReportingHeadName,T2.SPName
),CTE3 as(
  Select T1SPName,MAX(T1) as T1,SUM(T2) as T2,SUM(T3) as T3 from CTE2 
  where T2SPName is not null GROUP BY T1SPName
  union all
  Select T1SPName,SUM(T1) as T1,SUM(T2) as T2,SUM(T3) as T3 from CTE2 
  where T2SPName is null GROUP BY T1SPName)
Select T1SPName as SPName,T1+ISNULL(T2,0)+ISNULL(T3,0) as FinalTarget from CTE3;

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.

Answer 3

Hi @Manish Papreja

In the desired output section of your latest message, the FinalTarget corresponding to A3 is still 55.

According to what you mean, I guess it should be 75.

You can try this query.

;with CTE1 as(
  select SPName,Target,ReportingHeadName from mytable4
  union all
  select ReportingHeadName,Target,null from mytable4 as A
  where not exists (select SPName from mytable4 as B  where B.SPName = A.ReportingHeadName)
),CTE2 as(
  select 
	T1.SPName as T1SPName,T2.SPName as T2SPName,sum(T1.Target) as T1,
	sum(T2.Target)as T2,sum(T3.Target) as T3
  from CTE1 as T1
	   Left Outer Join CTE1 as T2 on T1.SPName = T2.ReportingHeadName 
	   AND T1.SPName <> T2.SPName AND T1.ReportingHeadName is not null
	   Left Outer Join CTE1 as T3 on T2.SPName = T3.ReportingHeadName 
  group by T1.SPName,T1.ReportingHeadName,T2.SPName,T3.SPName
),CTE3 as(
  select T1SPName,max(T1) as T1,max(T2) as T2,sum(T3) as T3 from CTE2
  where T2SPName is not null group by T1SPName,T2SPName 
  union all
  select T1SPName,sum(T1) as T1,sum(T2) as T2,sum(T3) as T3 from CTE2
  where T2SPName is null group by T1SPName 
),CTE4 as(
  select T1SPName,max(T1) as T1,sum(T2) as T2,sum(T3) as T3 from CTE3
  group by T1SPName)
Select T1SPName as SPName,T1+ISNULL(T2,0)+ISNULL(T3,0) as FinalTarget from CTE4;

Output:

By the way, please feel free to ask questions.

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.