Please help me with this query
-
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.
The only sensical way i know how to do this is through recursion.. but i cant make recursion in SQL transverse single branches of the tree then come back up and go down another.. instead if gets each 'level' of the tree.
Here is the query (Results below):
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:
As you can see, there are three different managers (see manager column), half of the tier 2s should be under one of the lower managers, and half under the other, and both of those managers should be under jack.
-
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...
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
--DECLARE Tier CURSOR
--Open Tier
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
--close Tier
--deallocate Tier
select EmployeeID, LastName, FirstName, DistinguishedName, Mail, Tier, Manager, [Group] from #mytable
ORDER BY [Group],Tier
-
Try something like this:
WITH EmployeeSubordinates (EmployeeID, LastName, FirstName, DistinguishedName, Mail,
Manager,Tier,Seq) AS
(
SELECT
ID,
sn [LastName],
givenName [FirstName],
DistinguishedName,
Mail,
Manager,
0 AS Tier,
right('0000000000' + cast(ID as varchar(max)), 10) + '.'
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,
Mgr.Seq + right('0000000000' + cast(e.ID as varchar(max)), 10) + '.'
FROM Employees e
INNER JOIN EmployeeSubordinates Mgr ON
e.Manager = Mgr.DistinguishedName
)
select *
from EmployeesSubordinates
order by Seq, Tier
You can leave the Seq column out of the final select (I would), but it should give you the sequence you want. You can also leave out the Tier column in this solution, if you don't want it in the final select.
I'm assuming the ID column is an integer.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply