Click here to Skip to main content
14,924,665 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more: , +
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.

SQL
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: Joined Tables

What I have tried:

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, but it is not correct. Returned result

The return result should be like this:
C#
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


How can i write query to get this data?
Posted
Updated 12-Sep-16 5:08am
Comments
RossMW 11-Sep-16 20:03pm
   
You may need to rethink what you are trying to achieve. In your query you are try to sum up values from a recursive loop, but that loop may have one or multiple recursive levels. I cant see how this is achieved in a select statement. To achieve this purely in SQL you will probably need as table function to do this but it could be complex and slow. Below is an example of a recursive function to retrieve the top most employeeid of a given employee as a guide or starting point.

<pre lang="SQL">
Create FUNCTION dbo.FindParent (@ID int)
returns Int

AS
BEGIN

Declare @parent int
Declare @tmp int
DECLARE @counter int
set @tmp = @ID
set @parent = @ID
SET @counter = 1
WHILE @counter < 10 and @tmp is not null
BEGIN
Select @tmp = Parentid, @parent = id from employee where id = @tmp
SET @counter = @counter + 1
end
return @parent;
end
GO
</pre>

1 solution

There might be a cleaner way to do this, but assuming you're using MS SQL Server 2008 or later, the hierarchyid data type[^] provides a solution:
SQL
WITH cteRawEmployees As
(
    SELECT
        E.ID,
        E.ParentID,
        E.Name,
        IsNull((
            SELECT Sum(S.Quantity) 
            FROM @Sales As S 
            WHERE S.EmployeeID = E.ID
        ), 0) As Sales
    FROM
        @Employees As E
),
cteEmployeeTree As
(
    SELECT
        R.ID,
        R.Name,
        R.Sales,
        CAST('/' + CAST(R.ID As varchar(10)) + '/' As varchar(max)) As NodePath
    FROM
        cteRawEmployees As R
    WHERE
        R.ParentID Is Null

    UNION ALL

    SELECT
        R.ID,
        R.Name,
        R.Sales,
        CAST(E.NodePath + CAST(R.ID As varchar(10)) + '/' As varchar(max))
    FROM
        cteRawEmployees As R
        INNER JOIN cteEmployeeTree As E
        ON E.ID = R.ParentID
),
cteEmployees As
(
    SELECT
        ID,
        Name,
        Sales,
        CAST(NodePath As hierarchyid) As NodePath
    FROM
        cteEmployeeTree
)
SELECT
    E.ID,
    E.Name,
    E.Sales As ParentSumSales,
    IsNull((
        SELECT Sum(Sales) 
        FROM cteEmployees As E2 
        WHERE E2.NodePath.IsDescendantOf(E.NodePath) = 1
        And E2.ID != E.ID -- NB: IsDescendantOf considers a node to be its own descendant.
    ), 0) As ChildSumSales
FROM
    cteEmployees As E
ORDER BY
    E.NodePath
;

Output:
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
   
Comments
Maciej Los 12-Sep-16 15:04pm
   
Looks perfect!
5!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900