SQL Server Hierarchy Unit Column Amount

Question

I have a project of my database according to the diagram.

• Category the table is a reference to parent child relations
• Budget will have all categories and amounts for each category table
• Expensewill have entries for the categories for which the amount was spent (consider the column Totalfrom this table).

I want to write a select statement that will retrieve a dataset with the columns shown 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 using CTE but could not use anything. Thank you in advance for your help.:)

Update

I just collect and simplify the data. I created one view with the request 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, this should lead to such results.

Answer 1

. . -, :

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

, hiearchyid. cte

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

Answer 2

, .

.

• , , categoryId categoryId. . , , SQL Server.

• , . , . .

, ,

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

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;