Hierarchy wise Sales Targets

Transact-SQL
1

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.

2

the DDL & DML that you provided is not working

  1. Region is a primary key and you have 2 INSERT statement with same Region value
  2. One of the insert statement, you attempt to insert string into the column Sub Reg Target

Please verify your DDL & DML and also format the expected result using the </> button

3 
Hi 

Hope this helps 

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'),
('North', 'A.S.M', 'AA', 'E1KK1', 'Asst. A.S.M', 'AS', 200.00, 'AA'),
('North', 'A.S.M', 'AA', 'E1KK2', 'Sr. Sales Engineer', 'PK', 5.00, 'AS'),
('North', 'A.S.M', 'AA', 'N4HR6', 'Asst. A.S.M', 'RK', 80.00, 'AA'),
('North', 'A.S.M', 'AA', 'N3P1', 'Asst. A.S.M', 'AS', 90.00, 'AA'),
('North', 'A.S.M', 'AA', 'N3P2', 'Sr. Sales Engineer', 'SS', 7.00, 'AS'),
('North', 'A.S.M', 'AA', 'CI1MP1', 'Asst. A.S.M', 'AM', 260.00, 'AA'),
('North', 'A.S.M', 'AA', 'N5RJ1', 'Sr. Sales Engineer', 'AK', 260.00, 'AA'),
(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
('Region', 'Designation', 'Employee Name', 'Sub Region', 'Sub Reg. Emp. Desigantion', 'Sub Reg. Emp. Name', 'Sub Reg.Target', 'Sub Reg. Emp. Reporting to'),
('Mumbai', 'M.D.', 'MP', 'W1MHM1', 'A.S.M', 'KRS', 200, 'MP'),
('Mumbai', 'KRS', 'A.S.M', 'W1MHM2', 'Asst. A.S.M', 'PMK', 330, 'KRS'),
('Mumbai', 'KRS', 'A.S.M', 'W1MHM3', 'Asst. A.S.M', 'SSS', 490, 'KRS');

WITH Hierarchy AS (
    SELECT 
        Designation,
        "Employee Name",
        SUM("Sub Reg Target") AS Total_Target
    FROM 
        Hierarchy_Temp
    GROUP BY 
        Designation, "Employee Name"
),
RecursiveCTE AS (
    SELECT 
        Designation,
        "Employee Name",
        Total_Target,
        CAST(NULL AS VARCHAR(11)) AS Parent_Designation,
        CAST(NULL AS VARCHAR(13)) AS Parent_Name
    FROM 
        Hierarchy
    WHERE 
        Designation = 'M.D.'
    
    UNION ALL
    
    SELECT 
        h.Designation,
        h."Employee Name",
        h.Total_Target + COALESCE(r.Total_Target, 0),
        r.Designation,
        r."Employee Name"
    FROM 
        Hierarchy h
    INNER JOIN 
        RecursiveCTE r ON h."Sub Reg Emp Reporting to" = r."Employee Name"
)

SELECT 
    M.[Employee Name] AS [M.D.],
    M.Total_Target AS [M.D. Target],
    A.[Employee Name] AS [A.S.M],
    A.Total_Target AS [A.S.M Target],
    AA.[Employee Name] AS [Asst. A.S.M],
    AA.Total_Target AS [Asst. A.S.M Target],
    SE.[Employee Name] AS [Sr. Sales Engineer],
    SE.Total_Target AS [Sr. Sales Engineer Target]
FROM 
    RecursiveCTE M
LEFT JOIN 
    RecursiveCTE A ON A.Parent_Name = M."Employee Name"
LEFT JOIN 
    RecursiveCTE AA ON AA.Parent_Name = A."Employee Name"
LEFT JOIN 
    RecursiveCTE SE ON SE.Parent_Name = AA."Employee Name"
ORDER BY 
    M.[Employee Name];