CODEWITHSUNDEEP
sqlsql-serverrecursive-query
Mahmood Khan
Mahmood Khan

Reputation: 27

SQL Recursive Query to find subordinates under a manager with missing levels

Consider the following Associate table:

Id |  Name |  ManagerId
---+-------+------------
1  |  A    |   Null         
2  |  B    |   1            
3  |  C    |   2             
4  |  D    |   3            
5  |  E    |   3            
6  |  F    |   3

Here there is a CEO (A), a manager (B), a team lead (C) and team members(D, E, F).

Another table Registration contains the associates that have registered for insurance:

Id |  Number of Family Members
---+--------------------------
3  |  3        
4  |  2       
6  |  2

I need a recursive query to get all the registered employees under the CEO, provided that some subordinates have not registered yet.

Upvotes: 0

Views: 602

Answers (2)

Balamurugan Annamalai
Balamurugan Annamalai

Reputation: 36

Based on your input, the query is.

; WITH recursive_CTE (id,name,MANAGERID,CEONAME,CEO)
 AS(
 SELECT  ID,name,MANAGERID,name ,ID FROM ASSOSIATETABLE WHERE MANAGERID IS NULL
    UNION  ALL
 SELECT B.ID,B.name,B.MANAGERID,A.CEONAME,A.CEO FROM recursive_CTE  A
 INNER JOIN ASSOSIATETABLE  B ON A.ID=B.ManagerID 
 )

 SELECT * FROM recursive_CTE
 WHERE ID NOT IN (SELECT ID FROM Registration ) 
 AND MANAGERID IS NOT NULL

Need the CEO in the result, please remove the "AND MANAGERID IS NOT NULL"

Output

Fiddler Output

Upvotes: 1

Vikram Singh
Vikram Singh

Reputation: 124

Declare @Employee TABLE 
(
id INT,
name VARCHAR(50) NOT NULL,
ManagerId Varchar(50)  NULL
)
Declare @Registration TABLE 
(
id INT,
No_of_FamilyMember Int
)

 INSERT INTO @Employee
VALUES
(1, 'CEO (A)', NULL),
(2, 'Manager (B)', 1),
(3, 'Team lead (C)', 2),
(4, 'Team members(D)', 3),
(5, 'Team members(E)', 3),
(6, 'Team members(F)', 3)

 INSERT INTO @Registration
VALUES
(3, 3),
(4, 2),
(6,2)
--SELECT * FROM @Employee

;WITH EMP_CTE AS
(
SELECT Id, Name, ManagerId, CAST('' AS VARCHAR(50)) ManagerName, 0 AS EmployeeLevel FROM @Employee WHERE ManagerId IS NULL
UNION ALL
SELECT T.Id,T.Name, T.ManagerId,CAST(C.name AS VARCHAR(50)) ManagerName, EmployeeLevel + 1 AS EmployeeLevel FROM @Employee AS T
INNER JOIN EMP_CTE  AS C ON C.[![enter image description here][1]][1]id=T.ManagerId
)
SELECT E.Id,Name, ManagerId, ManagerName, EmployeeLevel, 
case when No_of_FamilyMember is null then 'Not Registered' else cast(No_of_FamilyMember as Varchar) end FamilyMemebers
 FROM EMP_CTE E left join @Registration R on E.id = R.id

enter image description here

Upvotes: 0

Related Questions