Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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

Print | posted on Thursday, October 04, 2007 10:48 AM | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

Feedback

Gravatar

# 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
Gravatar

# 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
Gravatar

# 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
Gravatar

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

Powered by:
Powered By Subtext Powered By ASP.NET