Get multiple sums in tree structure

Question

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 11-Sep-16 3:11am

Member 10967983

Updated 12-Sep-16 5:08am

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Answer 1 · 2016-09-12T05:08:00

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