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

EmployeeID Name ParentID PositionID
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

Positions table

PositionID Position PositionTypeID
1 Operational Line Staff 1
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
9 Sales Manager 4
10 Branch Sr Manager 5

PositionTypes Table

PositionTypeID PositionType
1 Line Staff
3 Sr Line Staff
4 Line Manager
5 Sr Line Manager

Expected Output

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

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”.

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