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