## recursive sum

 Author Message gentong.bocor SSC Journeyman Group: General Forum Members Points: 83 Visits: 67 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 Eirikur Eiriksson SSChampion Group: General Forum Members Points: 14882 Visits: 18591 Quick question, what SQL Server version are you on? ChrisM@Work SSCoach Group: General Forum Members Points: 16042 Visits: 19525 `-- 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 Jeff Moden SSC Guru Group: General Forum Members Points: 84859 Visits: 41069 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 ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs Eirikur Eiriksson SSChampion Group: General Forum Members Points: 14882 Visits: 18591 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. gentong.bocor SSC Journeyman Group: General Forum Members Points: 83 Visits: 67 Thanks for all your participation solving my query problem..:-)