Update Total Hold Time

• KS-321165

  Default port

  Points: 1442

  More actions

  May 23, 2008 at 3:48 pm

  #186990

  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 want to update the TeamAverageHold time as a sum of Teams 1 and 2 :

  Update the TeamAvgHoldTime for Team 1 and Team 2 as a Total. sum(AvgHoldTime )/sum(TotSCI) for both TeamNum 1 and 2 as a Total i.e. 10000/200 = 50

  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

• ksullivan

  SSCrazy

  Points: 2346

  More actions

  May 24, 2008 at 8:44 am

  #819995

  declare @TeamAvgHoldTime int ---<<< or decimal as you need it

  select @TeamAvgHoldTime = sum(AvgHoldTime )/sum(TotSCI)

  from #tmp

  select @TeamAvgHoldTime

  update t set TeamAvgHoldTime = @TeamAvgHoldTime

  from #tmp t