|
Execute the following
Microsoft SQL Server T-SQL recursive scripts in Management Studio Query Editor to get the "immediate supervisor"
organizational chart of AdventureWorks Cycles and the number of subordinates at each level.
-- SQL recursive Common Table Expression - CTE - tree processing, tree parsing
-- MSSQL organizational chart - orgchart - direct report - chain of command
USE AdventureWorks;
GO
WITH cteSupervisor(ManagerID,EmployeeID,EmployeeLevel)
AS (SELECT ManagerID,
EmployeeID,
EmployeeLevel = 0
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID,
e.EmployeeID,
EmployeeLevel + 1
FROM HumanResources.Employee e
INNER JOIN cteSupervisor d
ON e.ManagerID = d.EmployeeID)
SELECT Manager = CO.LastName + ', ' + CO.FirstName,
Employee = C.LastName + ', ' + C.FirstName,
EmployeeLevel
FROM cteSupervisor DR
INNER JOIN HumanResources.Employee E
ON DR.EmployeeID = E.EmployeeID
INNER JOIN Person.Contact C
ON E.ContactID = C.ContactID
LEFT JOIN HumanResources.Employee EM
ON DR.ManagerID = EM.EmployeeID
LEFT JOIN Person.Contact CO
ON EM.ContactID = CO.ContactID
GO
-- Results
| Manager |
Employee |
EmployeeLevel |
| NULL |
Sánchez, Ken |
0 |
| Sánchez, Ken |
Bradley, David |
1 |
| Sánchez, Ken |
Duffy, Terri |
1 |
| Sánchez, Ken |
Trenary, Jean |
1 |
| Sánchez, Ken |
Norman, Laura |
1 |
| Sánchez, Ken |
Hamilton, James |
1 |
| Sánchez, Ken |
Welcker, Brian |
1 |
| Welcker, Brian |
Jiang, Stephen |
2 |
| Welcker, Brian |
Alberts, Amy |
2 |
| Welcker, Brian |
Abbas, Syed |
2 |
| Abbas, Syed |
Tsoflias, Lynn |
3 |
| Alberts, Amy |
Pak, Jae |
3 |
| Alberts, Amy |
Varkey Chudukatil, Ranjit |
3 |
| Alberts, Amy |
Valdez, Rachel |
3 |
....
------------
-- T-SQL count descendants at each level - count subordinates in organization
-- Parent-child hierarchy - count children by level
DECLARE @EmployeeID INT, @Supervisor NVARCHAR(50)
DECLARE curEmployee CURSOR FOR
SELECT EmployeeID, FullName = FirstName + ' ' + LastName
FROM AdventureWorks.HumanResources.Employee e
INNER JOIN AdventureWorks.Person.Contact pc
ON pc.ContactID = e.ContactID
DECLARE @Descendant TABLE(
EmployeeID INT,
FullName NVARCHAR(50),
[Level] INT NULL,
[DescendantCount] INT NULL
)
OPEN curEmployee
FETCH NEXT FROM curEmployee
INTO @EmployeeID, @Supervisor
WHILE (@@FETCH_STATUS = 0 )
BEGIN
WITH cteSubTree
AS (SELECT EmployeeID, 0 AS [Level]
FROM AdventureWorks.HumanResources.Employee e
WHERE EmployeeID = @EmployeeID -- root
UNION ALL
SELECT e.EmployeeID, [Level] + 1 -- recursive term
FROM cteSubTree c
INNER JOIN AdventureWorks.HumanResources.Employee e
ON c.EmployeeID = e.ManagerID)
INSERT INTO @Descendant
SELECT @EmployeeID,
@Supervisor,
[Level],
COUNT(* )
FROM cteSubTree
GROUP BY [Level]
FETCH NEXT FROM curEmployee
INTO @EmployeeID, @Supervisor
END
SELECT FullName,
[Level],
DescendantCount,
t.EmployeeID
FROM @Descendant t
INNER JOIN (SELECT EmployeeID
FROM @Descendant
GROUP BY EmployeeID
HAVING count(* ) > 1) g
ON t.EmployeeID = g.EmployeeID
ORDER BY t.EmployeeID, [Level]
CLOSE curEmployee
DEALLOCATE curEmployee
GO
/*
FullName Level DescendantCount EmployeeID
Roberto Tamburello 0 1 3
Roberto Tamburello 1 7 3
Roberto Tamburello 2 5 3
David Bradley 0 1 6
David Bradley 1 8 6
JoLynn Dobney 0 1 7
JoLynn Dobney 1 6 7
....
*/
------------
|
