Listing 4: Tree Sum That Uses Loops

CREATE TABLE #Tree
(
  lvl    INT NOT NULL,
  cid    INT NOT NULL,
  pid    INT NULL,
  salary INT NOT NULL,
);

CREATE UNIQUE CLUSTERED INDEX idx_lvl_cid on #Tree(lvl, cid);

-- Traverse the tree from root downwards, 
-- assigning lvl value to each node
DECLARE @lvl AS INT;
SET @lvl = 0;

INSERT INTO #Tree(lvl, cid, pid, salary)
  SELECT @lvl, empid, mgrid, salary
  FROM dbo.Employees
  WHERE mgrid IS NULL; -- For subtree sum use: WHERE empid = @root 

WHILE @@rowcount > 0
BEGIN
  SET @lvl = @lvl + 1;

  INSERT INTO #Tree(lvl, cid, pid, salary)
    SELECT @lvl, C.empid, C.mgrid, C.salary
    FROM #Tree AS P
      JOIN dbo.Employees AS C
        ON P.lvl = @lvl - 1
        AND C.mgrid = P.cid;
END

SET @lvl = @lvl - 1;

-- Now move upwards, aggregating the values
WHILE @lvl > 0
BEGIN
  WITH UPD_CTE AS
  (
    SELECT Target.salary AS tgt_salary, Source.salary AS src_salary
    FROM #Tree AS Target
      JOIN (SELECT pid, SUM(salary) AS salary
            FROM #Tree
            WHERE lvl = @lvl
            GROUP BY pid) AS Source
        on Target.lvl = @lvl - 1
        and Target.cid = Source.pid
  )
  UPDATE UPD_CTE SET tgt_salary = tgt_salary + src_salary;

  SET @lvl = @lvl - 1;
END

SELECT cid AS empid, salary AS sum_salary
FROM #Tree;

DROP TABLE #Tree;
GO