CODEWITHSUNDEEP
sqlsql-server
carlosm
carlosm

Reputation: 755

how to find all employees under each manager

I need to get a query to display the subordinates all the way down starting from one specific manager. Let's say I have this structure and I need to get all employees starting from ManagerId = 1 

EmployeeId  ManagerId
2           1
3           1 
4           3
5           3 
6           4

Here I have the visualization

I need to get as result of the query the ManagerId and direct report employees under him, along with their subordinates.

For example, using SQL Server 2014:

CREATE TABLE #Pepe (EmployeeId INT, ManagerId int)

INSERT INTO [#Pepe] ( [EmployeeId], [ManagerId] ) VALUES  (2, 1)
INSERT INTO [#Pepe] ( [EmployeeId], [ManagerId] ) VALUES  (3, 1)
INSERT INTO [#Pepe] ( [EmployeeId], [ManagerId] ) VALUES  (4, 3)
INSERT INTO [#Pepe] ( [EmployeeId], [ManagerId] ) VALUES  (5, 3)
INSERT INTO [#Pepe] ( [EmployeeId], [ManagerId] ) VALUES  (6, 4)

Now I get the CTE

;WITH relation AS 
    (
       SELECT 1 as EmployeeId,  0 AS LEVEL

       UNION ALL

       SELECT r.EmployeeId, LEVEL + 1 AS LEVEL
         FROM (SELECT EmployeeId, ManagerId FROM #Pepe) r
        INNER JOIN relation T 
           ON r.ManagerId = T.EmployeeId
        WHERE r.ManagerId <> r.EmployeeId 

    )  

SELECT DISTINCT  EmployeeId, LEVEL FROM relation

The result of my CTE is this:

EmployeeId  LEVEL
1           0
2           1 
3           1
4           2
5           2
6           3

This result start from one specific employee which is 1 (is hardcoded on the CTE), now I need only the direct report, Level = 1 and also Level 0 too which are employee id 1, 2 and 3.

This is fine, now what I need is for each employee id (level 0 and 1), I need to get this result:

EmployeeId  ManagerId
1           1
2           1
3           1
4           1
5           1
6           1
2           2
3           3
4           3
5           3
6           3

As you can see the Manager Id contains the employees with level 0 and 1, and for each one I basically call the CTE to get all employees down, like for example ManagerId = 2 doesn't have subordinates but I need to count it anyway. Is there an efficient way to do this? I was using cross apply putting the CTE in an inline function but I was having troubles with performance.

Upvotes: 4

Views: 5458

Answers (2)

Christopher Grimaldi
Christopher Grimaldi

Reputation: 11

You pretty much had it. Just needed to select the distinct managers from the #Pepe table instead of manually doing the 3 select statements

DECLARE @Pepe TABLE (EmployeeId INT, ManagerId int)

INSERT INTO @Pepe ( [EmployeeId], [ManagerId] ) VALUES  (1, 1)
INSERT INTO @Pepe ( [EmployeeId], [ManagerId] ) VALUES  (2, 1)
INSERT INTO @Pepe ( [EmployeeId], [ManagerId] ) VALUES  (3, 1)
INSERT INTO @Pepe ( [EmployeeId], [ManagerId] ) VALUES  (4, 3)
INSERT INTO @Pepe ( [EmployeeId], [ManagerId] ) VALUES  (5, 3)
INSERT INTO @Pepe ( [EmployeeId], [ManagerId] ) VALUES  (6, 4)

;WITH relation (ManagerId, EmployeeId, LEVEL)
AS 
(
    SELECT DISTINCT ManagerId, ManagerId AS EmployeeId,  0 AS LEVEL
    FROM @Pepe  

    UNION ALL

    SELECT t.[ManagerId], r.EmployeeId, LEVEL + 1 AS LEVEL
    FROM @Pepe r
      INNER JOIN relation T 
        ON r.ManagerId = T.EmployeeId
    WHERE r.ManagerId <> r.EmployeeId 
)  
SELECT DISTINCT EmployeeId, ManagerId, LEVEL FROM relation ORDER BY ManagerId

Upvotes: 0

carlosm
carlosm

Reputation: 755

I guess I found a solution, I would like to have an opinion about it.

;WITH relation AS 
    (
       SELECT 1 AS ManagerId, 1 as EmployeeId,  0 AS LEVEL UNION    
       SELECT 2 AS ManagerId, 2 as EmployeeId,  0 AS LEVEL UNION
       SELECT 3 AS ManagerId, 3 as EmployeeId,  0 AS LEVEL

       UNION ALL

       SELECT t.[ManagerId], r.EmployeeId, LEVEL + 1 AS LEVEL
         FROM (SELECT EmployeeId, ManagerId FROM #Pepe) r
        INNER JOIN relation T 
           ON r.ManagerId = T.EmployeeId
        WHERE r.ManagerId <> r.EmployeeId 

    )  

SELECT DISTINCT EmployeeId, [ManagerId] FROM relation ORDER BY 2

Upvotes: 1

Related Questions