CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012
www1986
www1986

Reputation: 101

Calculate sum of values in tree(recursive query)

I have tree structure in table employees (id,name,parentid) and this table can be nested.employees is one-to-many relation to another table Sales with columns(id, employeeid, quantity). Each employee has Sales Quantity. I want to calculate sum of quantity for each employee along side with child employees. I wrote some code to be more clearly.

DECLARE @Employees TABLE(ID INT, Name NVARCHAR(100), ParentID INT);
DECLARE @Sales TABLE(ID INT, EmployeeID INT, Quantity INT);    

INSERT INTO @Employees(ID, Name, ParentID)VALUES
(1,N'Employee1', NULL),
(2,N'Employee2', 1),
(3,N'Employee3', 2),
(4,N'Employee4', NULL),
(5,N'Employee5', 4),
(6, N'Employee6', 5)

INSERT INTO @Sales(ID, EmployeeID, Quantity)VALUES
(1,1,4),
(2,1,2),
(3,2,3),
(4,3,2),
(5,3,7),
(6,5,8),
(7,5,3),
(8,6,2)

I joined this Tables and looks likes this:

enter image description here

Here is my query 

;WITH cte 
AS
(
  SELECT e.ID, e.Name, e.ParentID FROM @Employees e
  WHERE e.ParentID IS NULL
  UNION ALL
  SELECT  e.ID, e.Name, e.ParentID  FROM @Employees e
    INNER JOIN cte c ON c.ID = e.ParentID
)
SELECT
     c.ID
    ,c.Name
    ,c.ParentID
    ,ISNULL(SUM(s.Quantity), 0) AS ParentSumSales
    ,ISNULL(LEAD(SUM(s.Quantity)) OVER(ORDER BY c.ID), 0) AS ChildSumSales
FROM cte c
    LEFT JOIN @Sales s ON s.EmployeeID = c.ID
GROUP BY c.ID, c.Name, c.ParentID

query returns this result:
enter image description here

But this is not correct, I want to get data like this:

ID     Name        ParentSumSales    ChildSumSales
---    ---------   -------------     -------------
1      Employee1   6                 12
2      Employee2   3                 9   
3      Employee3   9                 0
4      Employee4   0                 13
5      Employee5   11                2
6      Employee6   2                 0

Upvotes: 2

Views: 2733

Answers (2)

dnoeth
dnoeth

Reputation: 60462

Your attempt using LEAD will not work as it's not summing all previous levels and it IDs must be sequential.

Explode the full hierachy for each employee first such that each employe is included once per hierarchy level:

;WITH cte 
AS
(
  SELECT e.ID, e.Name, e.ID as sub_ID 
  FROM @Employees e
  -- no WHERE-condition to get all employees
  UNION ALL
  SELECT 
     c.ID, c.Name -- keep the initial employee
     ,e.ID as sub_ID
  FROM @Employees e
    INNER JOIN cte c ON c.sub_ID = e.ParentID
)

SELECT 
     c.ID
    ,c.Name
    -- parent level
    ,sum(case when c.id =  s.EmployeeID then s.Quantity else 0 end) AS ParentSumSales
    -- child level
    ,sum(case when c.id <> s.EmployeeID then s.Quantity else 0 end) AS ChildSumSales
FROM cte c
LEFT JOIN @Sales as s
ON s.EmployeeID = c.sub_ID
group by c.Name, c.id

Upvotes: 5

OlegAxenow
OlegAxenow

Reputation: 380

If your "Employees" table occasionally changes, I recommend you to implement Nested set model. Writing will be more complex, but reading will be simplier and faster.

Upvotes: 1

Related Questions