Here is an alternative method without recursion, uses Tally table type looping instead. The code uses Chris's sample data to produce comparable output. It is simplified and limited to that data set, not meant as a full solution but rather as a demonstration of the method.
😎
USE tempdb;
GO
-- create sample data
CREATE TABLE #ChartOfAccount (Account INT, [Description] VARCHAR(30), Parent INT)
INSERT INTO #ChartOfAccount (Account, [Description], Parent)
SELECT 1, 'ASSETS', 0 UNION ALL
SELECT 101, 'CURRENT ASSETS', 1 UNION ALL
SELECT 10100, 'Cash and Bank', 101 UNION ALL
SELECT 10101, 'Petty Cash', 10100 UNION ALL
SELECT 10301, 'Cash on Hand', 10100 UNION ALL
SELECT 10501, 'Bank', 10100 UNION ALL
SELECT 10701, 'Time Deposit', 10100;
CREATE TABLE #Transaction (Account INT, GroupID INT, BankID INT, Amount INT) -- (only child account)
INSERT INTO #Transaction (Account, GroupID, BankID, Amount)
SELECT 10101, 2, 1, 10 UNION ALL
SELECT 10101, 2, 2, 20 UNION ALL
SELECT 10301, 2, 1, 10 UNION ALL
SELECT 10501, 2, 1, 10 UNION ALL
SELECT 10501, 2, 2, 20 UNION ALL
SELECT 10701, 2, 2, 30;
/* CTE ACCOUNT_UPLINE
Creates one row for each node in the account's upline.
Note: simplified method using integer division and
a pseudo Tally table
*/
;WITH ACCOUNT_UPLINE AS
(
SELECT
X.Account
,X.UPLINE_PART
FROM
(
SELECT
COA.Account
,COA.Parent
,X.N
,CASE
WHEN X.N = -2 THEN COA.Account
WHEN POWER(10,X.N) = 0 THEN COA.Parent
ELSE COA.Parent / POWER(10,X.N)
END AS UPLINE_PART
FROM #ChartOfAccount COA
CROSS APPLY
(SELECT N FROM (VALUES (-2),(-1),(2),(4),(5),(6),(7)) AS X(N)) AS X
) AS X
WHERE X.UPLINE_PART > 0
OR ( X.Parent = 0 AND X.N = 1)
)
/* CTE FINAL_SET
Window function aggregation and grouping
*/
,FINAL_SET AS
(
SELECT
AU.UPLINE_PART AS Account
,TR.GroupID
,TR.BankID
,ROW_NUMBER() OVER
(
PARTITION BY TR.GroupID,TR.BankID,AU.UPLINE_PART
ORDER BY (SELECT NULL)
) AS GR_RID
,SUM(TR.Amount) OVER
(
PARTITION BY TR.GroupID,TR.BankID,AU.UPLINE_PART
) AS Amount
FROM #Transaction TR
INNER JOIN ACCOUNT_UPLINE AU
ON TR.Account = AU.Account
)
SELECT
FS.Account
,FS.GroupID
,FS.BankID
,FS.Amount
FROM FINAL_SET FS
WHERE FS.GR_RID = 1
ORDER BY FS.Account,FS.GroupID,FS.BankID;
DROP TABLE #ChartOfAccount
DROP TABLE #Transaction
Results
Account GroupID BankID Amount
-------- -------- ------- -------
1 2 1 30
1 2 2 70
101 2 1 30
101 2 2 70
10100 2 1 30
10100 2 2 70
10101 2 1 10
10101 2 2 20
10301 2 1 10
10501 2 1 10
10501 2 2 20
10701 2 2 30
IO Stats
TALLY LOOP
Table 'Worktable'. Scan count 3, logical reads 97, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#ChartOfAccount'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Transaction'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
RECURSIVE
Table '#Transaction'. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#ChartOfAccount'. Scan count 2, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 35, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Edit: added IO stats.