December 10, 2007 at 8:00 am
Hi there,
I am working with an accounting system, where the chart of accounts tree structure is built within a single table i.e. accountid and parentid, now the tree can go down different levels (currently up to a max of 6). The account no is 8 digits. For example I have the following:-
Level 1: ASSETS: 10000000
Level 2: CURRENT ASSETS: 11000000
Level 3: CASH AND BANK: 11100000
Level 4: CASH AND PETTY CASH: 11110000
Level 5: CASH IN USD: 11111000
Level 6: FINANCE PETTY CASH: 11111050
Now transactions are passed on to the FINANCE PETTY CASH account and I would like to traverse the tree in order to calculate the parent account balances such as Current Assets. I am seriously stuck and don't know how to go about this.
Please someone help!
December 10, 2007 at 11:26 am
Check out Recursive CTEs (common table expressions) in BOL here: http://msdn2.microsoft.com/en-us/library/ms186243.aspx.
December 10, 2007 at 3:30 pm
All common levels share the same parent ID correct? If so, why not group by parentid and sum the values. If you needed a breakdown you could also group by asset type. E.g. assets, current assets,cash and bank etc.
December 10, 2007 at 3:40 pm
here is a simple example
DECLARE @MyTable TABLE
(
ParentID INT,
AcctNbr INT,
[Level] INT,
Asset_Type varchar(25),
Amt money
)
INSERT INTO @MyTable
SELECT '1', 12345, 1, 'ASSETS', 10000000 UNION ALL
SELECT '1', 12345, 2, 'CURRENT ASSETS', 11000000 UNION ALL
SELECT '1', 12345, 3, 'CASH AND BANK', 11100000 UNION ALL
SELECT '1', 12345, 4, 'CASH AND PETTY CASH', 11110000 UNION ALL
SELECT '1', 12345, 5, 'CASH IN USD', 11111000 UNION ALL
SELECT '1', 12345, 6, 'FINANCE PETTY CASH', 11111050
SELECT ParentID, SUM(AMT)AS [Total_Parent_ID]
FROM @MyTable
GROUP BY ParentID
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply