0

Referring to an earlier problem statement at: SQL Server Hierarchical Sum of column

Code below for the implementation:

USE tempdb;
IF OBJECT_ID('dbo.Hierarchy') IS NOT NULL
    DROP TABLE dbo.[Hierarchy];

CREATE TABLE dbo.Hierarchy 
(
    ID INT NOT NULL PRIMARY KEY,
    ParentID INT NULL,
        CONSTRAINT [FK_parent] FOREIGN KEY ([ParentID]) REFERENCES dbo.Hierarchy([ID]),
    hid HIERARCHYID,
    Amount INT
);

INSERT INTO [dbo].[Hierarchy]
        ( [ID], [ParentID], [Amount] )
VALUES  
    (1, NULL, NULL ),
    (2, 1, NULL),
    (3, 1, 50),
    (4, 2, 58),
    (5, 2, 7),
    (6, 3, 10),
    (7, 3, 20)
; WITH cte AS (
    SELECT  [h].[ID] ,
            [h].[ParentID] ,
            CAST('/' + CAST(h.[ID] AS VARCHAR(10)) + '/' AS VARCHAR(MAX)) AS [h],
            [h].[hid]
    FROM    [dbo].[Hierarchy] AS [h]
    WHERE   [h].[ParentID] IS NULL

    UNION ALL

    SELECT  [h].[ID] ,
            [h].[ParentID] ,
            CAST([c].[h] + CAST(h.[ID] AS VARCHAR(10)) + '/' AS VARCHAR(MAX)) AS [h],
            [h].[hid]
    FROM    [dbo].[Hierarchy] AS [h]
    JOIN    [cte] AS [c]
            ON [h].[ParentID] = [c].[ID]
)
UPDATE [h]
SET hid = [cte].[h]
FROM cte
JOIN dbo.[Hierarchy] AS [h]
    ON [h].[ID] = [cte].[ID];

SELECT p.id, SUM([c].[Amount])
FROM dbo.[Hierarchy] AS [p]
JOIN [dbo].[Hierarchy] AS [c]
    ON c.[hid].IsDescendantOf(p.[hid]) = 1
GROUP BY [p].[ID];

Thanks to Ben Thul for the above code! I have modified it slightly to suit my problem statement. As can be seen the nodes with ID=1 and 2 do not have any values. The same in pictorial representation is below. I am not allowed to post inline pictures yet.

link here or https://i.stack.imgur.com/RuBdu.png

The scenario I have is since node with ID=3 has a value I want it's parent to get the summed value of nodes with ID 2 and 3. What the below code does is it bubbles the grandchildren node values as well.

Current Output:

<style>
table, th, td {
  border: 1px solid black;
}
</style>
<table>
<tr>
<th>ID</th>
<th>Value</th>
</tr>
<tr>
<td>1</td>
<td>145</td>
</tr>
<tr>
<td>2</td>
<td>65</td>
</tr>
<tr>
<td>3</td>
<td>80</td>
</tr>
<tr>
<td>4</td>
<td>58</td>
</tr>
<tr>
<td>5</td>
<td>7</td>
</tr>
<tr>
<td>6</td>
<td>10</td>
</tr>
<tr>
<td>7</td>
<td>20</td>
</tr>
</table>

Expected Output:

<style>
table {
  border-collapse: collapse;  
}

td, th {
  border: 1px solid #dddddd;
  text-align: left;
  padding: 8px;
}
</style>
<table>
<tr>
<th>ID</th>
<th>Value</th>
</tr>
<tr>
<td>1</td>
<td style="background-color:#FF0000;">115</td>
</tr>
<tr>
<td>2</td>
<td>65</td>
</tr>
<tr>
<td>3</td>
<td style="background-color:#FF0000;">50</td>
</tr>
<tr>
<td>4</td>
<td>58</td>
</tr>
<tr>
<td>5</td>
<td>7</td>
</tr>
<tr>
<td>6</td>
<td>10</td>
</tr>
<tr>
<td>7</td>
<td>20</td>
</tr>
</table>

I hope this is clear to what I need. Thanks for the help!

Palthis
  • 1
  • 1
  • 3
    . . State your problem in a self-contained way, including sample data, desired results, and a description. You can refer to other pages, but your question should stand by itself. – Gordon Linoff Feb 08 '19 at 20:32
  • Put some sample data together using rextester, and link it. You can use this unrelated example to draw insipration and adapt something suitable for your question https://rextester.com/TXQB38528 When you are done 'Fork' it! And you will have a new link. You will also have to explain in the question what you expect as output. – JGFMK Feb 09 '19 at 12:38
  • updated the OP with more details – Palthis Feb 11 '19 at 04:21

0 Answers0