Updating records with their subordinates via CTE or subquery

Question

Let's say I have a table with the following columns:

Employees Table

employeeID int
employeeName varchar(50)
managerID int
totalOrganization int

managerID is referential to employeeID. totalOrganization is currently 0 for all records.

I'd like to update totalOrganization on each row to the total number of employees under them.

So with the following records:

employeeID     employeeName     managerID     totalOrganization
1              John Cruz        NULL          0
2              Mark Russell     1             0
3              Alice Johnson    1             0
4              Juan Valdez      3             0

The query should update the totalOrganizations to:

employeeID     employeeName     managerID     totalOrganization
1              John Cruz        NULL          3
2              Mark Russell     1             0
3              Alice Johnson    1             1
4              Juan Valdez      3             0

I know I can get somewhat of an org. chart using the following CTE:

WITH OrgChart (employeeID, employeeName,managerID,level)
AS (
    SELECT employeeID,employeeName,0 as managerID,0 AS Level
    FROM Employees
    WHERE managerID IS NULL
    UNION ALL
    SELECT Employees.employeeID,Employees.employeeName,Employees.managerID,Level + 1
    FROM Employees INNER JOIN
    OrgChart ON Employees.managerID = OrgChart.employeeID
   )
SELECT employeeID,employeeName,managerID, level
FROM OrgChart;

Is there any way to update the Employees table using a stored procedure rather than building some routine outside of SQL to parse through the data?

Answer 1

You can add another CTE to determine the count of employees and then use that in an Update statement:

WITH OrgChart (employeeID, employeeName,managerID,level)
AS (
    SELECT employeeID,employeeName,0 as managerID,0 AS Level
    FROM Employees
    WHERE managerID IS NULL
    UNION ALL
    SELECT Employees.employeeID,Employees.employeeName,Employees.managerID,Level + 1
    FROM Employees 
        INNER JOIN OrgChart 
            ON Employees.managerID = OrgChart.employeeID
   )
   , SubordinateCount As
   (
   Select ManagerId, Count(*) As Total
   From OrgChart
   Group By ManagerId
   )
Update Employees
Set TotalOrganization = SubordinateCount.Total
FROM SubordinateCount
    Join Employees As E
        On E.employeeId = SubordinateCount.ManagerId

ADDITION

The change in spec is that you want a count of all subordinate employees. The trick to that is to create a path of the employee to each of their managers. So, first here is my test data:

Insert Employees(EmployeeId, Name, ManagerId) Values(1, 'Alice', Null)
Insert Employees(EmployeeId, Name, ManagerId) Values(2, 'Bob', 1)
Insert Employees(EmployeeId, Name, ManagerId) Values(3, 'Charlie', 1)
Insert Employees(EmployeeId, Name, ManagerId) Values(4, 'Dan', 3)
Insert Employees(EmployeeId, Name, ManagerId) Values(5, 'Ellen', 3)
Insert Employees(EmployeeId, Name, ManagerId) Values(6, 'Fred', 5)
Insert Employees(EmployeeId, Name, ManagerId) Values(7, 'Gale', 6)
Insert Employees(EmployeeId, Name, ManagerId) Values(8, 'Harry', 6)

So, first we write a query that gives us a path to their manager:

With 
    OrgChart As
    (
    Select E.EmployeeId, E.Name, Null As ManagerId, 0 AS Level
        , Cast( '/' + Cast(E.EmployeeId As varchar(10)) + '/' As varchar(100) ) As Path
    From dbo.Employees As E
    Where E.ManagerId Is Null
    Union All
    Select E.EmployeeID, E.Name, E.ManagerID, Level + 1
        , Cast( OrgChart.Path + Cast(E.EmployeeId As varchar(10)) + '/' As varchar(100))
    From dbo.Employees As E
        Join OrgChart 
            On OrgChart.EmployeeId = E.ManagerID
   )
Select *
From OrgChart 

That produces:

    EmployeeId  Name    ManagerId   Level   Path
    1           Alice       NULL        0       /1/
    2           Bob         1           1       /1/2/
    3           Charlie     1           1       /1/3/
    4           Dan         3           2       /1/3/4/
    5           Ellen       3           2       /1/3/5/
    6           Fred        5           3       /1/3/5/6/
    7           Gale        6           4       /1/3/5/6/7/
    8           Harry       6           4       /1/3/5/6/8/

Now we simply need to count instances where the given employee exists in someone's path:

With 
    OrgChart As
    (
    Select E.EmployeeId, E.Name, Null As ManagerId, 0 AS Level
        , Cast( '/' + Cast(E.EmployeeId As varchar(10)) + '/' As varchar(100) ) As Path
    From dbo.Employees As E
    Where E.ManagerId Is Null
    Union All
    Select E.EmployeeID, E.Name, E.ManagerID, Level + 1
        , Cast( OrgChart.Path + Cast(E.EmployeeId As varchar(10)) + '/' As varchar(100))
    From dbo.Employees As E
        Join OrgChart 
            On OrgChart.EmployeeId = E.ManagerID
   )
    , OrgCounts As
    (
    Select O.EmployeeId, O.Name, O.ManagerId, O.Level, O.Path
        , (Select Count(*)
            From OrgChart As O1
            Where O1.Path Like '%/' + Cast(E.EmployeeId As varchar(10)) + '/%') - 1 As SubordinateTotal
    From Employees As E
        Join OrgChart As O
            On O.EmployeeId = E.EmployeeId
    )
Select O.EmployeeId, O.Name, O.ManagerId, O.Level, O.Path, O.SubordinateTotal
From OrgCounts

I subtract one from the total to exclude the current employee. Now that we've found a query to provide the proper results, we can easily use that to do an update:

With 
    OrgChart As
    (
    Select E.EmployeeId, E.Name, Null As ManagerId, 0 AS Level
        , Cast( '/' + Cast(E.EmployeeId As varchar(10)) + '/' As varchar(100) ) As Path
    From dbo.Employees As E
    Where E.ManagerId Is Null
    Union All
    Select E.EmployeeID, E.Name, E.ManagerID, Level + 1
        , Cast( OrgChart.Path + Cast(E.EmployeeId As varchar(10)) + '/' As varchar(100))
    From dbo.Employees As E
        Join OrgChart 
            On OrgChart.EmployeeId = E.ManagerID
   )
    , OrgCounts As
    (
    Select O.EmployeeId, O.Name, O.ManagerId, O.Level, O.Path
        , (Select Count(*)
            From OrgChart As O1
            Where O1.Path Like '%/' + Cast(E.EmployeeId As varchar(10)) + '/%') - 1 As SubordinateTotal
    From Employees As E
        Join OrgChart As O
            On O.EmployeeId = E.EmployeeId
    )
Update Employees
Set TotalOrganization = O.SubordinateTotal
From OrgCounts As O
    Join dbo.Employees As E
        On E.EmployeeId = O.EmployeeId

Answer 2

This can (of course) be done within a stored procedure. However, it looks very much like it cannot be done with a single (CTE) statement, as you cannot sum a given employee's subordinates + all of their subordinates (i.e. tally all descendants underneath a given item in the hierarchy), as per this error message:

GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression 'Subordinates'.

So that routine you'd write outside of SQL (start at the lowest "level" of the hierarchy, count all those employees subordinates, repeat as you iterate up the hierarchy) would have to be written within SQL.

Answer 3

After a few hours of experimentation I came up with the following. It gives the desired results. Anyone see a way to improve it?

CREATE TABLE #totalOrganization (employeeID int,managerID int,level int);
CREATE TABLE #countedOrganization (employeeID int,managerID int,orgCount int,level int);


WITH OrgChart (employeeID,managerID,level) 
AS ( 
    SELECT employeeID,0 as managerID,0 AS Level 
    FROM Emp
    WHERE managerID IS NULL 
    UNION ALL 
    SELECT Emp.employeeID,Emp.managerID,Level + 1 
    FROM Emp 
        INNER JOIN OrgChart  
            ON Emp.managerID = OrgChart.employeeID 
   )
INSERT INTO
    #totalOrganization
    SELECT 
        employeeID,managerID,level
    FROM
        OrgChart;

DECLARE @maxLevel int
SELECT 
    @maxLevel = MAX(level)
FROM
    #totalOrganization;

WHILE (@maxLevel > -1)
    BEGIN
        INSERT INTO
            #countedOrganization
            SELECT
                upline.employeeID,upline.managerID,SUM(CONVERT(INT,CASE WHEN downline.orgCount IS NULL THEN 0 ELSE downline.orgCount END)) + CONVERT(INT,CASE WHEN COUNT(downline.employeeID) IS NULL THEN 0 ELSE COUNT(downline.employeeID) END),upline.level
            FROM
                #totalOrganization AS upline LEFT OUTER JOIN
                #countedOrganization AS downline ON downline.managerID=upline.employeeID
            WHERE
                upline.level = @maxLevel
            GROUP BY
                upline.employeeID,upline.managerID,upline.level

        SET @maxLevel = @maxLevel - 1
    END

UPDATE
    Emp
SET
    totalOrg= CONVERT(INT,CASE WHEN orgCount IS NULL THEN 0 ELSE orgCount END)
FROM
    #countedOrganization INNER JOIN
    Emp ON #countedOrganization.employeeID=Emp.employeeID