Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 recursive sum Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, August 28, 2014 11:59 PM
 Grasshopper Group: General Forum Members Last Login: Monday, March 9, 2015 4:10 AM Points: 12, Visits: 21
 hi.. i have a table like this1. Chart of account table `Account Description Parent1 ASSETS 0101 CURRENT ASSETS 110100 Cash and Bank 10110101 Petty Cash 1010010301 Cash on Hand 1010010501 Bank 1010010701 Time Deposit 10100`2. Transaction table (only child account)`Account GroupID BankID Amount10101 2 1 1010101 2 2 2010301 2 1 1010501 2 1 1010501 2 2 2010701 2 2 30`I need the output seems like this:`Account GroupID BankID SumAmount1 2 1 301 2 2 70101 2 1 30101 2 2 7010100 2 1 3010100 2 2 7010101 2 1 1010101 2 2 2010301 2 1 1010501 2 1 1010501 2 2 2010701 2 2 30`So, it's populated sum within GroupID & BankID for each parents of account.Can u pls give me the CTE recursion query ?Thanks
Post #1608497
 Posted Friday, August 29, 2014 2:11 AM
 SSCarpal Tunnel Group: General Forum Members Last Login: Today @ 3:35 AM Points: 4,039, Visits: 10,415
 Quick question, what SQL Server version are you on?
Post #1608510
 Posted Friday, August 29, 2014 5:32 AM
 SSCertifiable Group: General Forum Members Last Login: 2 days ago @ 10:12 AM Points: 7,291, Visits: 15,324
 `-- create sample dataDROP TABLE #ChartOfAccountCREATE TABLE #ChartOfAccount (Account INT, [Description] VARCHAR(30), Parent INT)INSERT INTO #ChartOfAccount (Account, [Description], Parent)SELECT 1, 'ASSETS', 0 UNION ALLSELECT 101, 'CURRENT ASSETS', 1 UNION ALLSELECT 10100, 'Cash and Bank', 101 UNION ALLSELECT 10101, 'Petty Cash', 10100 UNION ALLSELECT 10301, 'Cash on Hand', 10100 UNION ALLSELECT 10501, 'Bank', 10100 UNION ALLSELECT 10701, 'Time Deposit', 10100;DROP TABLE #TransactionCREATE TABLE #Transaction (Account INT, GroupID INT, BankID INT, Amount INT) -- (only child account)INSERT INTO #Transaction (Account, GroupID, BankID, Amount)SELECT 10101, 2, 1, 10 UNION ALLSELECT 10101, 2, 2, 20 UNION ALLSELECT 10301, 2, 1, 10 UNION ALLSELECT 10501, 2, 1, 10 UNION ALLSELECT 10501, 2, 2, 20 UNION ALLSELECT 10701, 2, 2, 30;-- solution with sample data provided;WITH rCTE AS ( SELECT [Level] = 1, c.Parent AS Account, t.GroupID, t.BankID, Amount = SUM(t.Amount) FROM #Transaction t INNER JOIN #ChartOfAccount c ON c.Account = t.Account GROUP BY c.Parent, t.GroupID, t.BankID UNION ALL SELECT [Level] = lr.[Level] + 1, tr.Parent AS Account, lr.GroupID, lr.BankID, Amount FROM rCTE lr INNER JOIN #ChartOfAccount tr ON tr.Account = lr.Account AND Parent > 0)SELECT [Level], Account, GroupID, BankID, Amount FROM rCTEUNION ALLSELECT [Level] = 0, Account, GroupID, BankID, Amount FROM #TransactionORDER BY Account, GroupID, BankID` “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps
Post #1608548
 Posted Friday, August 29, 2014 7:15 AM
 SSC-Dedicated Group: General Forum Members Last Login: Yesterday @ 9:10 PM Points: 37,867, Visits: 34,744
 If you're going to be doing a fair bit of reporting on hierarchical structures, please consider the following article. It takes a bit to setup but, once in place, is very quick and offers several different methods to analyze hierarchical structures.http://www.sqlservercentral.com/articles/T-SQL/94570/ --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1608590
 Posted Sunday, August 31, 2014 12:49 AM
 SSCarpal Tunnel Group: General Forum Members Last Login: Today @ 3:35 AM Points: 4,039, Visits: 10,415
 Here is an alternative method without recursion, uses Tally table type looping instead. The code uses Chris's sample data to produce comparable output. It is simplified and limited to that data set, not meant as a full solution but rather as a demonstration of the method.`USE tempdb;GO-- create sample dataCREATE TABLE #ChartOfAccount (Account INT, [Description] VARCHAR(30), Parent INT)INSERT INTO #ChartOfAccount (Account, [Description], Parent)SELECT 1, 'ASSETS', 0 UNION ALLSELECT 101, 'CURRENT ASSETS', 1 UNION ALLSELECT 10100, 'Cash and Bank', 101 UNION ALLSELECT 10101, 'Petty Cash', 10100 UNION ALLSELECT 10301, 'Cash on Hand', 10100 UNION ALLSELECT 10501, 'Bank', 10100 UNION ALLSELECT 10701, 'Time Deposit', 10100;CREATE TABLE #Transaction (Account INT, GroupID INT, BankID INT, Amount INT) -- (only child account)INSERT INTO #Transaction (Account, GroupID, BankID, Amount)SELECT 10101, 2, 1, 10 UNION ALLSELECT 10101, 2, 2, 20 UNION ALLSELECT 10301, 2, 1, 10 UNION ALLSELECT 10501, 2, 1, 10 UNION ALLSELECT 10501, 2, 2, 20 UNION ALLSELECT 10701, 2, 2, 30;/* CTE ACCOUNT_UPLINE Creates one row for each node in the account's upline. Note: simplified method using integer division and a pseudo Tally table*/;WITH ACCOUNT_UPLINE AS ( SELECT X.Account ,X.UPLINE_PART FROM ( SELECT COA.Account ,COA.Parent ,X.N ,CASE WHEN X.N = -2 THEN COA.Account WHEN POWER(10,X.N) = 0 THEN COA.Parent ELSE COA.Parent / POWER(10,X.N) END AS UPLINE_PART FROM #ChartOfAccount COA CROSS APPLY (SELECT N FROM (VALUES (-2),(-1),(2),(4),(5),(6),(7)) AS X(N)) AS X ) AS X WHERE X.UPLINE_PART > 0 OR ( X.Parent = 0 AND X.N = 1))/* CTE FINAL_SET Window function aggregation and grouping*/,FINAL_SET AS( SELECT AU.UPLINE_PART AS Account ,TR.GroupID ,TR.BankID ,ROW_NUMBER() OVER ( PARTITION BY TR.GroupID,TR.BankID,AU.UPLINE_PART ORDER BY (SELECT NULL) ) AS GR_RID ,SUM(TR.Amount) OVER ( PARTITION BY TR.GroupID,TR.BankID,AU.UPLINE_PART ) AS Amount FROM #Transaction TR INNER JOIN ACCOUNT_UPLINE AU ON TR.Account = AU.Account)SELECT FS.Account ,FS.GroupID ,FS.BankID ,FS.AmountFROM FINAL_SET FSWHERE FS.GR_RID = 1ORDER BY FS.Account,FS.GroupID,FS.BankID;DROP TABLE #ChartOfAccountDROP TABLE #Transaction`Results`Account GroupID BankID Amount-------- -------- ------- -------1 2 1 301 2 2 70101 2 1 30101 2 2 7010100 2 1 3010100 2 2 7010101 2 1 1010101 2 2 2010301 2 1 1010501 2 1 1010501 2 2 2010701 2 2 30`IO Stats`TALLY LOOPTable 'Worktable'. Scan count 3, logical reads 97, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table '#ChartOfAccount'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table '#Transaction'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.RECURSIVETable '#Transaction'. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table '#ChartOfAccount'. Scan count 2, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 2, logical reads 35, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.`Edit: added IO stats.
Post #1609091
 Posted Sunday, August 31, 2014 8:11 PM
 Grasshopper Group: General Forum Members Last Login: Monday, March 9, 2015 4:10 AM Points: 12, Visits: 21
 Thanks for all your participation solving my query problem..
Post #1609226

 Permissions

 Copyright © 2002-2015 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.