October 3, 2007 at 5:34 pm
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!
🙂
October 3, 2007 at 9:37 pm
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
October 4, 2007 at 2:31 am
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"
October 4, 2007 at 10:01 am
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