Sum up a tree hierachy in SQL Server 2005

<< New SQL Server 2005 OUTPUT operator | Home | Simulating cross apply with CSV-records in SQL Server 2000 >>

Sum up a tree hierachy in SQL Server 2005

-- 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          COALESCE(AccountNumber, 'All accounts') AS AccountNumber,
                SUM(Amount) AS Amount
FROM            Yak
WHERE           AccountNumber IS NOT NULL
GROUP BY        AccountNumber
ORDER BY        CASE
                        WHEN AccountNumber IS NULL THEN 1
                        ELSE 0
                END,
                AccountNumber

Feedback

# re: Sum up a tree hierachy in SQL Server 2005

If you had used the nested sets model for the hierarchy, then the query would have been:

SELECT O2.acct_nbr, SUM(S1.acct_amt)
FROM Accounts AS A1, Accounts AS A2,
WHERE A1.lft BETWEEN O2.lft AND A2.rgt
AND A1.acct_nbr = S1.acct_nbr
GROUP BY A2.acct_nbr;

You would avoid complex constraints that you need to prevent cycles and orphans. The table would be normalized. The query will run faster than recursion. Etc.

Adjacency list models are a bad choice for computations.

10/7/2007 11:06 PM | Joe Celko

# re: Sum up a tree hierachy in SQL Server 2005

Peso -- why all the NULL handling functions (COALESCE, the CASE expression) when you are not allowing any NULL values via the WHERE clause? Am I missing something or should it just be:

SELECT AccountNumber,
SUM(Amount) AS Amount
FROM Yak
WHERE AccountNumber IS NOT NULL
GROUP BY AccountNumber
ORDER BY AccountNumber

??

10/12/2007 10:53 PM | Jeff

# re: Sum up a tree hierachy in SQL Server 2005

Just for the case there are several "top accounts" to deal with.

10/13/2007 7:13 PM | Peso

# re: Sum up a tree hierachy in SQL Server 2005

It's perfect solution to make Trial Balance and Balance Sheet in Financial Accounting Project.

Thanks to All.

1/4/2012 12:54 PM | Mahesh Jarange

Comments have been closed on this topic.

Thinking outside the box

My Links

Advertisement

News

Archives

Post Categories