update column value based on other rows

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 column value based on other rows

Author

Topic

Vikas_3r
Starting Member

5 Posts

Posted - 2012-04-21 : 00:35:57

I have following table

sno name pid amount total
1 Arif 0 100 null
2 Raj 1 200 null
3 Ramesh 2 100 null
4 Pooja 2 100 null
5 Swati 3 200 null
6 King 4 100 null

I want total of each person such that it gives total sum of amount of its descendants.
For ex. for RAJ total will be : total= amount of(raj+ramesh+pooja+swati+king)
for SWATI :Total=amount of swati only.

Vikas_3r
Starting Member

5 Posts

Posted - 2012-04-21 : 03:32:42

Please give me the best solution for this.......

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-21 : 12:38:33

[code]
;With CTE()
AS
(
SELECT t.sno,t.name,t.pid,t.amount,CAST(t.name as varchar(8000)) AS Path
FROM table t
LEFT JOIN table t1
ON t.pid = t1.sno
WHERE t1.sno IS NULL
UNION ALL
SELECT t.sno,t.name,t.pid,t.amount, CAST(c.path + '/' + t.name as varchar(8000))
FROM CTE c
INNER JOIN table t
ON t.pid = c.sno
)

SELECT LEFT(Path,CHARINDEX('/',Path + '/')-1) AS Root,SUM(amount) AS Total
FROM CTE
GROUP BY LEFT(Path,CHARINDEX('/',Path + '/')-1)

OPTION (MAXRECURSION 0)
[/code]

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

Vikas_3r
Starting Member

5 Posts

Posted - 2012-04-23 : 03:06:06

Its O.K. But how can i achieve this for all the rows? Above is just showing one Row (Arif).... Is it possible to do it without path?.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 12:06:24

so how should be output?

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

Vikas_3r
Starting Member

5 Posts

Posted - 2012-04-24 : 00:58:56

The output should like this:
1 Arif 800(Arif+Raj+Ramesh+Pooja+swati+king)
2 Raj 700 (Raj+Ramesh+Pooja+swati+king)
3 Ramesh 300 (Ramesh+swati)
4 Pooja 200 (pooja+king)
5 Swati 200 (swati itself bcoz its sno is not in pid colm)
6 King 100 (king's amount itself)

And also this total must be updated to table itself........

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-24 : 01:12:32

[code]
;With CTE()
AS
(
SELECT t.sno,t.name,t.pid,t.amount,CAST(t.name as varchar(8000)) AS Path
FROM table t
LEFT JOIN table t1
ON t.pid = t1.sno
WHERE t1.sno IS NULL
UNION ALL
SELECT t.sno,t.name,t.pid,t.amount, CAST(c.path + '/' + t.name as varchar(8000))
FROM CTE c
INNER JOIN table t
ON t.pid = c.sno
)

SELECT *
FROM table t
CROSS APPLY (SELECT SUM(Amount) AS Total
FROM CTE
WHERE '/' + Path + '/' LIKE '%/' + t.name + '/%'
)c

OPTION (MAXRECURSION 0)
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources