recursive sum for a general ledger balance

  • Hi, I have two tables:

    Accounts { AccountNumber, ParentAccountNumber }

    Transactions { AccountNumber, Amount }

    The accounts table is a chart of accounts, it's self-referenced so I can show this as a tree. The user can register Transactions referring only to lower-level accounts (accounts with no children).

    Take this as a sample data on Accounts table:

    AccountNumber ParentAccountNumber

    100-000-000

    100-001-000 100-000-000

    100-002-000 100-000-000

    100-002-001 100-002-000

    100-002-002 100-002-000

    and in the transactions table:

    AccountNumber Amount

    100-001-000 1000

    100-002-001 500

    100-002-002 300

    I want to get the following result:

    AccountNumber totalAmount

    100-000-000 1800

    100-001-000 1000

    100-002-000 800

    100-002-001 500

    100-002-002 300

    the 100-000-000 sums the amount of all the transactions that starts with 100

    The 100-002-000 is the sum of the amount of all the transactions that starts with 100-002

    The 100-001-000, 100-002-001 and 100-002-002 are the transactions

    What can you suggest to me? I should say the nesting level could be between 3 and 10 levels, but its defined by the user. And the transactions table could reach the 200,000 records.

    Thanks to any one whom could give me any clue!

    🙂

  • the trick is to group by portions of the account number.

    this produces the results you were looking for:

    CREATE TABLE #Accounts (AccountNumber varchar(30), ParentAccountNumber varchar(30) )

    insert into #Accounts(AccountNumber ,ParentAccountNumber)

    SELECT '100-000-000',NULL UNION

    SELECT '100-001-000','100-000-000' UNION

    SELECT '100-002-000','100-000-000' UNION

    SELECT '100-002-001','100-002-000' UNION

    SELECT '100-002-002','100-002-000'

    CREATE TABLE #Transactions(AccountNumber varchar(30), Amount money )

    INSERT INTO #Transactions(AccountNumber, Amount)

    SELECT '100-001-000', 1000 UNION

    SELECT '100-002-001', 500 UNION

    SELECT '100-002-002', 300

    SELECT AccountNumber,sum(Amount) AS Amount from #Transactions group by AccountNumber

    UNION

    SELECT LEFT(AccountNumber,7) + '-000-000' AS AccountNumber,sum(Amount) AS Amount from #Transactions group by LEFT(AccountNumber,7) + '-000-000'

    UNION

    SELECT LEFT(AccountNumber,3) + '-000' AS AccountNumber,sum(Amount) AS Amount from #Transactions group by LEFT(AccountNumber,3) + '-000'

    ORDER BY AccountNumber

    AccountNumber Amount

    ------------------------------ ---------------------

    100-000 1800.0000

    100-001-000 1000.0000

    100-002-000 800.0000

    100-002-001 500.0000

    100-002-002 300.0000

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell's suggestion will work as long as there are only two levels of accounts.

    This will traverse complete tree hierarchy

    -- Prepare sample data

    DECLARE @Accounts TABLE (AccountNumber CHAR(11), ParentAccountNumber CHAR(11))

    INSERT @Accounts

    SELECT '100-000-000', NULL UNION ALL

    SELECT '100-001-000', '100-000-000' UNION ALL

    SELECT '100-002-000', '100-000-000' UNION ALL

    SELECT '100-002-001', '100-002-000' UNION ALL

    SELECT '100-002-002', '100-002-000'

    DECLARE @Transactions TABLE (AccountNumber CHAR(11), Amount MONEY)

    INSERT @Transactions

    SELECT '100-001-000', 1000.00 UNION ALL

    SELECT '100-002-001', 500.00 UNION ALL

    SELECT '100-002-002', 300.00

    -- Setup staging expression

    ;WITH Yak (AccountNumber, Amount)

    AS (

    SELECT AccountNumber,

    SUM(Amount) AS Amount

    FROM @Transactions

    GROUP BY AccountNumber

    UNION ALL

    SELECT a.ParentAccountNumber,

    y.Amount

    FROM @Accounts AS a

    INNER JOIN Yak AS y ON y.AccountNumber = a.AccountNumber

    )

    -- Show the expected resultset

    SELECT AccountNumber,

    SUM(Amount) AS Amount

    FROM Yak

    WHERE AccountNumber IS NOT NULL

    GROUP BY AccountNumber

    ORDER BY AccountNumber


    N 56°04'39.16"
    E 12°55'05.25"

  • Wow! Thank you very much folks ...

    Lowell, your approach does works but has the limit of two levels and I forgot to mention the account number format is variable, so I can't substring in levels using the number because the positions and lengths should change. But I really appreciate your help.

    And Peter, it's just what I was looking for, I tried before using a similar approach but I couldn't get it work, you did it... Thanks a lot! 😀

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply