Calculations through a hierarchy in SQL

Question

I am trying to perform some calculation by navigating through a hierarchy. In the simple example below, where organizations have a headcount and can be associated with parent organizations, the headcount is only specified for "leafs" organizations. I want to calculate the headcount all the way up the hierarchy using the simple rule: parent_headcount = sum(children_headcount). I liked the idea of using SQL Common Table Expression for this, but this does not quite work. The determination of the level works (as it follows the natural top-down order of navigation), but not the headcount determination. How would you fix this, or is there a better way to perform this calculation bottom-up?

-- Define the hierachical table Org
drop table if exists Org
create table Org (
    ID int identity (1,1) not null, Name nvarchar(50), parent int null, employees int,
    constraint [PK_Org] primary key clustered (ID),
    constraint [FK_Parent] foreign key (parent) references Org(ID)
);

-- Fill it in with a simple example
insert into Org (name, parent, employees) values ('ACME', NULL, 0);
insert into Org (name, parent, employees) values ('ACME France', (select Org.ID from Org where Name = 'ACME'), 0);
insert into Org (name, parent, employees) values ('ACME UK', (select Org.ID from Org where Name = 'ACME'), 0);
insert into Org (name, parent, employees) values ('ACME Paris', (select Org.ID from Org where Name = 'ACME France'), 200);
insert into Org (name, parent, employees) values ('ACME Lyons', (select Org.ID from Org where Name = 'ACME France'), 100);
insert into Org (name, parent, employees) values ('ACME London', (select Org.ID from Org where Name = 'ACME UK'), 150);
select * from Org;

-- Try to determine the total number of employees at any level of the hierarchy
with Orgs as (
    select
        ID, name, parent, 0 as employees, 0 as level from Org where parent is NULL
    union all
    select 
        child.ID, child.name, child.parent, Orgs.employees + child.employees, level + 1 from Org child
        join Orgs on child.parent = Orgs.ID
)
select * from Orgs;

This query returns:

The determination of the level is correct, but the calculation of the headcount is not (UK should be 150, France 300, and 450 at the top fo the hierarchy). It seems that CTE is suitable for top-down navigation, but not bottom-up?

Answer 1

Just another option using the datatype hierarchyid

Note: the @Top and Nesting is optional

Example

Declare @Top int = null

;with cteP as (
      Select ID
            ,Parent 
            ,Name 
            ,HierID = convert(hierarchyid,concat('/',ID,'/'))
            ,employees
      From   Org 
      Where  IsNull(@Top,-1) = case when @Top is null then isnull(Parent ,-1) else ID end
      Union  All
      Select ID  = r.ID
            ,Parent  = r.Parent 
            ,Name   = r.Name
            ,HierID = convert(hierarchyid,concat(p.HierID.ToString(),r.ID,'/'))
            ,r.employees
      From   Org r
      Join   cteP p on r.Parent  = p.ID)
Select Lvl   = A.HierID.GetLevel()
      ,A.ID
      ,A.Parent
      ,Name  = Replicate('|---',A.HierID.GetLevel()-1) + A.Name
      ,Employees = sum(B.Employees)
 From  cteP A
 Join  cteP B on B.HierID.ToString() like A.HierID.ToString()+'%'
 Group By A.ID,A.Parent,A.Name,A.HierID
 Order By A.HierID

Returns