May 20, 2008 at 1:15 pm
Hi,
I have the following table :
Month, Year, TeamNum, Ext, AvgHoldTime,TotSCI, TeamAvgHoldTime
2 2008 1 200 1000 20
2 2008 1 300 2000 40
2 2008 2 200 3000 60
2 2008 2 300 4000 80
I need to update the TeamAvgHoldTime as sum(AvgHoldTime )*sum(TotSCI) for TeamNum 1 and 2.
It should show the following after an update:
Month, Year, TeamNum, Ext, AvgHoldTime,TotSCI, TeamAvgHoldTime
2 2008 1 200 1000 20 50
2 2008 1 300 2000 40 50
2 2008 2 200 3000 30 100
2 2008 2 300 4000 40 100
How do I write T-SQL for updating TeamAvgHoldtime within the same table?
Thanks:)
May 21, 2008 at 12:22 am
Hi
This may helpful to you...
CREATE TABLE #a
(month int,
year int,
teamnum int,
ext int,
avgholdtime int,
totsci int,
totalavgholdtime int )
INSERT INTO #a VALUES (2,2008,1,200,1000,20,0)
INSERT INTO #a VALUES (2,2008,1,300,2000,40,0)
INSERT INTO #a VALUES (2,2008,2,200,3000,60,0)
INSERT INTO #a VALUES (2,2008,3,300,4000,80,0)
UPDATE #a
SET totalavgholdtime = b.totalavgholdtime
FROM#a a
INNER JOIN (SELECT teamnum,(sum(avgholdtime) / sum(totsci)) as totalavgholdtime
FROM #a
GROUP BY teamnum ) b
ON a.teamnum = b.teamnum
SELECT * FROM #a
Thanks
jaypee.s
May 21, 2008 at 8:28 am
Thanks it works!:)
May 23, 2008 at 3:42 pm
Hi,
Now with a twist , I want to do same as before but update the TeamAverageHold time as a sum of Teams 1 and 2 :
Update the TeamAvgHoldTime as sum(AvgHoldTime )*sum(TotSCI) for TeamNum 1 and 2 but as a Total for Team 1 and 2 i.e. 10000/200 = 50
I have the following table :
Month, Year, TeamNum, Ext, AvgHoldTime,TotSCI, TeamAvgHoldTime
2 2008 1 200 1000 20
2 2008 1 300 2000 40
2 2008 2 200 3000 60
2 2008 2 300 4000 80
It should show the following after an update:
Month, Year, TeamNum, Ext, AvgHoldTime,TotSCI, TeamAvgHoldTime
2 2008 1 200 1000 20 50
2 2008 1 300 2000 40 50
2 2008 2 200 3000 60 50
2 2008 2 300 4000 80 50
How do I write T-SQL for updating TeamAvgHoldtime within the same table?
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply