I have a table which has employee details
EmpId ManagerId Level Value
1 0 5 CEO
2 1 4 EMP
3 1 4 ORG
4 2 3 NULL
5 2 3 NULL
6 2 2 NULL
7 1 1 NULL
8 5 0 NULL
Now, I have to start wil Employee Id 2 and found all it's low level hirerachy (i.e. 2, 4, 5, 6, 8) and assign them value same as "2" (i.e. EMP).
Expected output :
EmpId ManagerId Level Value
1 0 5 CEO
2 1 4 EMP
3 1 4 ORG
4 2 3 EMP
5 2 3 EMP
6 2 2 EMP
7 1 1 NULL
8 5 0 EMP
What I am trying:
; WITH LevelHire AS
(
SELECT EmpId, ManagerId,Level
FROM EmployeeTable
WHERE EmpId =2
UNION ALL
SELECT Lh.EmpId, RC.ManagerId, Lh.Level
FROM LevelHire LH
INNER JOIN [EmployeeTable] RC
ON LH.EmpId= RC.EmpId
)
SELECT * FROM LevelHire
option (maxrecursion 0)
How can I achieve the same?
See Question&Answers more detail:os