Update col value. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update col value.

Author

Topic

Vikas_3r
Starting Member

5 Posts

Posted - 2012-04-18 : 09:20:26

My table is such that

sn name sn1 amt tot
1 arif 1 100
2 singh 1 200
3 ravi 2 200
4 shruti 3 100
5 sandy 2 100

i have to find the total income of each. sn1 is an father/mother for resp. Name. If one has their child then the amount of child must be added with the amount of that man. If child also has child ,amount is added to prev. Total and update into that total for the resp. Name.
E.g.
Arif has one child(singh) so arif amt+ singh amoun.now again singh have ravi as child.add ravi's amt to prev. Total.and so on.if anyone doesn't have child the its total will be only single amount.
As such arif having 600,
sing 500,ravi 300,shruti 100,sandy 100.

I am very confuse to solve this.i am using cursor bt no expected o/p.plz help.

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 00:53:27

you need to recursive cte for that

do like


;With Sn_hier
AS
(
SELECT sn,name,sn1,amt,cast(name as varchar(5000)) as path
FROM table
WHERE sn=sn1
UNION ALL
SELECT t.sn,t.name,t.sn1,t.amt,cast(h.path + '/'+ t.name as varchar(5000))
FROM sn_Hier h
INNER JOIN table t
ON t.sn1 = h.sn
)

SELECT LEFT(Path,CHARINDEX('/',Path + '/')-1),SUM(amt) AS tot 
FROM sn_Hier
GROUP BY LEFT(Path,CHARINDEX('/',Path + '/')-1)

OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources