Copilot 🤖

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