x

Best way to update data in a table

I have the following data in a sql server 2005 table:

team    score  week    win    loss
team1   100    1        
team2   120    1        
team3   130    1        
team4   80     1        
team5   90     1

I want to update the win & loss columns so that each team has results compared to all other scores that week, so team1 would show 2 wins, 2 losses, team2 would have 3 wins, 1 loss, etc. Ever week the scores would be inputted and I want the wins and losses calculated.

How do I go about this? It is a small database.

more ▼

asked Dec 04, 2009 at 07:20 PM in Default

avatar image

user-697
21 2 2 4

I think you need some more information about what will be entered. How do you go from 0-0 to 2-2?

Dec 04, 2009 at 09:41 PM Steve Jones - Editor ♦♦

also, how do you change weeks? Update the column or add a new row?

Dec 04, 2009 at 09:42 PM Steve Jones - Editor ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

(I believe this will do what you want as long as there are no ties.)
I was originally going to use RANK() but decided against it. After seeing Jonathan's answer, I reconsidered the use of RANK(). I have edited my answer back to using RANK() and added the ability to include ties. If ties are not possible, you can just remove that column from the update.

IF OBJECT_ID('dbo.UpdateWeekWinsAndLosses') is not null DROP PROCEDURE dbo.UpdateWeekWinsAndLosses GO CREATE PROCEDURE dbo.UpdateWeekWinsAndLosses @week smallint AS BEGIN SET NOCOUNT ON;

 WITH WinLoss (team, win, loss, tie)
 AS
 (
     SELECT tr.team,
         RANK() OVER (PARTITION BY tr.week ORDER BY tr.score ASC) - 1 AS win,
         RANK() OVER (PARTITION BY tr.week ORDER BY tr.score DESC) - 1 AS loss,
         COUNT(*) OVER (PARTITION BY tr.week) -
             (RANK() OVER (PARTITION BY tr.week ORDER BY tr.score ASC) - 1) -
             (RANK() OVER (PARTITION BY tr.week ORDER BY tr.score DESC) - 1) - 1 AS tie
     FROM TeamRecords tr
     WHERE tr.week = @week
 )
 UPDATE TeamRecords
 SET win = wl.win, loss = wl.loss, tie = wl.tie
 FROM TeamRecords tr
     INNER JOIN WinLoss wl
     ON wl.team = tr.team
 WHERE week = @week

END GO

Each week, run the procedure for that week #.

EXEC dbo.UpdateWeekWinsAndLosses 1
more ▼

answered Dec 05, 2009 at 04:59 AM

avatar image

Tom Staab ♦
14.5k 7 14 18

(comments are locked)
10|1200 characters needed characters left

A pair of reverse sorted RANK() functions should do the trick:

declare @scores table (team int, score int, week int, win int, loss int) insert into @scores values (1, 100, 1, null, null) insert into @scores values (2, 120, 1, null, null) insert into @scores values (3, 130, 1, null, null) insert into @scores values (4, 80, 1, null, null) insert into @scores values (5, 90, 1, null, null) insert into @scores values (1, 80, 2, null, null) insert into @scores values (2, 110, 2, null, null) insert into @scores values (3, 90, 2, null, null) insert into @scores values (4, 120, 2, null, null) insert into @scores values (5, 100, 2, null, null)

select team, score, week, rank() over (partition by week order by score asc) - 1 as win, rank() over (partition by week order by score desc) - 1 as loss from @scores order by week, team

more ▼

answered Dec 06, 2009 at 01:10 AM

avatar image

Jonathan Kehayias
293 1 3 6

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2018
x162

asked: Dec 04, 2009 at 07:20 PM

Seen: 2225 times

Last Updated: Dec 04, 2009 at 07:33 PM

Copyright 2016 Redgate Software. Privacy Policy