recursive sum

Question

Post reply

recursive sum

gentong.bocor

Default port

Points: 1480
More actions
August 28, 2014 at 11:59 pm

#99064

hi.. i have a table like this
1. Chart of account table
AccountDescription Parent
1ASSETS 0
101 CURRENT ASSETS 1
10100Cash and Bank 101
10101Petty Cash 10100
10301Cash on Hand 10100
10501Bank 10100
10701Time Deposit 10100
2. Transaction table (only child account)
Account GroupID BankID Amount
10101 2 1 10
10101 2 2 20
10301 2 1 10
10501 2 1 10
10501 2 2 20
10701 2 2 30
I need the output seems like this:
Account GroupID BankID SumAmount
1 2 1 30
1 2 2 70
101 2 1 30
101 2 2 70
10100 2 1 30
10100 2 2 70
10101 2 1 10
10101 2 2 20
10301 2 1 10
10501 2 1 10
10501 2 2 20
10701 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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182879 More actions · Answer 1

Quick question, what SQL Server version are you on?

😎

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

-- create sample data

DROP TABLE #ChartOfAccount

CREATE TABLE #ChartOfAccount (Account INT, [Description] VARCHAR(30), Parent INT)

INSERT INTO #ChartOfAccount (Account, [Description], Parent)

SELECT 1, 'ASSETS', 0 UNION ALL

SELECT 101, 'CURRENT ASSETS', 1 UNION ALL

SELECT 10100, 'Cash and Bank', 101 UNION ALL

SELECT 10101, 'Petty Cash', 10100 UNION ALL

SELECT 10301, 'Cash on Hand', 10100 UNION ALL

SELECT 10501, 'Bank', 10100 UNION ALL

SELECT 10701, 'Time Deposit', 10100;

DROP TABLE #Transaction

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 ALL

SELECT 10101, 2, 2, 20 UNION ALL

SELECT 10301, 2, 1, 10 UNION ALL

SELECT 10501, 2, 1, 10 UNION ALL

SELECT 10501, 2, 2, 20 UNION ALL

SELECT 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 rCTE

UNION ALL

SELECT [Level] = 0,

Account, GroupID, BankID, Amount

FROM #Transaction

ORDER 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 Shaw}

For 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 Moden

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 3

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182879 More actions · Answer 4

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 data

CREATE TABLE #ChartOfAccount (Account INT, [Description] VARCHAR(30), Parent INT)

INSERT INTO #ChartOfAccount (Account, [Description], Parent)

SELECT 1, 'ASSETS', 0 UNION ALL

SELECT 101, 'CURRENT ASSETS', 1 UNION ALL

SELECT 10100, 'Cash and Bank', 101 UNION ALL

SELECT 10101, 'Petty Cash', 10100 UNION ALL

SELECT 10301, 'Cash on Hand', 10100 UNION ALL

SELECT 10501, 'Bank', 10100 UNION ALL

SELECT 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 ALL

SELECT 10101, 2, 2, 20 UNION ALL

SELECT 10301, 2, 1, 10 UNION ALL

SELECT 10501, 2, 1, 10 UNION ALL

SELECT 10501, 2, 2, 20 UNION ALL

SELECT 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.Amount

FROM FINAL_SET FS

WHERE FS.GR_RID = 1

ORDER BY FS.Account,FS.GroupID,FS.BankID;

DROP TABLE #ChartOfAccount

DROP TABLE #Transaction

Results

Account GroupID BankID Amount

-------- -------- ------- -------

1 2 1 30

1 2 2 70

101 2 1 30

101 2 2 70

10100 2 1 30

10100 2 2 70

10101 2 1 10

10101 2 2 20

10301 2 1 10

10501 2 1 10

10501 2 2 20

10701 2 2 30

IO Stats

TALLY LOOP

Table '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.

RECURSIVE

Table '#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 Default port Points: 1480 More actions · Answer 5

Thanks for all your participation solving my query problem..

🙂