Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


recursive sum


recursive sum

Author
Message
gentong.bocor
gentong.bocor
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 67
hi.. i have a table like this

1. Chart of account table

Account   Description    Parent
1   ASSETS    0
101 CURRENT ASSETS    1
10100   Cash and Bank    101
10101   Petty Cash    10100
10301   Cash on Hand     10100
10501   Bank    10100
10701   Time 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
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6691 Visits: 17681
Quick question, what SQL Server version are you on?
Cool
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8949 Visits: 19009
-- 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
Exploring Recursive CTEs by Example Dwain Camps
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44940 Visits: 39859
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6691 Visits: 17681
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.
Cool

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
gentong.bocor
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 67
Thanks for all your participation solving my query problem..
:-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search