Best way to update data in a table

Question

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.

Answer 1

(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

Answer 2

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

Best way to update data in a table

Follow this question

Related Questions