July 14, 2009 at 5:58 am
Hello,
I am wondering if we can get the desire result without using cursor/loop. I have a table with sample data as below:
RecordID ParentRecordIDCredit
1 NULL 10
2 NULL 5
3 1 7
4 1 8
5 2 15
7 5 10
Now i want to write a t-sql statement without using cursor/loop which will do the sum of the credits of all the child records (including parent record) for the parent records whose ParentRecordId is NULL. So it means for the RecordID 2, it should add the credit of RecordID 2,5 and 7.
So the result will look like below:
RecordIDTotal Credits
125
230
-Deepak
July 14, 2009 at 6:11 am
Off the top of my head....
with cteResursive(BaseRecordId,RecordID, ParentRecordID, Credit)
as
(
Select RecordId,RecordId,ParentRecordId,Credit
from yourTab
where ParentRecordId is null
union all
Select BaseRecordId,RecordId,ParentRecordId,Credit
from cteResursive join yourTab on YourTab.ParentNodeId = cteResursive.RecordID
)
Select BaseRecordId,sum(credit)
from cteRecursive
group by BaseRecordId
July 14, 2009 at 6:13 am
This is one way to get it:
DECLARE @test-2 TABLE (
RecordID int,
ParentRecordId int,
Credit int
)
INSERT INTO @test-2 VALUES (1, NULL, 10)
INSERT INTO @test-2 VALUES (2, NULL, 5)
INSERT INTO @test-2 VALUES (3, 1, 7)
INSERT INTO @test-2 VALUES (4, 1, 8)
INSERT INTO @test-2 VALUES (5, 2, 15)
INSERT INTO @test-2 VALUES (7, 5, 10)
SELECT RecordId, SumCredit = Credit + (SELECT SUM(Credit) FROM @test-2 WHERE ParentRecordId = SRC.RecordID)
FROM @test-2 AS SRC
WHERE ParentRecordId IS NULL
-- Gianluca Sartori
July 14, 2009 at 6:14 am
Dave Ballantyne (7/14/2009)
Off the top of my head....
with cteResursive(BaseRecordId,RecordID, ParentRecordID, Credit)as
(
Select RecordId,RecordId,ParentRecordId,Credit
from yourTab
where ParentRecordId is null
union all
Select BaseRecordId,RecordId,ParentRecordId,Credit
from cteResursive join yourTab on YourTab.ParentNodeId = cteResursive.RecordID
)
Select BaseRecordId,sum(credit)
from cteRecursive
group by BaseRecordId
Nice, I vote for your solution Dave. I didn't see it and I posted mine.
-- Gianluca Sartori
July 14, 2009 at 6:51 am
Dave, it does not work. It throws the following error:
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
I swap the joining fields for both tables in the "On" clause to stop the infinite recursion, but in that case, I get the different result.
-Deepak
July 14, 2009 at 7:04 am
Why this problem is different with others is that here the t-sql statement should be able to sum the credits of n level of child records for the main parent record.
A CTE works for the child records which have direct relationship to parent records. But what about the child records which has indirect relationship to their parent's parent record.
It seems that only solution here is to use cursor......
What all other think?
July 14, 2009 at 7:08 am
Small correction...
with cteResursive(BaseRecordId,RecordID, ParentRecordID, Credit)
as
(
Select RecordId,RecordId,ParentRecordId,Credit
from yourTab
where ParentRecordId is null
union all
Select BaseRecordId,RecordId,ParentRecordId,Credit
from cteResursive join yourTab on YourTab.ParentRecordID = cteResursive.RecordID
where ParentRecordId is not null
)
Select BaseRecordId,sum(credit)
from cteRecursive
group by BaseRecordId
If you run that and you get the recursive error please post DDL and sample data
July 14, 2009 at 7:13 am
Deepak Jain (7/14/2009)
It seems that only solution here is to use cursor......
I already posted a solution without cursors. If you can't get Dave's one to work, use mine.
There are very few things that can't be done without cursors.
-- Gianluca Sartori
July 14, 2009 at 7:27 am
Hi Gianluca,
Your solution too has a bug. It is not adding the third level of child. When I run it, it gives the following result:
RecordID SumCredit
125
220
The Sum of RecordID 2 should be 30 not 20 as it needs to add the credit of itself and all it's direct and indirect child i.e. RecordId 5 and 7.
-Deepak
July 14, 2009 at 7:34 am
No luck dave,
Here is the DDL and Sample data:
DECLARE @RTest TABLE
(
RecordID int,
ParentRecordId int,
Credit int
)
INSERT INTO @RTest VALUES (1,NULL,10)
INSERT INTO @RTest VALUES (2,NULL,5)
INSERT INTO @RTest VALUES (3,1,7)
INSERT INTO @RTest VALUES (4,1,8)
INSERT INTO @RTest VALUES (5,2,15)
INSERT INTO @RTest VALUES (7,5,10)
-Deepak
July 14, 2009 at 7:37 am
Tried and tested solution
create table #t1(
RecordID integer,
ParentRecordID integer,
Credit integer)
go
insert into #t1 values(1, NULL, 10)
insert into #t1 values(2, NULL, 5)
insert into #t1 values(3, 1, 7)
insert into #t1 values(4, 1, 8)
insert into #t1 values(5, 2, 15)
insert into #t1 values(7, 5 ,10)
go
with cteRecursive(BaseRecordId,RecordID, ParentRecordID, Credit)
as
(
Select RecordId,RecordId,ParentRecordId,Credit
from #t1 yourTab
where ParentRecordId is null
union all
Select BaseRecordId,yourTab.RecordId,yourTab.ParentRecordId,yourTab.Credit
from cteRecursive join #t1 yourTab on YourTab.ParentRecordID = cteRecursive.RecordID
where yourTab.ParentRecordId is not null
)
Select BaseRecordId,sum(credit)
from cteRecursive
group by BaseRecordId
go
July 14, 2009 at 7:47 am
Deepak Jain (7/14/2009)
Hi Gianluca,Your solution too has a bug. It is not adding the third level of child. When I run it, it gives the following result:
RecordID SumCredit
125
220
The Sum of RecordID 2 should be 30 not 20 as it needs to add the credit of itself and all it's direct and indirect child i.e. RecordId 5 and 7.
-Deepak
You're right. Sorry for posting a wrong qry.
I still believe you can work around Dave's suggestions. Cursor based code can almost every time be converted to set based.
-- Gianluca Sartori
July 14, 2009 at 7:52 am
Wow!!! It works great!!!
Thanks Dave, and Gianluca you too.
-Deepak
July 17, 2009 at 6:20 am
Hi deepak,
I am just curius to know whether the above solution worked for 4th, 5th level of the child records. I tried here with some sample data but didnt get the desired output.
July 17, 2009 at 7:18 am
manish.singh (7/17/2009)
Hi deepak,I am just curius to know whether the above solution worked for 4th, 5th level of the child records. I tried here with some sample data but didnt get the desired output.
It should work to the 100th level , if you get that deep you can override with the MAXRECURSION(X) option.
If it doesent , post some sample data and ill take a look.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply