SQL Puzzle | The Complex Hierarchy Puzzle [All Positions below – Line Manager]

In this puzzle we have to write a query that will give us the list of all Sr Line Staff or lower with their corresponding Line Manager. For details please refer the input data and the expected output.

Sample Input

Employees Table

Positions table

PositionTypes Table

Expected Output

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE tblPositionTypes    
(
     PositionTypeID INT 
     ,PositionType VARCHAR(35)
)
GO

INSERT INTO tblPositionTypes VALUES
(1,'Line Staff'),
(2,'Team Lead'),
(3,'Sr Line Staff'),
(4,'Line Manager'),
(5,'Sr Line Manager')
GO

CREATE TABLE tblPositions        
(
     PositionID     INT 
     ,Position VARCHAR(100)
     ,PositionTypeID INT
)
GO

INSERT INTO tblPositions VALUES
(1,'Operational Line Staff',1),
(2,'Operational Team Lead',2),
(3,'Operational Sr Line Staff',3),
(4,'Operational Line Manager',4),
(5,'Research Line Staff',  1),
(6,'Research Line Manager', 4),
(7,'Sales Staff',     1),
(8,'Sales Team Lead', 2),
(9,'Sales Manager',   4),
(10, 'Branch Sr Manager',  5)
GO

CREATE TABLE tblEmployees  
(
     EmployeeID     INT
     ,Name VARCHAR(10)
     ,ParentID  INT
     ,PositionID INT 
)
GO

INSERT INTO tblEmployees VALUES       
(1   ,'Joe'     ,4   ,1         ),
(2   ,'Sue'     ,5   ,5         ),
(3   ,'John'    ,6   ,7         ),
(4   ,'Amy'     ,7   ,2         ),
(5   ,'Luis'    ,10  ,6         ),
(6   ,'Harry'   ,8   ,8   ),
(7   ,'Pete'    ,9   ,3         ),
(8   ,'Rhonda', 10,  9    ),
(9   ,'Maria'   ,10  ,4   ),
(10  ,'Jack'    ,NULL,     10   ),
(11  ,'Kate'    ,12  ,5         ),
(12  ,'Aaron'   ,10  ,6   ),
(13  ,'Julie'   ,14  ,7   ),
(14  ,'Sarah'   ,15  ,8   ),
(15  ,'Bob'     ,10  ,9         )
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1

--

;WITH CTE AS
(
     SELECT e.EmployeeID,e.Name,p.Position,e.ParentID FROM tblEmployees e
     INNER JOIN tblPositions    p ON e.PositionID = p.PositionID    
     INNER JOIN tblPositionTypes pt ON pt.PositionTypeID = p.PositionTypeID
     WHERE pt.PositionTypeID IN (1,2,3)    
)
,CTE1 AS
(
     SELECT e.EmployeeID,e.Position,e.Name,e.Name Name1,e.ParentID , 0 distance FROM CTE e
     UNION ALL
     SELECT * FROM 
     (
           SELECT e.EmployeeID,e1.Position,e1.name,e.name name1,e.ParentID, e1.distance + 1 distance
           FROM CTE1 e1 INNER JOIN tblEmployees e ON e.EmployeeID = e1.ParentID 
     )k   
)
SELECT R.EmployeeID,R.Name,R.Position ,Q.Name1 [Manager Name] FROM 
(
     SELECT * FROM CTE1 e 
     WHERE DISTANCE = 0
)r
CROSS APPLY
(
     SELECT * FROM CTE1 t
     WHERE t.Name = r.Name AND DISTANCE = ( SELECT MAX(DISTANCE) - 1 FROM CTE1 t WHERE t.Name = r.Name )
)q
ORDER BY r.EmployeeID 


--

Output

--

/*------------------------
;WITH CTE AS
(
     SELECT e.EmployeeID,e.Name,p.Position,e.ParentID FROM tblEmployees e
     INNER JOIN tblPositions    p ON e.PositionID = p.PositionID    
     INNER JOIN tblPositionTypes pt ON pt.PositionTypeID = p.PositionTypeID
     WHERE pt.PositionTypeID IN (1,2,3)    
)
,CTE1 AS
(
     SELECT e.EmployeeID,e.Position,e.Name,e.Name Name1,e.ParentID , 0 distance FROM CTE e
     UNION ALL
     SELECT * FROM 
     (
           SELECT e.EmployeeID,e1.Position,e1.name,e.name name1,e.ParentID, e1.distance + 1 distance
           FROM CTE1 e1 INNER JOIN tblEmployees e ON e.EmployeeID = e1.ParentID 
     )k   
)
SELECT R.EmployeeID,R.Name,R.Position ,Q.Name1 [Manager Name] FROM 
(
     SELECT * FROM CTE1 e 
     WHERE DISTANCE = 0
)r
CROSS APPLY
(
     SELECT * FROM CTE1 t
     WHERE t.Name = r.Name AND DISTANCE = ( SELECT MAX(DISTANCE) - 1 FROM CTE1 t WHERE t.Name = r.Name )
)q
ORDER BY r.EmployeeID 
------------------------*/

EmployeeID  Name       Position                     Manager Name
----------- ---------- ---------------------------- ------------
1           Joe        Operational Line Staff       Maria
2           Sue        Research Line Staff          Luis
3           John       Sales Staff                  Rhonda
4           Amy        Operational Team Lead        Maria
6           Harry      Sales Team Lead              Rhonda
7           Pete       Operational Sr Line Staff    Maria
11          Kate       Research Line Staff          Aaron
13          Julie      Sales Staff                  Bob
14          Sarah      Sales Team Lead              Bob

(9 row(s) affected)

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com