SQL Server Hierarchical Sum of column

Question

I have my database design as per the diagram.

• Category table is self referencing parent child relationship
• Budget will have all the categories and amount define for each category
• Expense table will have entries for categories for which the amount has been spend (consider Total column from this table).

I want to write select statement that will retrieve dataset with columns given below :

ID   
CategoryID   
CategoryName   
TotalAmount (Sum of Amount Column of all children hierarchy From BudgetTable  )   
SumOfExpense (Sum of Total Column of Expense  all children hierarchy from expense table)

I tried to use a CTE but was unable to produce anything useful. Thanks for your help in advance. :)

Update

I just to combine and simplify data I have created one view with the query below.

SELECT        
    dbo.Budget.Id, dbo.Budget.ProjectId, dbo.Budget.CategoryId, 
    dbo.Budget.Amount, 
    dbo.Category.ParentID, dbo.Category.Name, 
    ISNULL(dbo.Expense.Total, 0) AS CostToDate
FROM
    dbo.Budget 
INNER JOIN
    dbo.Category ON dbo.Budget.CategoryId = dbo.Category.Id 
LEFT OUTER JOIN
    dbo.Expense ON dbo.Category.Id = dbo.Expense.CategoryId

Basically that should produce results like this.

Answer 1

This is an interesting problem. And I'm going to solve it with a hierarchyid. First, the setup:

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 NOT null
);

INSERT INTO [dbo].[Hierarchy]
        ( [ID], [ParentID], [Amount] )
VALUES  
    (1, NULL, 100 ),
    (2, 1, 50),
    (3, 1, 50),
    (4, 2, 58),
    (5, 2, 7),
    (6, 3, 10),
    (7, 3, 20)
SELECT * FROM dbo.[Hierarchy] AS [h];

Next, to update the hid column with a proper value for the hiearchyid. I'll use a bog standard recursive cte for that

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];

Now that the heavy lifting is done, the results you want are almost trivially obtained:

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];

Answer 2

After much research and using test data, I was able to get the running totals starting from bottom of hierarchy.

The solution is made up of two steps.

1. Create a scalar-valued function that will decide whether a categoryId is a direct or indirect child of another categoryId. This is given in first code-snippet. Note that a recursive query is used for this since that is the best approach when dealing with hierarchy in SQL Server.

2. Write the running total query that will give totals according to your requirements for all categories. You can filter by category if you wanted to on this query. The second code snippet provides this query.

Scalar-valued function that tells if a child category is a direct or indirect child of another category

CREATE FUNCTION dbo.IsADirectOrIndirectChild(
               @childId int, @parentId int)
RETURNS int
AS
BEGIN

    DECLARE @isAChild int;
    WITH h(ParentId, ChildId)
    -- CTE name and columns
         AS (
         SELECT TOP 1 @parentId, @parentId
         FROM dbo.Category AS b
         UNION ALL
         SELECT b.ParentId, b.Id AS ChildId
         FROM h AS cte
              INNER JOIN
              Category AS b
              ON b.ParentId = cte.ChildId AND
                 cte.ChildId IS NOT NULL)
         SELECT @isAChild = ISNULL(ChildId, 0)
         FROM h
         WHERE ChildId = @childId AND
               ParentId <> ChildId
         OPTION(MAXRECURSION 32000);
    IF @isAChild > 0
    BEGIN
        SET @isAChild = 1;
    END;
    ELSE
    BEGIN
        SET @isAChild = 0;
    END;
    RETURN @isAChild;
END;
GO

Query for running total starting from bottom of hierarchy

SELECT c.Id AS CategoryId, c.Name AS CategoryName,
(
    SELECT SUM(ISNULL(b.amount, 0))
    FROM dbo.Budget AS b
    WHERE dbo.IsADirectOrIndirectChild( b.CategoryId, c.Id ) = 1 OR
          b.CategoryId = c.Id
) AS totalAmount,
(
    SELECT SUM(ISNULL(e.total, 0))
    FROM dbo.Expense AS e
    WHERE dbo.IsADirectOrIndirectChild( e.CategoryId, c.Id ) = 1 OR
          e.CategoryId = c.Id
) AS totalCost
FROM dbo.Category AS c;