How to find level of employee position using RECURSIVE COMMON TABLE EXPRESSION
Posted
by user309381
on Stack Overflow
See other posts from Stack Overflow
or by user309381
Published on 2010-06-08T20:10:36Z
Indexed on
2010/06/08
20:12 UTC
Read the original article
Hit count: 404
;with Ranked(Empid,Mngrid,Empnm,RN,level) As ( select Empid,Mngrid ,Empnm ,row_number() over (order by Empid)AS RN , 0 as level from dbo.EmpMngr ), AnchorRanked(Empid,Mngrid,Empnm,RN,level) AS(select Empid,Mngrid,Empnm,RN ,level from Ranked ), RecurRanked(Empid,Mngrid,Empnm,RN,level) AS(select Empid,Mngrid,Empnm,RN,level from AnchorRanked
Union All
select Ranked.Empid,Ranked.Mngrid,Ranked.Empnm,Ranked.RN,Ranked.level + 1 from Ranked inner join RecurRanked on Ranked.Empid = RecurRanked.Empid AND
Ranked.RN = RecurRanked.RN+1)
select Empid,Empnm,level from RecurRanked
© Stack Overflow or respective owner
