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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
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!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply