asked
Parent Child
I have a table that has a parentid and a childid (coa_cnf_account). I have another table that has the money associated with each of these accounts(rr_data_revenue). I am trying to get the parent total for what is in the parent account and all the accounts that are under that parent. I tried to write a CTE but I am missing something Here is what I got so far. ;WITH recurs AS ( SELECT coa_cnf_account.COAAccountId, coa_cnf_account.ParentAccountId, case when accountingtype = 1 then sum(RevenueAmount) else 0 end as Debit, case when accountingtype = 2 then sum(RevenueAmount) else 0 end as Credit FROM RR_DATA_REVENUE INNER JOIN coa_cnf_account on coa_cnf_account.COAAccountId = RR_DATA_REVENUE.coaaccountid -- WHERE ParentAccountId IS NOT NULL group by coa_cnf_account.COAAccountId, coa_cnf_account.ParentAccountId, accountingtype UNION ALL SELECT C.COAAccountId, C.ParentAccountId, R.Debit, r.Credit FROM RR_DATA_REVENUE INNER JOIN coa_cnf_account c on c.COAAccountId = RR_DATA_REVENUE.coaaccountid INNER JOIN recurs r ON r.ParentAccountid = c.COAAccountId ) SELECT R.COAAccountId, c.ParentAccountiD, c.Code, c.Name, sum(r.Debit) as Debit, sum(r.credit) as Credit, SUM(r.Debit - r.Credit) AS BAL FROM recurs R INNER JOIN coa_cnf_account c on c.COAAccountId = r.coaaccountid GROUP BY R.COAAccountId, c.Code, c.Name, c.ParentAccountiD ORDER BY COAAccountId, c.Code, c.Name Currently this returns me this ![alt text][1] I know that this is incorrect because I can do a sum on the amount for coaaccountid = 2 and it it 0 which is correct. It should have a total of 135.42 which is the sum of the 2 accounts under it. See this.. ![alt text][2] Any Help would be appreciated. ***Text from the comment, originally posted as answer by OP due to the character number limit in comments:*** I have 2 tables that I will be pulling the information from the configuration table which holds the parent key and the data table which has the balances. Configuration table is CREATE TABLE [dbo].[COA_CNF_Account]( [COAAccountId] [int] IDENTITY(1,1) NOT NULL, [COAAccountTypeId] [int] NOT NULL, [ParentAccountId] [int] NULL, [COAAccountAK] [varchar](32) NOT NULL, [Code] [nvarchar](10) NOT NULL, [Name] [nvarchar](50) NULL, [Description] [nvarchar](100) NULL ,[SystemAccount] [smallint] NOT NULL, [Enabled] [smallint] NOT NULL, [ExternalCode] [nvarchar](25) NULL ) The data table is CREATE TABLE [dbo].[RR_DATA_Revenue]( [RevenueRecognitionDataId] [bigint] IDENTITY(1,1) NOT NULL, [COAAccountId] [int] NOT NULL, [RevenueAmount] [float] NOT NULL, [AccountingType] [smallint] NULL ) I can get the balance for each account with select COAAccountId, sum(RevenueAmount) from RR_DATA_Revenue group by COAAccountId The problem is that I need to be able to roll that total up to parent, grandparent and greatparent and so on. There is NO limit to how many children or parents an account can have. So I need to see something like this AccountID ParentID COABalance TotalForAllChildrenInclMyMoney ----------- ----------- ---------------------- ------------------------------ 1 0 10 48 2 1 5 5 3 1 15 24 4 3 9 9 An account can it's own balance also that is in the column COABalance. Then I need to be able to show all the money for that account and all it's children in the column Total for all children(including my money). Any help would be wonderful Thank you [1]: /storage/temp/4485-question1.jpg [2]: /storage/temp/4486-2.jpg
Can you mock up some example ddl and data to better explain the base data. My gut feel after first looking is that 541.68 is 4 times the amount you expected (135.42), and there are 4 child accounts of coaaccountid 2 - so somewhere you are recursing or aggregating too much. How many levels of accounts are there? The example only shows 2 - in which case a recursive CTE is possible overkill, but your data might have more.
The text in this comment has been moved to the original question, the section beginning with ***Text from the comment, originally posted as answer by OP due to the character number limit in comments:***
@kdixon By the time I slowly typed my comment under your original post, you already clarified the details, but still there is a question about the data in the screenshot. It looks like account = 1 has 2 child accounts (2 and 3), and one of the child accounts (3 to be exact) has its own child account = 4. In this scenario, the total for account 3 is correct ($15 of its own plus $9 from its child = 24), but the total for parent account = 1 is not correct, the total should be $24 (from account 3) plus $5 from account = 2 plus $10 of its own, so the total should be $39, but you show it as $48 (?!?). Could you please clarify how you have arrived to the figure of $48 in place of expected $39 for this account. I understand that it is possible to come up with $48 by adding the money from account 4, but this would be a **double-dipping** because the $9 from account 4 has been already counted as **total for all children (including my money)** for account = 3. Is this how you would like to calculate the sum (adding same money more then once)? Thank you.
@kdixon Is it possible for the parent account of a certain account type to have deeper level child/grandchild etc. accounts of a different account type? Or every branch in the hierarchy has accounts of the same account type, kinda dictated by the parent-most account? Also, I would like to know whether the parent accounts have zero or null as their respective parent account ID, or maybe they may have either one depending on which account. Please let me know. Thank you.
@kdixon In case if the data has more than 2 levels (***the only scenario*** which will justify wheeling out the recursive CTE) then please clarify whether the sum of balances for deeper levels should foot the balance of their respective immediate parent or not. This will help greatly with figuring out the correct method to aggregate the values. For example, using the provided sample data, suppose that COAAccountId = 7 (whose parent is 2) has few more child accounts of its own, say 3 "grandchild" accounts referencing account 7 as their parent. Should the sum of balances of these 3 grandchild accounts be equal to the balance of account 7? Or you need to just sum up the balances of grandchild ***and*** child accounts to come up with parent-most account balance. Again, as @Kev Riley ♦♦ already pointed out, none of it matters if you only have but 2 levels in this hierarchy. Please clarify. Thank you.
Before moving on to the solution, I would like to mention that I hope that the provided table definitions are not complete. In other words, the actual tables are not rolled out as heaps, but are rather properly clustered. From the table definitions it appears that there might be multiple rows for the same COAAccountId in the table named RR\_DATA\_Revenue. This means that the rows should be pre-aggregated ***BEFORE*** the recursive CTE is wheeled out. Generally speaking, using [recursive CTE][1] to query the tables organized as adjacency lists is pretty straightforward and well documented. The alternative approach with redesigning the tables as nested sets is well worth considering because it may lead to huge performance gains in case if performance of recursive CTE is below acceptable. Please read the related articles by @Jeff Moden, these are a must read: - [Hierarchies on Steroids #1][2] - [Hierarchies on Steroids #2][3] Once the data is returned via recursive CTE, the only problem remains is to calculate the sum of revenue amounts for all children on any deeper levels (without double dipping on intermediate levels :) ). Because the handy "Sort" column is included, it can be used to query any returned accounts for all its direct and indirect children in order to sum up the revenue amounts. Please include the Sort column in the select list so it is easy to see how it works. Here is the script which will produce desired results: ;with aggregated as ( select a.COAAccountId, a.ParentAccountId, max(a.Code) Code, max(a.[Name]) [Name], sum(r.RevenueAmount) RevenueAmount from dbo.COA_CNF_Account a inner join dbo.RR_DATA_Revenue r on a.COAAccountId = r.COAAccountId group by a.COAAccountId, a.ParentAccountId ), recurs as ( -- anchor - records with just the parentmost accounts select COAAccountId, ParentAccountId, Code, [Name], RevenueAmount, 0 TreeLevel, '/' + cast(COAAccountId as varchar(max)) + '/' Sort from aggregated where ParentAccountId is null -- recursive part union all select a.COAAccountId, a.ParentAccountId, a.Code, a.[Name], a.RevenueAmount, r.TreeLevel + 1, r.Sort + cast(a.COAAccountId as varchar) + '/' from aggregated a inner join recurs r on a.ParentAccountId = r.COAAccountId ) select r.COAAccountId, r.ParentAccountId, r.Code, r.[Name], r.RevenueAmount, r.RevenueAmount + isnull(c.r, 0) TotalForAllChildrenInclMyMoney from recurs r cross apply ( select sum(RevenueAmount) from recurs where TreeLevel > r.TreeLevel and charindex('/' + cast(r.COAAccountId as varchar) + '/', Sort) > 0 ) c(r) order by Sort; go Hope this helps. Oleg [1]:
https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx [2]:
http://www.sqlservercentral.com/articles/Hierarchy/94040/ [3]:
http://www.sqlservercentral.com/articles/T-SQL/94570/
18 People are following this question.
