need help with SQL statement to get counts managers of managers of managers

[DougP]

We have 500 people in the Org. the columns in the table is set up as Name,Report1,Report2,Report3,Report4.
So each person reports to someone i.e. their manager who is in Report1
So Fred Flinstone reports to Barney Rubble who reports to Wilma, who reports to Betty.
Betty is the top
Like so
Name, Report1, Report2,Report3,Report4,
Fred, Barney, Wilma,Betty, NULL
Bam Bam, Barney,Wilma,Betty, NULL
Pebbles,Wilma,Betty, NULL,NULL
I need to get counts of everyone who works under Wilma for example:
So it would Be Fred,Barney,Bam Bam,Pebbles = 4
Under Betty it would be Fred,Barney,Bam Bam,Pebbles,Wilma = 5
So some how I need to loop, but would don't want 4 recursive loops for each report.
Is there a way to set up a SQL statement to do this maybe a WITH

WITH MyReport1
AS ( SELECT Name,Manager,Report1,Report2,Report3,Report4
FROM MyTable 
Where Report1 = 'Barney Rubble') 
(SELECT Name, Report1,Report2,Report3,Report4
FROM MyReport1)

DougP

 

[chamilz]

check this out...

http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

 

[DougP]

Ok so extrapolating the above URL example, I get the following in the code block. But it fails with this Error:
"The statement terminated. The maximum recursion 100 has been exhausted before statement completion."

I don't have a HumanResources table or a "Level" column, so I'm not sure what to put in the missing parts? And if I take out the inner join it returns about twice as many results as there are rows in the table.

WITH DirectReports (manager, Name )
AS
(
-- Anchor member definition
    SELECT e.manager, e.Name 
        FROM dbo.LDAPData AS e
    UNION ALL
-- Recursive member definition
    SELECT e.manager, e.name 
        FROM dbo.LDAPData AS e
    Inner Join DirectReports ON DirectReports.Manager = e.Manager 
)
-- Statement that executes the CTE
SELECT Name,  manager
FROM DirectReports

DougP

 

[DaveInIowa]

I'm not sure a Common-Table-Expression will help in this situation because of your non-relational table structure. If someone does come up with a solution, I'd be anxious to see it. In the meantime, will this help?

allCombos As (
SELECT CAST(Report4 As NVARCHAR) Manager, CAST(Report3 As NVARCHAR) Employee FROM MyTable
UNION SELECT Report4, Report2 FROM MyTable
UNION SELECT Report4, Report1 FROM MyTable
UNION SELECT Report4, Name FROM MyTable
UNION SELECT Report3, Report2 FROM MyTable
UNION SELECT Report3, Report1 FROM MyTable
UNION SELECT Report3, Name FROM MyTable
UNION SELECT Report2, Report1 FROM MyTable
UNION SELECT Report2, Name FROM MyTable
UNION SELECT Report1, Name FROM MyTable
),

allPeople As (
SELECT Manager As Name FROM allCombos
 UNION
SELECT Employee FROM allCombos
),

managerCounts As (
SELECT Manager, COUNT(*) As PeopleManaged
  FROM allCombos
 GROUP BY Manager
)

SELECT allPeople.Name, ISNULL(managerCounts.PeopleManaged, 0) As PeopleManaged
  FROM allPeople
  LEFT
  JOIN managerCounts
    ON allPeople.Name = managerCounts.Manager
 WHERE allPeople.Name IS NOT NULL