June 8, 2022 at 12:10 am
OK - very new to this so I apologize if this is obvious but I can't figure this one out. I'm looking to create league standings from a table with all the game data (table called games) and want the team names from the table with the team name and ID data (table called teams). I want it to look something like this in order from best to worst in standings so:
Team Name, Wins, Losses, Win%, Points_For, Points_Against
TEAM 1, 15, 5, 75%, 1500, 1200
TEAM 2, 14, 6, 70%, 1475, 1300
TEAM 3, 12, 8, 60%, 1350, 1300
TEAM 4, 10,10, 50%, 1200,1200 ETC....
Game Table is: Game_ID, Game_Date, Home_Team, Away_Team, Home_Score, Away_Score
Team Table is: Team_ID, Team_Name
The other thing I want to do is show this for different years so need to be able to group by year(Game_Date) as Season for each season 2015, 2016, 2017, 2018, 2019 etc... So a different standings table for each year.
Any help with this is greatly appreciated.
Thank you
June 8, 2022 at 9:36 am
Hello and welcome to the forum.
It would be very helpful if you could provide some test data for the Game and Team tables, along with your desired results based on that test data (this can be as simple as a screenshot from Excel). I'd suggest using three or four teams with games against each other for this sample.
Please see the following thread for details on how to post your sample data:
The great thing about doing it like this is that people here can cut and paste your sample code straight into SSMS and provide working code for you to use.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 8, 2022 at 1:38 pm
To generate table schema, right click your table(s) then --> Script Table as --> Create to -->
June 8, 2022 at 3:23 pm
This might give you some helpful ideas. It also contains minimum sample data to show you how data can be posted. I used a Season table to determine the Season year -- each Season and its start and end dates are loaded into a table, then you can determine the Season by looking up the Game_Date.
DROP TABLE IF EXISTS #game;
CREATE TABLE #game (
Game_ID int NOT NULL,
Game_Date date NOT NULL,
Home_Team int NULL,
Away_Team int NULL,
Home_Score int NULL,
Away_Score int NULL
);
DROP TABLE IF EXISTS #team;
CREATE TABLE #team (
Team_ID int NOT NULL,
Team_Name varchar(100) NOT NULL,
First_Game_Date date NULL
);
DROP TABLE IF EXISTS #season;
CREATE TABLE #season (
Season smallint NOT NULL,
Start_Date date NOT NULL,
End_Date date NOT NULL
);
INSERT INTO #team ( Team_ID, Team_Name ) VALUES
(1, 'Team 1'), (2, 'Team 2'), (3, 'Team 3'), (4, 'Team 4')
INSERT INTO #season VALUES
(2022, '20220301', '20220831');
INSERT INTO #game VALUES
( 1, '20220501', 1, 2, 3, 5 ),
( 2, '20220501', 3, 4, 6, 2 ),
( 3, '20220503', 2, 3, 7, 4 ),
( 4, '20220503', 4, 1, 4, 1 );
DECLARE @Season smallint
SET @Season = 2022
SELECT
Team_ID,
SUM(Win) AS Wins, SUM(CASE WHEN Win = 0 THEN 1 ELSE 0 END) AS Losses,
CAST(SUM(Win) * 100.0 / ISNULL(COUNT(*), 0) AS decimal(4, 1)) AS [Win%],
SUM(Points_For) AS Points_For, SUM(Points_Against) AS Points_Against
FROM #game g
INNER JOIN #season s ON s.Season = @Season AND g.Game_Date BETWEEN s.Start_Date AND s.End_Date
CROSS APPLY (
SELECT g.Home_Team AS Team_ID, CASE WHEN g.Home_Score - g.Away_Score > 0 THEN 1 ELSE 0 END AS Win, g.Home_Score AS Points_For, g.Away_Score AS Points_Against
UNION ALL
SELECT g.Away_Team, CASE WHEN g.Away_Score - g.Home_Score > 0 THEN 1 ELSE 0 END, g.Away_Score, g.Home_Score AS Points_Against
) AS ca1
GROUP BY Team_ID
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 8, 2022 at 3:54 pm
OK lets see if I can do this correctly. Below are the tables and some table data. Also, Mr Pletcher, thank you for posting, my data is structured a bit differently so maybe I need to rethink how I have structured my tables. I don't have a binary win_loss in the game data which you will see. I put the teamId of the home team and the score and the teamId of the away team and the score and then need to calculate who won the game - there are never ties.
GAMES TABLE
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Games]') AND type in (N'U'))
DROP TABLE [dbo].[Games]
CREATE TABLE [dbo].[Games](
[pk_game_ID] [bigint] NOT NULL,
[game_date] [date] NOT NULL,
[Home_Id] [int] NOT NULL,
[Home_Score] [tinyint] NOT NULL,
[Opp_Id] [int] NOT NULL,
[Opp_Score] [tinyint] NOT NULL,
CONSTRAINT [PK_tbl_Games] PRIMARY KEY CLUSTERED
(
[pk_game_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
TEAMS TABLE
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Teams]') AND type in (N'U'))
DROP TABLE [dbo].[Teams]
GO
/****** Object: Table [dbo].[Teams] Script Date: 6/8/2022 8:40:42 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Teams](
[team_id] [int] NOT NULL,
[team_name] [nchar](50) NOT NULL,
[team_nickname] [nchar](10) NOT NULL,
[team_conference] [nchar](30) NOT NULL,
CONSTRAINT [PK_tbl_Teams] PRIMARY KEY CLUSTERED
(
[team_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
DATA FOR THE GAMES TABLE
SELECT '1', '2016-09-21', '101', '3', '102', '1' UNION ALL
SELECT '2', '2016-09-21', '103', '2', '95', '3' UNION ALL
SELECT '3', '2016-09-21', '104', '0', '94', '3' UNION ALL
SELECT '4', '2016-09-23', '106', '0', '98', '3' UNION ALL
SELECT '5', '2016-09-23', '92', '3', '96', '0' UNION ALL
SELECT '6', '2016-09-24', '97', '3', '105', '0' UNION ALL
SELECT '7', '2016-09-24', '92', '2', '98', '3' UNION ALL
SELECT '8', '2016-09-24', '103', '3', '101', '0' UNION ALL
SELECT '9', '2016-09-24', '99', '3', '93', '0'
DATA FOR THE TEAMS TABLE
SELECT '92', 'Michigan State University', 'MSU', 'Big Ten' UNION ALL
SELECT '93', 'Ohio State University', 'OSU', 'Big Ten' UNION ALL
SELECT '94', 'Pennsylvania State University', 'PSU', 'Big Ten' UNION ALL
SELECT '95', 'University of Illinois Urbana-Champaign', 'ILL', 'Big Ten' UNION ALL
SELECT '96', 'University of Iowa', 'IOWA', 'Big Ten' UNION ALL
SELECT '97', 'University of Minnesota', 'UMN', 'Big Ten' UNION ALL
SELECT '98', 'University of Nebraska-Lincoln', 'UNL', 'Big Ten' UNION ALL
SELECT '99', 'University of Wisconsin-Madison ', 'WISC', 'Big Ten' UNION ALL
SELECT '101', 'Indiana University Bloomington', 'IU ', 'Big Ten' UNION ALL
SELECT '102', 'Northwestern University', 'NU ', 'Big Ten' UNION ALL
SELECT '103', 'Purdue University ', 'PURD', 'Big Ten' UNION ALL
SELECT '104', 'Rutgers University', 'RU ', 'Big Ten' UNION ALL
SELECT '105', 'University of Maryland ', 'UMD', 'Big Ten' UNION ALL
I Hope I did this right - thank you all for your help and advice....
June 8, 2022 at 4:48 pm
so maybe I need to rethink how I have structured my tables. I don't have a binary win_loss in the game data which you will see. I put the teamId of the home team and the score and the teamId of the away team and the score and then need to calculate who won the game - there are never ties.
I do exactly the same thing. The "Win" column you see is calculated in the query itself, it is not stored in the data. Take a closer look at the CROSS APPLY code.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 8, 2022 at 7:51 pm
OK tried out the code but I'm getting an error saying
Message 8115, Level 16, State 2, Line 4
Arithmetic overflow error converting expression to data type tinyint.
I don't understand as everything is just basic match. Also, I tried removing the CAST statement and still get the same error and I removed everything in the first select statement except the Sum(Win) as Wins statement and still get the error so it must be somewhere in the cross apply section but I don't see what the issue is.
Thanks
June 8, 2022 at 7:56 pm
Got it to work - just changed it from subtracting the score to making it a greater than statement so
m.Home_Score < m.Away_Score Then 1 else 0....
Thank you for your help - amazing!
May 14, 2023 at 9:24 pm
This was removed by the editor as SPAM
May 14, 2023 at 10:37 pm
Hi there! It sounds like you want to create league standings based on game data and team data in different seasons. Here's what you can do:
First, you'll need to join the two tables together using the team ID. You can do this using a SQL query like this:
vbnet Copy code SELECT games.Home_Team, games.Away_Team, games.Home_Score, games.Away_Score, teams.Team_Name FROM games JOIN teams ON games.Home_Team = teams.Team_ID This will give you a table with the team names included.
Next, you'll want to calculate the wins, losses, and win percentage for each team in each season. You can do this using a combination of GROUP BY and CASE statements. Here's an example query:
sql Copy code SELECT teams.Team_Name, YEAR(games.Game_Date) as Season, SUM(CASE WHEN games.Home_Team = teams.Team_ID AND games.Home_Score > games.Away_Score THEN 1 WHEN games.Away_Team = teams.Team_ID AND games.Away_Score > games.Home_Score THEN 1 ELSE 0 END) as Wins, SUM(CASE WHEN games.Home_Team = teams.Team_ID AND games.Home_Score < games.Away_Score THEN 1 WHEN games.Away_Team = teams.Team_ID AND games.Away_Score < games.Home_Score THEN 1 ELSE 0 END) as Losses, ROUND(SUM(CASE WHEN games.Home_Team = teams.Team_ID OR games.Away_Team = teams.Team_ID THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) as Win_Percentage, SUM(CASE WHEN games.Home_Team = teams.Team_ID THEN games.Home_Score ELSE games.Away_Score END) as Points_For, SUM(CASE WHEN games.Home_Team = teams.Team_ID THEN games.Away_Score ELSE games.Home_Score END) as Points_Against FROM games JOIN teams ON games.Home_Team = teams.Team_ID OR games.Away_Team = teams.Team_ID GROUP BY teams.Team_Name, YEAR(games.Game_Date) ORDER BY Win_Percentage DESC This query will give you the team name, season, wins, losses, win percentage, points for, and points against for each team in each season. You can modify the query to filter by specific seasons or add additional columns as needed.
Hope this helps! Let me know if you have any further questions.
I'm pretty sure that, based on this poster's previous history of spam and the fact that this response was generated by one of the multiple forms of AI and the fact that this poster is responding to a nearly year old post were the OP has announce they 'got it to work", this is a prelude to spam.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 8, 2023 at 7:49 am
This was removed by the editor as SPAM
January 25, 2024 at 3:38 am
This was removed by the editor as SPAM
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply