CTE to build a list of departments and managers (hierarchical)
- by Milky Joe
I need to generate a list of users that are managers, or managers of managers, for company departments.
I have two tables; one details the departments and one contains the manager hierarchy (simplified):
CREATE TABLE [dbo].[Manager](
[ManagerId] [int],
[ParentManagerId] [int])
CREATE TABLE [dbo].[Department](
[DepartmentId] [int],
[ManagerId] [int])
Basically, I'm trying to build a CTE that will give me a list of DepartmentIds, together with all ManagerIds that are in the manager hierarchy for that department.
So... Say Manager 1 is the Manager for Department 1, and Manager 2 is Manager 1's Manager, and Manager 3 is Manager 2's Manager, I'd like to see:
DepartmentId, ManagerId
1, 1
1, 2
1, 3
Basically, managers are able to deal with all of their sub-manager's departments.
Building the CTE to return the Manager hierarchy was fairly simple, but I'm struggling to inject the Departments in there:
WITH DepartmentManagers
AS
(
SELECT ManagerId,
ParentManagerId,
0 AS Depth
From Manager
UNION ALL
SELECT Manager.ManagerId,
Manager.ParentManagerId,
DepartmentManagers.Depth + 1 AS Depth
FROM Manager
INNER JOIN DepartmentManagers
ON DepartmentManagers.ManagerId = Manager.ParentManagerId
)
Can anyone help?
