Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

This problem is all about identifying all the employees directly or indirectly to whom the given Employee reports to. The challenge is to write a query that can take a Employee Id as a parameter and list all the employees to whom the Employee is reporting, directly or indirectly.

TSQL Beginners Challenge 17- Working with Employee hierarchical structures

Solution to TSQL Beginners Challenge 17

Jun 9 2011 4:39AM by Zibi   

Solution to TSQL Beginners Challenge 17

;with cte01 as (--split xml into table		
    SELECT EmpId=R2.value('data(./@EmpId)', 'int')
          ,EmpName=R2.value('data(./@EmpName)', 'varchar(max)')
          ,ReportsTo=R2.value('data(./@ReportsTo)', 'varchar(max)')
    FROM @Employees  cross apply EmpRecord.nodes('//Employee') as B(R2)
)
, cte02 as (
  select EmpId, EmpName, ReportsTo, Stack=Empname,Lev=1
  from cte01 where EmpId=@EmpId
 
  union all
  select C1.EmpId, C1.EmpName, C1.ReportsTo, C2.Stack+'/'+C1.EmpName, Lev=C2.Lev+1
  from cte02 C2 join cte01 C1 on C1.EmpId=C2.ReportsTo
  
  
)
, cte03 as (
select EmpId
       ,Empname 
       ,ReportsTo
       ,Stack
       ,rn=row_number() over (order by Lev desc)-1
from cte02
)
select Level=rn
       ,Path=Stack
       ,Hierarchy=replicate(' ', rn)+Empname 
from cte03 order by rn      

Tags:


Zibi
25 · 6% · 2030
0
Liked



Submit

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]