Hierarchy wise Sales Targets
I am having following data with me
CREATE TABLE Hierarchy_Temp (
Region VARCHAR(6) NOT NULL PRIMARY KEY
,Designation VARCHAR(11)
,"Employee Name" VARCHAR(13)
,"Sub Region" VARCHAR(10)
,"Sub Reg Emp Desigantion" VARCHAR(25)
,"Sub Reg Emp Name" VARCHAR(18)
,"Sub Reg Target" NUMERIC(15, 2)
,"Sub Reg Emp Reporting to" VARCHAR(26)
);
INSERT INTO Hierarchy_Temp (
Region
,Designation
,"Employee Name"
,"Sub Region"
,"Sub Reg Emp Desigantion"
,"Sub Reg Emp Name"
,"Sub Reg Target"
,"Sub Reg Emp Reporting to"
)
VALUES (
'North'
,'M.D.'
,'MP'
,'N1'
,'A.S.M'
,'AA'
,300
,'MP'
);
INSERT INTO Hierarchy_Temp (
Region
,Designation
,"Employee Name"
,"Sub Region"
,"Sub Reg Emp Desigantion"
,"Sub Reg Emp Name"
,"Sub Reg Target"
,"Sub Reg Emp Reporting to"
)
VALUES (
'North'
,'A.S.M'
,'AA'
,'E1KK1'
,'Asst. A.S.M'
,'AS'
,200.00
,'AA'
);
INSERT INTO Hierarchy_Temp (
Region
,Designation
,"Employee Name"
,"Sub Region"
,"Sub Reg Emp Desigantion"
,"Sub Reg Emp Name"
,"Sub Reg Target"
,"Sub Reg Emp Reporting to"
)
VALUES (
'North'
,'A.S.M'
,'AA'
,'E1KK2'
,'Sr. Sales Engineer'
,'PK'
,5.00
,'AS'
);
INSERT INTO Hierarchy_Temp (
Region
,Designation
,"Employee Name"
,"Sub Region"
,"Sub Reg Emp Desigantion"
,"Sub Reg Emp Name"
,"Sub Reg Target"
,"Sub Reg Emp Reporting to"
)
VALUES (
'North'
,'A.S.M'
,'AA'
,'N4HR6'
,'Asst. A.S.M'
,'RK'
,80.00
,'AA'
);
INSERT INTO Hierarchy_Temp (
Region
,Designation
,"Employee Name"
,"Sub Region"
,"Sub Reg Emp Desigantion"
,"Sub Reg Emp Name"
,"Sub Reg Target"
,"Sub Reg Emp Reporting to"
)
VALUES (
'North'
,'A.S.M'
,'AA'
,'N3P1'
,'Asst. A.S.M'
,'AS'
,90.00
,'AA'
);
INSERT INTO Hierarchy_Temp (
Region
,Designation
,"Employee Name"
,"Sub Region"
,"Sub Reg Emp Desigantion"
,"Sub Reg Emp Name"
,"Sub Reg Target"
,"Sub Reg Emp Reporting to"
)
VALUES (
'North'
,'A.S.M'
,'AA'
,'N3P2'
,'Sr. Sales Engineer'
,'SS'
,7.00
,'AS'
);
INSERT INTO Hierarchy_Temp (
Region
,Designation
,"Employee Name"
,"Sub Region"
,"Sub Reg Emp Desigantion"
,"Sub Reg Emp Name"
,"Sub Reg Target"
,"Sub Reg Emp Reporting to"
)
VALUES (
'North'
,'A.S.M'
,'AA'
,'CI1MP1'
,'Asst. A.S.M'
,'AM'
,260.00
,'AA'
);
INSERT INTO Hierarchy_Temp (
Region
,Designation
,"Employee Name"
,"Sub Region"
,"Sub Reg Emp Desigantion"
,"Sub Reg Emp Name"
,"Sub Reg Target"
,"Sub Reg Emp Reporting to"
)
VALUES (
'North'
,'A.S.M'
,'AA'
,'N5RJ1'
,'Sr. Sales Engineer'
,'AK'
,260.00
,'AA'
);
INSERT INTO Hierarchy_Temp (
Region
,Designation
,"Employee Name"
,"Sub Region"
,"Sub Reg Emp Desigantion"
,"Sub Reg Emp Name"
,"Sub Reg Target"
,"Sub Reg Emp Reporting to"
)
VALUES (
NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
);
INSERT INTO Hierarchy_Temp (
Region
,Designation
,"Employee Name"
,"Sub Region"
,"Sub Reg Emp Desigantion"
,"Sub Reg Emp Name"
,"Sub Reg Target"
,"Sub Reg Emp Reporting to"
)
VALUES (
'Region'
,'Designation'
,'Employee Name'
,'Sub Region'
,'Sub Reg. Emp. Desigantion'
,'Sub Reg. Emp. Name'
,Sub Reg.Target
,'Sub Reg. Emp. Reporting to'
);
INSERT INTO Hierarchy_Temp (
Region
,Designation
,"Employee Name"
,"Sub Region"
,"Sub Reg Emp Desigantion"
,"Sub Reg Emp Name"
,"Sub Reg Target"
,"Sub Reg Emp Reporting to"
)
VALUES (
'Mumbai'
,'M.D.'
,'MP'
,'W1MHM1'
,'A.S.M'
,'KRS'
,200
,'MP'
);
INSERT INTO Hierarchy_Temp (
Region
,Designation
,"Employee Name"
,"Sub Region"
,"Sub Reg Emp Desigantion"
,"Sub Reg Emp Name"
,"Sub Reg Target"
,"Sub Reg Emp Reporting to"
)
VALUES (
'Mumbai'
,'KRS'
,'A.S.M'
,'W1MHM2'
,'Asst. A.S.M'
,'PMK'
,330
,'KRS'
);
INSERT INTO Hierarchy_Temp (
Region
,Designation
,"Employee Name"
,"Sub Region"
,"Sub Reg Emp Desigantion"
,"Sub Reg Emp Name"
,"Sub Reg Target"
,"Sub Reg Emp Reporting to"
)
VALUES (
'Mumbai'
,'KRS'
,'A.S.M'
,'W1MHM3'
,'Asst. A.S.M'
,'SSS'
,490
,'KRS'
);
Need OUTPUT IN FOLLOWING
The logic to be following each person in hierarchy is having target equal to target assigned to him individually + the target assigned to his subordinates. The output gives clarity in hierarchy and targets
+------+-------------+-------+--------------+-------------+--------------------+--------------------+---------------------------+
| M.D. | M.D. Target | A.S.M | A.S.M Target | Asst. A.S.M | Asst. A.S.M Target | Sr. Sales Engineer | Sr. Sales Engineer Target |
+------+-------------+-------+--------------+-------------+--------------------+--------------------+---------------------------+
| MP | 2,222 | AA | 1,202 | AS | 205 | PK | 5 |
| RK | 81,30,000 | | | | | | |
| AS | 94,05,000 | SS | 7,05,000 | | | | |
| AM | 2,61,50,000 | | | | | | |
| AK | 2,58,45,000 | | | | | | |
| KRS | 1020 | PMK | 330 | | | | |
| SSS | 490 | | | | | | |
+------+-------------+-------+--------------+-------------+--------------------+--------------------+---------------------------+
Can anybody help me in achieving the above desired output.