Hierarchical cumulative sum in sql query (id,parentId) structure

Question

I have two tables that I want to get all accounts and their Credit sum and Debit sum even parent records have to sum thier child values

I'm implementing Id ParentId Structure .

1- [AccountChart] Table with these fields :

Id smallint
ParentId smallint Null
AccountName nvarchar(100)

2- [Transaction] Table with these fields

AccountId smallInt
TransactionDate DateTime
Debit decimal(19,5)
Credit decimal(19,5)

here is what I tried

    Create table [AccountChart] (
    id int not null,
    parentId int null,
    AccountName nvarchar(100)

    PRIMARY KEY (ID)
      )

    Create table [TransactionData] (
    id int not null,
    TransactionDate DateTime not null,
    AccountId int not null,
    Credit decimal(19,5),
    Debit decimal(19,5)

    PRIMARY KEY (ID)
    )

    insert into [AccountChart] (id,parentId,AccountName) values (1,null,'level 0');
    insert into [AccountChart] (id,parentId,AccountName) values (2,1,'level 2');
    insert into [AccountChart] (id,parentId,AccountName) values (3,2,'level 3 - 1');
    insert into [AccountChart] (id,parentId,AccountName) values (4,2,'level 3 - 2 ');


    insert into [TransactionData] (id,TransactionDate,AccountId,Credit,Debit) values (1,'2020-03-17',3,1000.0,0.0)
    insert into [TransactionData] (id,TransactionDate,AccountId,Credit,Debit) values (2,'2020-03-17',3,0.0,1000.0)
    insert into [TransactionData] (id,TransactionDate,AccountId,Credit,Debit) values (3,'2020-03-17',4,4000.0,0.0)
    insert into [TransactionData] (id,TransactionDate,AccountId,Credit,Debit) values (4,'2020-03-17',3,6000.0,0.0)
    insert into [TransactionData] (id,TransactionDate,AccountId,Credit,Debit) values (5,'2020-03-17',4,3000.0,0.0)


    ;WITH cteBalances (AccountId, ParentId, Credit, Debit)
    AS (SELECT
      AccountChart.Id,
      AccountChart.ParentId,
      SUM([TransactionData].Credit) AS Credit,
      SUM([TransactionData].Debit) AS Debit
     FROM [TransactionData]
     INNER JOIN AccountChart ON ([TransactionData].AccountId = AccountChart.Id)
     GROUP BY AccountChart.Id,AccountChart.ParentId)

    SELECT
      AccountChart.id,
      AccountChart.ParentId,
      AccountChart.AccountName,
      cteBalances.Credit,
      cteBalances.Debit
    FROM  AccountChart 
    left JOIN cteBalances ON (AccountChart.ID = cteBalances.AccountID)

the problem that I couldn't bring the summary values for the parent records.

the results I get is :

id  ParentId    AccountName   Credit        Debit
1   NULL        level 0       NULL          NULL
2   1           level 2       NULL          NULL
3   2           level 3 - 1   7000.00000    1000.00000
4   2           level 3 - 2   7000.00000    0.00000

but I excpect to be calculate the childs in parent as this

id  ParentId    AccountName   Credit        Debit
1   NULL        level 0       14000.00000   1000.00000
2   1           level 2       14000.00000   1000.00000
3   2           level 3 - 1   7000.00000    1000.00000
4   2           level 3 - 2   7000.00000    0.00000

Answer 1

I like the technique of using Range Keys. If you have a slow moving hierarchy, I would create a table to store these values

Just to expand, the range keys facilitate selection, and variable depth aggregation.

You may notice that the SEQ has an Order by AccountName. In my GL systems, we have a column for presentation sequence. This controls the order rather than ALPHA on name or significant digits on the account.

Example

Declare @Top  int         =  null   --<<  Sets top of Hier Try 2
Declare @Nest varchar(25) = '|---'  --<<  Optional: Added for readability

;with cteP as (
      Select Seq  = cast(10000+Row_Number() over (Order by AccountName) as varchar(500))
            ,ID
            ,ParentID 
            ,Lvl=1
            ,AccountName
      From   [AccountChart] 
      Where  IsNull(@Top,-1) = case when @Top is null then isnull(ParentID ,-1) else ID end
      Union  All
      Select Seq  = cast(concat(p.Seq,'.',10000+Row_Number() over (Order by r.AccountName)) as varchar(500))
            ,r.ID
            ,r.ParentID 
            ,p.Lvl+1
            ,r.AccountName
      From   [AccountChart] r
      Join   cteP p on r.ParentID  = p.ID)
     ,cteR1 as (Select *,R1=Row_Number() over (Order By Seq) From cteP)
     ,cteR2 as (Select A.ID,R2=Max(B.R1) From cteR1 A Join cteR1 B on (B.Seq like A.Seq+'%') Group By A.Seq,A.ID )
Select A.Lvl
      ,A.R1  
      ,B.R2
      ,A.ID
      ,A.ParentID 
      ,AccountName = max(Replicate(@Nest,A.Lvl-1) + A.AccountName)
      ,Credit      = sum(C.Credit)
      ,Debit       = sum(C.Debit)
 From cteR1 A
 Join cteR2 B on A.ID=B.ID
 Join (Select _R1=A.R1,B.* From cteR1 A Join [TransactionData] B on A.ID=B.AccountID ) C on (C._R1 between A.R1 and B.R2)
 Group By A.R1,B.R2,A.Lvl,A.ID,A.ParentID
 Order By A.R1

Returns