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

Oct 8 2010 2:59AM by Kabila   

Solution to TSQL Beginners Challenge 17


DECLARE @EmpID VARCHAR(20) 
SELECT @EmpID = 7 ;
with cte
as
(
select a.reportsto as empid,cast(a.empname+'/'+b.empname as varchar(200)) as cnt
 from employees a inner join employees b on a.reportsto=b.empid
  where a.empid=@EmpID
union all
select e.reportsto,cast(isnull(cnt,'')+'/'+b.empname as varchar(200))
 from employees e inner join cte a  on a.empid=e.empid
 inner join employees b on  e.reportsto=b.empid
)
select row_number() over (order by len(cnt) desc)-1 as [Level],cnt,space(row_number() over (order by len(cnt) desc)-1) +empname as Hierarchy from(
select empid,empname as cnt from employees where empid=@EmpID
union all
select empid,cnt from cte where empid is not null
) as A  inner join employees e  on a.empid=e.empid

Tags:


Kabila
2219 · 0% · 6
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]