Help with CTE please - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Help with CTE please

Author

Topic

samcook1
Starting Member

2 Posts

Posted - 2008-07-02 : 10:13:46

I really need this to display with persons under their direct manager, in more of a tree form, instead of listing each 'Tier' in order, any help would be greatly appreciated.

I've tried a few things, i dont know if you can solve it using more than one UNION ALL in the CTE, i keep confusing myself when i think about it.. any solution that would work would be extremely helpful. Thanks.

Here is the query (Results below):

quote:
Declare @Manager varchar(100);
set @Manager = 'uid=jack.mccrossan@hp.com,ou=People,o=hp.com';

WITH EmployeeSubordinates (EmployeeID, LastName, FirstName, DistinguishedName, Mail, Manager,Tier) AS
(
SELECT
ID,
sn [LastName],
givenName [FirstName],
DistinguishedName,
Mail,
Manager,
0 AS Tier
FROM Employees WHERE
Employees.DistinguishedName = @Manager
UNION ALL
SELECT
e.ID,
e.sn [LastName],
e.givenName [FirstName],
e.DistinguishedName,
e.Mail,
e.Manager,
Mgr.Tier + 1 As Tier
FROM Employees e
INNER JOIN EmployeeSubordinates Mgr ON
e.Manager = Mgr.DistinguishedName
)

Select EmployeeID, LastName, FirstName, DistinguishedName, Mail, Tier, Manager from EmployeeSubordinates

Here are my results:

samcook1
Starting Member

2 Posts

Posted - 2008-07-02 : 14:25:29

Just incase anyone was interested in seeing the complete solution, i had to use two loops to assign "groups" -- every manager and his subordinates were grouped together...

quote:

drop table #MyTable;
Declare @Manager varchar(200);
Declare @TempManager varchar(200);
--set @Manager = 'uid=ann.livermore@hp.com,ou=People,o=hp.com';
set @Manager = 'uid=kim.box@hp.com,ou=People,o=hp.com';
--set @Manager = 'uid=jack.mccrossan@hp.com,ou=People,o=hp.com';
--set @Manager = 'uid=mark.hurd@hp.com,ou=People,o=hp.com';

WITH EmployeeSubordinates (EmployeeID, LastName, FirstName, DistinguishedName, Mail, Manager,Tier) AS
(
SELECT
ID,
sn [LastName],
givenName [FirstName],
DistinguishedName,
Mail,
Manager,
0 AS Tier
FROM Employees WHERE
Employees.DistinguishedName = @Manager
UNION ALL
SELECT
e.ID,
e.sn [LastName],
e.givenName [FirstName],
e.DistinguishedName,
e.Mail,
e.Manager,
Mgr.Tier + 1 As Tier
FROM Employees e
INNER JOIN EmployeeSubordinates Mgr ON
e.Manager = Mgr.DistinguishedName
)

Select EmployeeID, LastName, FirstName, DistinguishedName, Mail, Tier, Manager, 0 as [Group]
INTO #MyTable
from EmployeeSubordinates
OPTION (Maxrecursion 100)

Declare @Tier int
Set @Tier = 0
Declare @MgrID varchar(200)
Declare @group int
set @group = 0

WHILE (@Tier < 15)
BEGIN

DECLARE Mgrs CURSOR
For
Select Distinct Manager From #MyTable
where Tier = @Tier
Open Mgrs

Fetch Next FROM Mgrs INTO @MgrID

While (@@Fetch_Status = 0)
Begin

Update #MyTable Set [Group] = @group
where @MgrID = DistinguishedName
Update #MyTable Set [Group] = @group
where @MgrID = Manager

set @group = @group + 1

Fetch Next FROM Mgrs INTO @MgrID
END
close Mgrs
Deallocate Mgrs

set @Tier = @Tier + 1
END

select EmployeeID, LastName, FirstName, DistinguishedName, Mail, Tier, Manager, [Group] from #mytable
ORDER BY [Group],Tier

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources