Stored Procedure Help

[upillai4444]

Hey all,

 

I'm trying to figure out a score/ranking procedure from a SQL DB.  I am using MS Visual Studio.

I have the following tables[Attributes]:

Team[Name, Color]

Week[week_id, Date]

Game[week_id, home_team_name, away_team_name, home_team_score, away_team_score, reffing_team_name]

I want to run a stored procedure that will show all the teams, their wins/losses/ties, points, and ordered by rank which is based on points.  Points are calculated by wins +2, ties +1, losses +0.  The "Game" table only has scores from week_id 1-3.  Below is what I have so far, but it returns the same numbers for wins, ties, and losses.  And I haven't even started to figure out the ranking and points part yet, cause I can't even get the correct totals for W/T/L.  Any help would be greatly appreciated. 

AND FYI, I'm a noob to all this.

ALTER PROCEDURE dbo.GenerateStandingsReport
AS
BEGIN
SET NOCOUNT ON
SELECT 	reffing_team_name "Team", 
COUNT(CASE 
WHEN (home_team_name = reffing_team_name AND home_team_score > away_team_score) 
OR (away_team_name = reffing_team_name AND away_team_score > home_team_score) 
THEN 1 
ELSE 0
END) "Wins", /*--calculate team’s wins*/
COUNT(CASE
WHEN (home_team_name = reffing_team_name AND home_team_score = away_team_score) 
OR (away_team_name = reffing_team_name AND away_team_score = home_team_score) 
THEN 1	
ELSE 0 	
END) "Ties", /*--calculate team’s ties*/
COUNT(CASE 
WHEN (home_team_name = reffing_team_name AND home_team_score < away_team_score) 
OR (away_team_name = reffing_team_name AND away_team_score < home_team_score) 
THEN 1 
ELSE 0 
END) "Losses" /*--calculate team’s losses*/
FROM game, week
WHERE game.week_id = week.week_id
AND week.date < current_timestamp
GROUP BY reffing_team_name
ORDER BY wins DESC, ties DESC, losses ASC
END