• 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.