Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [ The Manager employee hierarchy Puzzle ]  – In this puzzle we have to find employees their managers with their hierarchy. Please check out the sample input and expected output for details.

Sample Input

EmpID EmpName ReportsTo
1 Jacob NULL
2 Rui NULL
3 Jacobson NULL
4 Jess 1
5 Steve 1
6 Bob 1
7 Smith 2
8 Bobbey 2
9 Steffi 3
10 Bracha 3
11 John 5
12 Michael 6
13 Paul 6
14 Lana 7
15 Johnson 7
16 Mic 8
17 Stev 8
18 Paulson 9
19 Jessica 10

Expected Output

Hierarchy Hierarchy EmpName EmpId Level FullyQualifiedName
Jacob 1 Jacob 1 0 .Jacob.
    Bob 6 Bob 6 1 .Jacob..Bob.
        Michael 12 Michael 12 2 .Jacob..Bob..Michael.
        Paul 13 Paul 13 2 .Jacob..Bob..Paul.
    Jess 4 Jess 4 1 .Jacob..Jess.
    Steve 5 Steve 5 1 .Jacob..Steve.
        John 11 John 11 2 .Jacob..Steve..John.
Jacobson 3 Jacobson 3 0 .Jacobson.
    Bracha 10 Bracha 10 1 .Jacobson..Bracha.
        Jessica 19 Jessica 19 2 .Jacobson..Bracha..Jessica.
    Steffi 9 Steffi 9 1 .Jacobson..Steffi.
        Paulson 18 Paulson 18 2 .Jacobson..Steffi..Paulson.
Rui 2 Rui 2 0 .Rui.
    Bobbey 8 Bobbey 8 1 .Rui..Bobbey.
        Mic 16 Mic 16 2 .Rui..Bobbey..Mic.
        Stev 17 Stev 17 2 .Rui..Bobbey..Stev.
    Smith 7 Smith 7 1 .Rui..Smith.
        Johnson 15 Johnson 15 2 .Rui..Smith..Johnson.
        Lana 14 Lana 14 2 .Rui..Smith..Lana.

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

Script

Use the below script to generate the source table and fill them up with the sample data.

--Create Table

CREATE TABLE Employees  (EmpID INT, EmpName VARCHAR(20), ReportsTo INT)

--Insert Data
INSERT INTO Employees(EmpID, EmpName, ReportsTo)
SELECT 1, 'Jacob', NULL UNION ALL
SELECT 2, 'Rui', NULL UNION ALL
SELECT 3, 'Jacobson', NULL UNION ALL
SELECT 4, 'Jess', 1 UNION ALL
SELECT 5, 'Steve', 1 UNION ALL
SELECT 6, 'Bob', 1 UNION ALL
SELECT 7, 'Smith', 2 UNION ALL
SELECT 8, 'Bobbey', 2 UNION ALL
SELECT 9, 'Steffi', 3 UNION ALL
SELECT 10, 'Bracha', 3 UNION ALL
SELECT 11, 'John', 5 UNION ALL
SELECT 12, 'Michael', 6 UNION ALL
SELECT 13, 'Paul', 6 UNION ALL
SELECT 14, 'Lana', 7 UNION ALL
SELECT 15, 'Johnson', 7 UNION ALL
SELECT 16, 'Mic', 8 UNION ALL
SELECT 17, 'Stev', 8 UNION ALL
SELECT 18, 'Paulson', 9 UNION ALL
SELECT 19, 'Jessica', 10

--Verify Data
SELECT * FROM Employees E

UPDATE – 24-Apr-2015 – Solution 1 


--

;WITH CTE(EmpName , EmpId, Level,FullyQualifiedName) AS (
     Select E.EmpName, E.EmpID, 0 Level
     , Cast('.'+E.EmpName+'.' as Varchar(MAX)) FullyQualifiedName         
     From Employees E Where E.ReportsTo IS NULL
     UNION ALL
     Select E.EmpName, E.EmpID, c.Level + 1 , c.FullyQualifiedName+'.'+E.EmpName+'.' FullyQualifiedName  
     From Employees E INNER JOIN CTE c on c.EmpID = e.ReportsTo 
)
SELECT SPACE(LEVEL*4) + H.EmpName Hierarchy,SPACE(LEVEL*4) + CAST(H.EmpID AS VARCHAR(MAX)) Hierarchy , * FROM CTE H 
ORDER BY H.FullyQualifiedName
`
--

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

Keep Learning

http://MSBISkills.com