Need to iterate thru rows to find each manager and then their subordinates in hierarchy 3

[DougP]

We have 800 people or so in our Org.
I would like to be able to pass the VP managers ID number (Enterprise ID), have it get all four of his “Reports to” Managers. Lets call them Report1.
Then loop through the Report1 Enterprise ID to find their Report2 people, and so on.
So we end up with a hierarchy of employees.
Each Row or Employee has both their own Enterprise ID and their managers Enterprise ID.

Is there a way to do this in T-SQL. someone did it in .NET but I was told if it was done in SQL it would run way faster. There are over 200,000 people in the entire list so it can take a while.
If a manager has one or more people under them we need to get them all.
So if the VP Enterprise ID = 1234
Then his reports to would each have their managers ID = 1234
example
txtGivenName txtEnterpriseID txtManagerEnterpriseID
VP 1234 we don’t care
sue 8737 1234
kim 8723 1234
bob 8675 1234
joe 6544 1234
cary 8777 6544
dave 8737 6544
fred 7664 8675
mary 9009 8723
an so on for 800 people...
gmmastros helped me with this code in another post. which gets the VP.
how can I store each of the txtManagerEnterpriseID in ana array to get their Reports to. then store thsoes in an array to get their Reports to, etc.

 Select Employee.txtEnterpriseID ,
		Employee.txtSurName + ', ' + Employee.txtGivenName as 'Name' ,
		Employee.txtInternalEmail ,
		Employee.txtManagerEnterpriseID ,
		Manager.txtSurName + ', ' + Manager.txtGivenName as 'Manager Name',

Hope this makes sense
TIA

DougP

 

[gmmastros]

You can use a recursive CTE to do this.

Try:

; WITH Subordinates AS 
( 
  SELECT  txtEnterpriseID, 
          txtSurName + ', ' + txtGivenName as [Name],
		  txtManagerEnterpriseID 
  FROM    dbo.tblLDAP
  WHERE   txtManagerEnterpriseID IS NULL

  UNION ALL

  SELECT  txtEnterpriseID,
          txtSurName + ', ' + txtGivenName as [Name], 
          txtManagerEnterpriseID 
  FROM    dbo.tblLDAP
          INNER JOIN Subordinates
		    ON  dbo.tblLDAP.txtManagerEnterpriseID = Subordinates.txtEnterpriseID 
  WHERE    dbo.tblLDAP.txtManagerEnterpriseID IS NOT NULL 
)
SELECT *
FROM   Subordinates

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[drlex]

Recursion is probably best in hierarchical situations of self-joining.
Adapting an answer given a few weeks back using a common table expression and using your example of employee #1234:

declare @EnterpriseID as int
set @EnterpriseID = 1234

;with EmployeeHierarchy (txtEnterpriseID, txtManagerEnterpriseID , [employee name], [manager]) as
(
    select parentEmployee.txtEnterpriseID,
           parentEmployee.txtManagerEnterpriseID ,
           parentEmployee.txtGivenName + ' ' + parentEmployee.txtSurName,
		   cast(parentEmployee.txtGivenName + ' ' + parentEmployee.txtSurName as varchar(255)) 
    from   Employee parentEmployee
    where  txtManagerEnterpriseID = @EnterpriseID
UNION ALL
    select childEmployee.txtEnterpriseID,
           childEmployee.txtManagerEnterpriseID ,
           childEmployee.txtGivenName + ' ' + childEmployee.txtSurName,
		   cast(EmployeeHierarchy.[employee name] as varchar(255))
    from   Employee childEmployee
    join   EmployeeHierarchy
    on     childEmployee.txtManagerEnterpriseID  = EmployeeHierarchy.txtEnterpriseID
)

Select * from EmployeeHierarchy

The above creates a temp.table of names and ID pairs, using the selected employee ID as the seed. If it suits, you could amend this to be a stored procedure or a table-based function as required.



soi là, soi carré

 

[drlex]

Oops - I should have refreshed before posting, as I've been beaten like a rented mule!

soi là, soi carré

 

[gmmastros]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[drlex]

Excellent smiley, George - that's worth a star alone!

Noticed a slip in my code
where txtManagerEnterpriseID = @EnterpriseID
should be
where txtEnterpriseID = @EnterpriseID

soi là, soi carré

 

[gmmastros]

It's actually the "Dead Horse" smiley, but I thought it was close enough, and also kinda humorous. Glad you liked it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DougP]

drlex, I used your example works great; have a star.

< I built my own

http://www.robots2000.com

DougP