June 24, 2010 at 2:01 pm
Greetings!
I have a staff table with EmpId and the corresponding SupervisorId. The top level employee is the one with superid is NULL.
I am trying for a query to see all the employees (both direct reports and indirect reports)under top levels.
Create table script with sample data.
create table #Staff (EmpId int, SuperId int)
insert into #Staff
select 2000,4000
unionselect 4000,6000
unionselect 6000,8000
unionselect 8000,10000
unionselect 10000,NULL
unionselect 900,1900
unionselect 1900,NULL
unionselect 300,1300
unionselect 1300,2300
unionselect 2300,NULL
select * from #Staff
Expected Result Set
select 10000 as SuperID,8000 as EmpID into #ExpectedResultSet
union select 10000 ,6000
union select 10000,4000
union select 10000,2000
union select 1900,900
union select 2300,1300
union select 2300,300
select * from #ExpectedResultSet
I was able to come up with this query but not able to get how i want to see the result set.
with CTE as
(select EmpID,SuperID,0 as Lvl from #Staff
where SuperID is null
union all
select S.EmpID,S.SuperID,Lvl+1 as Lvl from #Staff S inner join CTE on CTE.EmpID = S.SuperID)
select * from CTE
Any ideas please?
June 24, 2010 at 2:13 pm
It is not a Query but here is a link to an article that describes this very thing. I explains how to use a CTE to do this as well as having a walkthrough example.
http://msdn.microsoft.com/en-us/library/ms186243.aspx
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 24, 2010 at 2:28 pm
Thanks for the quick response Dan!
Please see attachment. that's what i could get but i am trying to show the toppest levels and all the employees underneath them like below (possibly)
SuperID EmpID
100008000
100006000
100004000
100002000
1900900
23001300
2300300
June 24, 2010 at 2:35 pm
Please provide table definition (DDL), sample data and expected result based on the sample in a ready to use format as described in the first link in my signature.
Also, please include your current query and where you get stuck.
June 24, 2010 at 2:55 pm
lmu92 (6/24/2010)
Please provide table definition (DDL), sample data and expected result based on the sample in a ready to use format as described in the first link in my signature.Also, please include your current query and where you get stuck.
Lutz, i edited my initial post based on your comments. Thanks.
June 24, 2010 at 7:16 pm
I think this is what you're looking for. If so, you were pretty close.
;WITH CTE AS
(
SELECT EmpID,SuperID,
-- provide a unique ParentNumber (PN) for each root supervisor
PN = ROW_NUMBER() OVER (ORDER BY EmpID)
FROM #Staff
WHERE SuperID IS NULL
UNION ALL
SELECT S.EmpID, CTE.EmpID, CTE.PN -- pull in the supervisor
FROM #Staff S
JOIN CTE
ON CTE.EmpID = S.SuperID
)
SELECT SuperID = t1.EmpId, t2.EmpId
FROM CTE t1 -- t1 is the main supervisor
JOIN CTE t2
ON t1.PN = t2.PN
WHERE t1.SuperId IS NULL
AND t2.SuperId IS NOT NULL
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 25, 2010 at 7:55 am
Thanks Wayne,this helps...
June 25, 2010 at 8:41 am
UnionAll (6/25/2010)
Thanks Wayne,this helps...
NP. Does it work correctly for you?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply