Automation - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Automation

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-01-27 : 08:19:48

Steven writes "I need to display the standings for baseball teams in order from wins, ties, losses. When a game is complete a table called GameOutcomes is updated with the score. The following fields are updated (Team1, Team2, Team1Score, Team2Score). I also have a table called ActiveTeams. The fields in this table are (Team)

I figured out a way to do this by using a cursor to loop through the ActiveTeams table, Fetch the Team, Then do a select statement to determine the wins, losses, and ties:

Declare @Team nvarchar(50)
Declare Cur_Tally Cursor
For Select Team From ActiveTeams

Open Cur_Tally
FETCH Cur_Tally INTO @Team

While @@Fetch_Status = 0
Begin
SELECT @Team,
(
SELECT Count(*) AS Wins From GameOutcomes
WHERE (Team1 = @Team AND Team1Score > Team2Score)
OR (Team2 = @Team AND Team2Score > Team1Score)
AND Division = 'Prep' AND Season = YEAR(GETDATE())
) AS Win,
(
SELECT Count(*) AS Loss From GameOutcomes
WHERE (Team1 = @Team AND Team1Score < Team2Score)
OR (Team2 = @Team AND Team2Score < Team1Score)
AND Division = 'Prep' AND Season = YEAR(GETDATE())
) AS Loss,
(
SELECT Count(*) AS Tie From GameOutcomes
WHERE ((Team1 = @Team OR Team2 = @Team) AND Team1Score = Team2Score) AND Division = 'Prep' AND Season = YEAR(GETDATE())
) AS Tie
FETCH Cur_Tally INTO @Team
End
Close Cur_Tally
Deallocate Cur_Tally

This works except it will not order the standings correctly. Another thing I was gonna try was to add columns Wins, Losses, Tie to the ActiveTeams Table and maybe use a sql query for a default value but the sql query will need to use the field Team for that row. Not sure I explained this well. Any suggestions that you may have will be helpful.

Thanks,
Steve"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-27 : 14:50:55

Try this to order your results by winning Pct:

SELECT Team, SUM(Wins) as Wins, SUM(Losses) as Losses, SUM(Ties) as Ties, SUM(Wins) / SUM(Games) as WinningPct
FROM
(
SELECT
CASE WHEN T = 1 THEN Team1 ELSE Team2 END as Team,
CASE WHEN T * Team1Score > T * Team2Score THEN 1 ELSE 0 END As Wins,
CASE WHEN T * Team1Score < T * Team2Score THEN 1 ELSE 0 END as Losses,
CASE WHEN Team1Score = Team2Score THEN 1 ELSE 0 END as Ties,
1 as Games
FROM
GameOutcomes
CROSS JOIN (SELECT 1 as T UNION ALL SELECT -1 as T) A
) B
GROUP BY Teams
ORDER BY Sum(Wins) / Sum(Games)

Might be a little tricky, but look through it and you'll see how it works. The tricky part is the CROSS JOIN with values of -1 and 1, which affect the score comparisions, and also which allows us to traverse the table twice and add up wins, losses and ties for each team.

- Jeff

Edited by - jsmith8858 on 01/27/2003 15:08:23

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources