Recursively sum child's nominal to parent, on dynamic level parent-child structure

Question

I have data that looks like this

I am trying to sum the Nominal column to each respective parent, so it will looks like this

I provided the SQL script for the data :

CREATE TABLE [dbo].[Account](
    [AccountSeq] [bigint]  NOT NULL PRIMARY KEY,
    [ParentAccountSeq] [bigint] NULL,
    [AccountCode] [nvarchar](50) NOT NULL,
    [AccountName] [nvarchar](50) NOT NULL,
    [Nominal] [decimal](18, 2) NOT NULL
) 

INSERT [dbo].[Account] ([AccountSeq], [ParentAccountSeq], [AccountCode], [AccountName], [Nominal]) 
VALUES 
    (1, NULL, N'101', N'AKTIVA LANCAR', CAST(0.00 AS Decimal(18, 2)))
    ,(2, 1, N'10101', N'KAS', CAST(0.00 AS Decimal(18, 2)))
    ,(3, 2, N'1010101', N'KAS KECIL (IDR)', CAST(10000.00 AS Decimal(18, 2)))
    ,(4, 2, N'1010102', N'KAS KECIL ($$$)', CAST(15000.00 AS Decimal(18, 2)))
    ,(5, 1, N'10102', N'BANK', CAST(0.00 AS Decimal(18, 2)))
    ,(6, 5, N'1010201', N'BCA PKU AC: 220.391', CAST(20000.00 AS Decimal(18, 2)))
    ,(7, 5, N'1010202', N'BCA PKU AC: 220.279', CAST(25000.00 AS Decimal(18, 2)))
    ,(8, 1, N'10104', N'PIUTANG USAHA', CAST(30000.00 AS Decimal(18, 2)))
    ,(10, 1, N'10105', N'PIUTANG PROYEK', CAST(40000.00 AS Decimal(18, 2)))
    ,(11, NULL, N'201', N'HUTANG JANGKA PENDEK', CAST(50000.00 AS Decimal(18, 2)))
    ,(12, NULL, N'301', N'MODAL', CAST(60000.00 AS Decimal(18, 2)))

The last SQL query I tried:

WITH cteTest AS (
    SELECT
    AccountSeq,
    ParentAccountSeq,
    AccountCode,
    AccountName,
    CAST(Nominal AS DECIMAL(18,2)) AS Nominal
    FROM
    [Account]
    WHERE
    ParentAccountSeq IS NULL

    UNION ALL

    SELECT
    a.AccountSeq,
    a.ParentAccountSeq,
    a.AccountCode,
    a.AccountName,
    CAST((a.Nominal + cte.Nominal) AS DECIMAL(18,2)) AS Nominal
    FROM
    [Account] a
    INNER JOIN [cteTest] cte ON cte.AccountSeq = a.ParentAccountSeq
)
SELECT
*
FROM
[cteTest]
ORDER BY
AccountSeq

Please can anyone help me to solve this?

Answer 1

For this task we can collect all children for every account and then sum nominals to parent account.

1. Anchor part of recursive query.
   We take all accounts from table as root.

2. Recursive part. Recursive collect children's (thru link to parent), children's of children and so on. Column rootAccount is the same for all children on every branch of the tree.
   Output of recursive query below.

3. Aggregate nominal by rootAccount.

Here lvl and accs columns - for debug and clarity. See example

Your source data

AccountSeq	ParentAccountSeq	AccountCode	AccountName	Nominal
1	null	101	AKTIVA LANCAR	0.00
2	1	10101	KAS	0.00
3	2	1010101	KAS KECIL (IDR)	10000.00
4	2	1010102	KAS KECIL ($$$)	15000.00
5	1	10102	BANK	0.00
6	5	1010201	BCA PKU AC: 220.391	20000.00
7	5	1010202	BCA PKU AC: 220.279	25000.00
8	1	10104	PIUTANG USAHA	30000.00
10	1	10105	PIUTANG PROYEK	40000.00
11	null	201	HUTANG JANGKA PENDEK	50000.00
12	null	301	MODAL	60000.00

Recursive query

with r as(
  select 0 lvl,AccountSeq rootAccount,AccountSeq incAccount,Nominal
  from tAccount
  union all
  select lvl+1 lvl,r.rootAccount,t.AccountSeq incAccount,t.Nominal
  from r inner join tAccount t on t.ParentAccountSeq=r.incAccount
)
select a.*,t.sumNominal,accs
from tAccount a
left join(
   select rootAccount,sum(nominal)sumNominal
     ,string_agg(cast(incAccount as varchar),',') accs
   from r
   group by rootAccount
  ) t on a.AccountSeq=t.rootAccount

AccountSeq	ParentAccountSeq	AccountCode	AccountName	Nominal	sumNominal	accs
1	null	101	AKTIVA LANCAR	0.00	140000.00	1,2,5,8,10,6,7,3,4
2	1	10101	KAS	0.00	25000.00	2,3,4
3	2	1010101	KAS KECIL (IDR)	10000.00	10000.00	3
4	2	1010102	KAS KECIL ($$$)	15000.00	15000.00	4
5	1	10102	BANK	0.00	45000.00	5,6,7
6	5	1010201	BCA PKU AC: 220.391	20000.00	20000.00	6
7	5	1010202	BCA PKU AC: 220.279	25000.00	25000.00	7
8	1	10104	PIUTANG USAHA	30000.00	30000.00	8
10	1	10105	PIUTANG PROYEK	40000.00	40000.00	10
11	null	201	HUTANG JANGKA PENDEK	50000.00	50000.00	11
12	null	301	MODAL	60000.00	60000.00	12

Details.
Recursive query output before aggregate

with r as(
  select 0 lvl,AccountSeq rootAccount,AccountSeq incAccount,Nominal
    ,cast(AccountSeq as varchar(100)) path
    ,AccountName
  from tAccount
  union all
  select lvl+1 lvl,r.rootAccount,t.AccountSeq incAccount,t.Nominal
    ,cast(concat(path,',',cast(t.AccountSeq as varchar(100)))as varchar(100)) path
    ,t.AccountName
  from r inner join tAccount t on t.ParentAccountSeq=r.incAccount
)
select * from r order by rootAccount,lvl;

lvl	rootAccount	incAccount	Nominal	path	AccountName
0	1	1	0.00	1	AKTIVA LANCAR
1	1	2	0.00	1,2	KAS
1	1	5	0.00	1,5	BANK
1	1	8	30000.00	1,8	PIUTANG USAHA
1	1	10	40000.00	1,10	PIUTANG PROYEK
2	1	6	20000.00	1,5,6	BCA PKU AC: 220.391
2	1	7	25000.00	1,5,7	BCA PKU AC: 220.279
2	1	3	10000.00	1,2,3	KAS KECIL (IDR)
2	1	4	15000.00	1,2,4	KAS KECIL ($$$)
0	2	2	0.00	2	KAS
1	2	3	10000.00	2,3	KAS KECIL (IDR)
1	2	4	15000.00	2,4	KAS KECIL ($$$)
0	3	3	10000.00	3	KAS KECIL (IDR)
0	4	4	15000.00	4	KAS KECIL ($$$)
0	5	5	0.00	5	BANK
1	5	6	20000.00	5,6	BCA PKU AC: 220.391
1	5	7	25000.00	5,7	BCA PKU AC: 220.279
0	6	6	20000.00	6	BCA PKU AC: 220.391
0	7	7	25000.00	7	BCA PKU AC: 220.279
0	8	8	30000.00	8	PIUTANG USAHA
0	10	10	40000.00	10	PIUTANG PROYEK
0	11	11	50000.00	11	HUTANG JANGKA PENDEK
0	12	12	60000.00	12	MODAL

fiddle