Building of an organizational chart for AdventureWorks & AdventureWorks2008

-- SQL recursive CTE to find subordinates of executive, manager & supervisor
-- Tree processing - find all descendants - find children - self-referencing table
DECLARE @EmployeeID INT
SET @EmployeeID = 109
USE AdventureWorks;
WITH cteEmployeeName
AS (SELECT FullName = FirstName + ' ' + LastName,
EmployeeID
FROM HumanResources.Employee e
INNER JOIN Person.Contact c
ON e.ContactID = c.ContactID),
cteSubordinates
AS (SELECT EmployeeID,
LEVEL = 0
FROM HumanResources.Employee
WHERE EmployeeID = @EmployeeID
UNION ALL
SELECT e.EmployeeID,
LEVEL + 1
FROM cteSubordinates cte
INNER JOIN HumanResources.Employee e
ON cte.EmployeeID = e.ManagerID)
SELECT FullName,
s.EmployeeID,
LEVEL
FROM cteSubordinates s
INNER JOIN cteEmployeeName en
ON s.EmployeeID = en.EmployeeID
/*
FullName EmployeeID Level
Ken Sánchez 109 0
David Bradley 6 1
Terri Duffy 12 1
Jean Trenary 42 1
Laura Norman 140 1
James Hamilton 148 1
Brian Welcker 273 1
Stephen Jiang 268 2
Amy Alberts 284 2

*/

------------
-- SQL Server 2008 tree processing functions - MSSQL organization chart - orgchart
-- T-SQL recursive Common Table Expression - CTE - SQL hierarchyid
USE AdventureWorks2008;
GO
WITH cteDirectReports(ManagerID,EmployeeID,ManagerLevel)
AS (SELECT OrganizationNode.GetAncestor(1),
OrganizationNode,
OrganizationLevel - 1
FROM HumanResources.Employee
WHERE OrganizationLevel = 0
UNION ALL
SELECT e.OrganizationNode.GetAncestor(1),
e.OrganizationNode,
OrganizationLevel - 1
FROM HumanResources.Employee e
INNER JOIN cteDirectReports d
ON e.OrganizationNode.GetAncestor(1) = d.EmployeeID)
SELECT Manager = replicate('_',(ManagerLevel) * 4) + CO.LastName + ', ' +
CO.FirstName,
Employee = C.LastName + ', ' + C.FirstName,
ManagerLevel,
EmployeeLevel = ManagerLevel + 1
FROM cteDirectReports DR
INNER JOIN HumanResources.Employee E
ON DR.EmployeeID = E.OrganizationNode
INNER JOIN Person.Person C
ON E.BusinessEntityID = C.BusinessEntityID
INNER JOIN HumanResources.Employee EM
ON DR.ManagerID = EM.OrganizationNode
INNER JOIN Person.Person CO
ON EM.BusinessEntityID = CO.BusinessEntityID
ORDER BY DR.EmployeeID

GO

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.