November 7, 2014 at 11:37 am
I have the following Games table:
CREATE TABLE [dbo].[Games](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Lge] [nvarchar](255) NULL,
[GameDate] [date] NULL,
[HomeTeam] [nvarchar](255) NULL,
[Home_Score] [float] NULL,
[AwayTeam] [nvarchar](255) NULL,
[Away_Score] [float] NULL)
with the following data:
INSERT INTO [dbo].[Games2]
([Lge]
,[GameDate]
,[HomeTeam]
,[Home_Score]
,[AwayTeam]
,[Away_Score])
VALUES
('LeagueA'
,'01/02/2014'
,'TeamA'
,5
,'TeamB'
,6)
INSERT INTO [dbo].[Games2]
([Lge]
,[GameDate]
,[HomeTeam]
,[Home_Score]
,[AwayTeam]
,[Away_Score])
VALUES
('LeagueA'
,'02/02/2014'
,'TeamA'
,3
,'TeamB'
,6)
INSERT INTO [dbo].[Games2]
([Lge]
,[GameDate]
,[HomeTeam]
,[Home_Score]
,[AwayTeam]
,[Away_Score])
VALUES
('LeagueA'
,'02/03/2014'
,'TeamB'
,8
,'TeamA'
,6)
INSERT INTO [dbo].[Games2]
([Lge]
,[GameDate]
,[HomeTeam]
,[Home_Score]
,[AwayTeam]
,[Away_Score])
VALUES
('LeagueA'
,'03/02/2014'
,'TeamB'
,7
,'TeamA'
,6)
INSERT INTO [dbo].[Games2]
([Lge]
,[GameDate]
,[HomeTeam]
,[Home_Score]
,[AwayTeam]
,[Away_Score])
VALUES
('LeagueA'
,'04/02/2014'
,'TeamB'
,1
,'TeamA'
,6)
INSERT INTO [dbo].[Games2]
([Lge]
,[GameDate]
,[HomeTeam]
,[Home_Score]
,[AwayTeam]
,[Away_Score])
VALUES
('LeagueA'
,'05/02/2014'
,'TeamC'
,2
,'TeamA'
,5)
INSERT INTO [dbo].[Games2]
([Lge]
,[GameDate]
,[HomeTeam]
,[Home_Score]
,[AwayTeam]
,[Away_Score])
VALUES
('LeagueA'
,'06/02/2014'
,'TeamC'
,3
,'TeamA'
,1)
This gives the standings as:
Team B4 - 1 -
Team C1 - 1 1.5
Team A2 - 5 3
How can I query the data to find the "games behind" at any date?
November 7, 2014 at 8:27 pm
CELKO (11/7/2014)
>> I have the following Games table: <<This kind of problem shows up too often to be real. What school is using this as homework? We need to find out so we can report your for cheating.
This thing has no key, so it is not a table. And this mess has no way to ever have a key! You do not know ISO-11179 naming rules.
In your world, do teams get scores of 3.141592653? No? Then using FLOAT is insane! Give me a team name in any sport that is 250 letters long. If you allow garbage data, you will get it.
Why don't you know the correct DATE format? This is a basic IT standard!
Why did you use IDENTITY in a table? Did you know that only punch card programmers put one column name per line and one value per line with leading commas? We did this to allow use to re-arrange the deck.
You do not know that DDL needs constraints. It is okay to skip some of them in a forum posting, but you did nothing right; not the data types, the display formats keys, constraints, etc.
It looks like the game date is the key. But you never tell us anything.
CREATE TABLE Games
(league_name VARCHAR(10) NOT NULL,
game_date DATE NOT NULL PRIMARY KEY,
CHECK (hometeam_name <> awayteam_name),
hometeam_name VARCHAR(10) NOT NULL,
home_score INTEGER DEFAULT 0 NOT NULL,
awayteam_name VARCHAR(10) NOT NULL,
away_score INTEGER DEFAULT 0 NOT NULL)
>> with the following data: <<
Please learn to use the ANSI/ISO Standard syntax for insertions.
INSERT INTO Games
(league_name, game_date, hometeam_name, home_score, awayteam_name, away_score)
VALUES
('LeagueA', '2014-01-02', 'TeamA', 5, 'TeamB', 6),
('LeagueA', '2014-02-02', 'TeamA', 3, 'TeamB', 6),
('LeagueA', '2014-02-03', 'TeamB', 8, 'TeamA', 6),
('LeagueA', '2014-03-02', 'TeamB', 7, 'TeamA', 6),
('LeagueA', '2014-04-02', 'TeamB', 1, 'TeamA', 6),
('LeagueA', '2014-05-02', 'TeamC', 2, 'TeamA', 5),
('LeagueA', '2014-06-02', 'TeamC', 3, 'TeamA', 1);
Where is your attempt at an answer? What is the rule for ties (I assumed no ties) ? See why I think you are cheating?
SELECT X.team_name, SUM(X.win_cnt) AS wins, SUM(X.loss_cnt) AS losses
FROM (SELECT hometeam_name,
CASE WHEN home_score > away_score
THEN 1 ELSE 0 END,
CASE WHEN home_score < away_score
THEN 1 ELSE 0 END
FROM Games AS G1
UNION ALL
SELECT awayteam_name,
CASE WHEN home_score < away_score
THEN 1 ELSE 0 END,
CASE WHEN home_score > away_score
THEN 1 ELSE 0 END
FROM Games AS G2)
AS X (team_name, win_cnt, loss_cnt)
GROUP BY X.team_name;
TeamA25
TeamB41
TeamC11
Date can't really be a primary key by itself now can it, unless it's the only sports league in history that can't allow 2 different games on the same day.
And the "National Football League" or the [Toronto] "Maple Leafs" don't fit in your table either, guess they'll have to be eliminated!
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".
November 7, 2014 at 11:41 pm
Since you want to criticize my table structure and imply that I am doing this for a school project and am therefore cheating, here is the actual table structure:
USE [DB_75956_cd]
GO
/****** Object: Table [dbo].[Games] Script Date: 11/8/2014 1:36:39 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Games](
[Game_ID] [int] IDENTITY(1,1) NOT NULL,
[TxtYear] [nvarchar](max) NULL,
[NumYear] [nvarchar](max) NULL,
[GameDate] [date] NULL,
[GameYear] [varchar](255) NULL,
[GameMonth] [varchar](255) NULL,
[GameDay] [varchar](255) NULL,
[FBWK] [nvarchar](255) NULL,
[HomeTeam] [int] NULL,
[AwayTeam] [int] NULL,
[OT?] [nvarchar](255) NULL,
[BBOuts] [nvarchar](50) NULL,
[Neutral] [nvarchar](255) NULL,
[Attendance] [nvarchar](50) NULL,
[BBGm#] [nvarchar](50) NULL,
[BBD/N] [nvarchar](255) NULL,
[FWC] [nvarchar](255) NULL,
[FDivision] [nvarchar](255) NULL,
[ConfCGm] [nvarchar](50) NULL,
[NFLCGm] [int] NULL,
[AFLCGm] [int] NULL,
[SBGm] [int] NULL,
[TBGm] [nvarchar](255) NULL,
[WS] [nvarchar](255) NULL,
[Stadium] [int] NULL,
[Sport] [int] NULL,
[OT#] [int] NULL,
[BBInn] [nvarchar](50) NULL,
[HKSO] [int] NULL,
[Outdoor] [int] NULL,
[Forfeit] [nvarchar](5) NULL,
[HKNoRegTie] [int] NULL,
[Playoff] [bit] NULL,
[HConf] [int] NULL,
[HDiv] [int] NULL,
[AConf] [int] NULL,
[ADiv] [int] NULL,
[PO_1-1] [nvarchar](max) NULL,
[PO_1-2] [nvarchar](max) NULL,
[PO_1-3] [nvarchar](max) NULL,
[PO_1-4] [nvarchar](max) NULL,
[PO_1-5] [nvarchar](max) NULL,
[PO_1-6] [nvarchar](max) NULL,
[PO_1-7] [nvarchar](max) NULL,
[PO_2-1] [nvarchar](max) NULL,
[PO_2-2] [nvarchar](max) NULL,
[PO_2-3] [nvarchar](max) NULL,
[PO_2-4] [nvarchar](max) NULL,
[PO_2-5] [nvarchar](max) NULL,
[PO_2-6] [nvarchar](max) NULL,
[PO_2-7] [nvarchar](max) NULL,
[PO_CF-1] [nvarchar](max) NULL,
[PO_CF-2] [nvarchar](max) NULL,
[PO_CF-3] [nvarchar](max) NULL,
[PO_CF-4] [nvarchar](max) NULL,
[PO_CF-5] [nvarchar](max) NULL,
[PO_CF-6] [nvarchar](max) NULL,
[PO_CF-7] [nvarchar](max) NULL,
[PO_F-1] [nvarchar](max) NULL,
[PO_F-2] [nvarchar](max) NULL,
[PO_F-3] [nvarchar](max) NULL,
[PO_F-4] [nvarchar](max) NULL,
[PO_F-5] [nvarchar](max) NULL,
[PO_F-6] [nvarchar](max) NULL,
[PO_F-7] [nvarchar](max) NULL,
[PO_F-8] [nvarchar](max) NULL,
[BASG] [bit] NULL,
[AASG] [bit] NULL,
[Completion] [nvarchar](max) NULL,
[Notes] [nvarchar](max) NULL,
[H_SC] [float] NULL,
[A_SC] [float] NULL,
[RS] [int] NULL,
[PO] [int] NULL,
CONSTRAINT [PK_Games] PRIMARY KEY CLUSTERED
(
[Game_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Games] WITH CHECK ADD CONSTRAINT [FK_Games_AConference] FOREIGN KEY([AConf])
REFERENCES [dbo].[Conferences] ([Conference_ID])
GO
ALTER TABLE [dbo].[Games] CHECK CONSTRAINT [FK_Games_AConference]
GO
ALTER TABLE [dbo].[Games] WITH CHECK ADD CONSTRAINT [FK_Games_ADivisions] FOREIGN KEY([ADiv])
REFERENCES [dbo].[Divisions] ([Division_ID])
GO
ALTER TABLE [dbo].[Games] CHECK CONSTRAINT [FK_Games_ADivisions]
GO
ALTER TABLE [dbo].[Games] WITH CHECK ADD CONSTRAINT [FK_Games_AwayTeams] FOREIGN KEY([AwayTeam])
REFERENCES [dbo].[Teams] ([Team_ID])
GO
ALTER TABLE [dbo].[Games] CHECK CONSTRAINT [FK_Games_AwayTeams]
GO
ALTER TABLE [dbo].[Games] WITH CHECK ADD CONSTRAINT [FK_Games_HConference] FOREIGN KEY([HConf])
REFERENCES [dbo].[Conferences] ([Conference_ID])
GO
ALTER TABLE [dbo].[Games] CHECK CONSTRAINT [FK_Games_HConference]
GO
ALTER TABLE [dbo].[Games] WITH CHECK ADD CONSTRAINT [FK_Games_HDivisions] FOREIGN KEY([HDiv])
REFERENCES [dbo].[Divisions] ([Division_ID])
GO
ALTER TABLE [dbo].[Games] CHECK CONSTRAINT [FK_Games_HDivisions]
GO
ALTER TABLE [dbo].[Games] WITH CHECK ADD CONSTRAINT [FK_Games_HomeTeams] FOREIGN KEY([HomeTeam])
REFERENCES [dbo].[Teams] ([Team_ID])
GO
ALTER TABLE [dbo].[Games] CHECK CONSTRAINT [FK_Games_HomeTeams]
GO
ALTER TABLE [dbo].[Games] WITH CHECK ADD CONSTRAINT [FK_Games_Sports] FOREIGN KEY([Sport])
REFERENCES [dbo].[Sports] ([Sport_ID])
GO
ALTER TABLE [dbo].[Games] CHECK CONSTRAINT [FK_Games_Sports]
GO
ALTER TABLE [dbo].[Games] WITH CHECK ADD CONSTRAINT [FK_Games_Stadiums] FOREIGN KEY([Stadium])
REFERENCES [dbo].[Stadiums] ([StadiumId])
GO
ALTER TABLE [dbo].[Games] CHECK CONSTRAINT [FK_Games_Stadiums]
GO
All I did was create a temporary table to try to get an answer to my query question. But since you don't have the intelligence to answer my question, you think it is better to attack my question instead. Next time just admit you don't know how to figure out the answer and keep you juvenile responses to your self. I don't need 15 year olds trying to figure out how to do things.
November 10, 2014 at 4:04 pm
measterbro (11/7/2014)
Since you want to criticize my table structure and imply that I am doing this for a school project and am therefore cheating, here is the actual table structure:CREATE TABLE [dbo].[Games](
[Game_ID] [int] IDENTITY(1,1) NOT NULL,
[TxtYear] [nvarchar](max) NULL,
[NumYear] [nvarchar](max) NULL,
[GameDate] [date] NULL,
[GameYear] [varchar](255) NULL,
[GameMonth] [varchar](255) NULL,
[GameDay] [varchar](255) NULL,
[FBWK] [nvarchar](255) NULL,
[HomeTeam] [int] NULL,
[AwayTeam] [int] NULL,
[OT?] [nvarchar](255) NULL,
[BBOuts] [nvarchar](50) NULL,
[Neutral] [nvarchar](255) NULL,
[Attendance] [nvarchar](50) NULL,
[BBGm#] [nvarchar](50) NULL,
[BBD/N] [nvarchar](255) NULL,
[FWC] [nvarchar](255) NULL,
[FDivision] [nvarchar](255) NULL,
[ConfCGm] [nvarchar](50) NULL,
[NFLCGm] [int] NULL,
[AFLCGm] [int] NULL,
[SBGm] [int] NULL,
[TBGm] [nvarchar](255) NULL,
[WS] [nvarchar](255) NULL,
[Stadium] [int] NULL,
[Sport] [int] NULL,
[OT#] [int] NULL,
[BBInn] [nvarchar](50) NULL,
[HKSO] [int] NULL,
[Outdoor] [int] NULL,
[Forfeit] [nvarchar](5) NULL,
[HKNoRegTie] [int] NULL,
[Playoff] [bit] NULL,
[HConf] [int] NULL,
[HDiv] [int] NULL,
[AConf] [int] NULL,
[ADiv] [int] NULL,
[PO_1-1] [nvarchar](max) NULL,
[PO_1-2] [nvarchar](max) NULL,
[PO_1-3] [nvarchar](max) NULL,
[PO_1-4] [nvarchar](max) NULL,
[PO_1-5] [nvarchar](max) NULL,
[PO_1-6] [nvarchar](max) NULL,
[PO_1-7] [nvarchar](max) NULL,
[PO_2-1] [nvarchar](max) NULL,
[PO_2-2] [nvarchar](max) NULL,
[PO_2-3] [nvarchar](max) NULL,
[PO_2-4] [nvarchar](max) NULL,
[PO_2-5] [nvarchar](max) NULL,
[PO_2-6] [nvarchar](max) NULL,
[PO_2-7] [nvarchar](max) NULL,
[PO_CF-1] [nvarchar](max) NULL,
[PO_CF-2] [nvarchar](max) NULL,
[PO_CF-3] [nvarchar](max) NULL,
[PO_CF-4] [nvarchar](max) NULL,
[PO_CF-5] [nvarchar](max) NULL,
[PO_CF-6] [nvarchar](max) NULL,
[PO_CF-7] [nvarchar](max) NULL,
[PO_F-1] [nvarchar](max) NULL,
[PO_F-2] [nvarchar](max) NULL,
[PO_F-3] [nvarchar](max) NULL,
[PO_F-4] [nvarchar](max) NULL,
[PO_F-5] [nvarchar](max) NULL,
[PO_F-6] [nvarchar](max) NULL,
[PO_F-7] [nvarchar](max) NULL,
[PO_F-8] [nvarchar](max) NULL,
[BASG] [bit] NULL,
[AASG] [bit] NULL,
[Completion] [nvarchar](max) NULL,
[Notes] [nvarchar](max) NULL,
[H_SC] [float] NULL,
[A_SC] [float] NULL,
[RS] [int] NULL,
[PO] [int] NULL,
CONSTRAINT [PK_Games] PRIMARY KEY CLUSTERED
(
[Game_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Yeah, gotta say, that is a horrible table-like structure. Awful use of (max) everywhere, and resolutely lacking in any normalization whatsoever.
You need to spend a day or two reviewing normal forms, and taking a crack at splitting that single blob of columns into genuine tables.
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".
November 10, 2014 at 9:06 pm
CREATE TABLE [dbo].[Games] (
[Game_ID] INT IDENTITY (1, 1) NOT NULL,
[TxtYear] NVARCHAR (20) NULL,
[NumYear] NVARCHAR (4) NULL,
[GameDate] DATE NULL,
[GameYear] NVARCHAR (4) NULL,
[GameMonth] NVARCHAR (2) NULL,
[GameDay] NVARCHAR (2) NULL,
[FBWK] NVARCHAR (255) NULL,
[HomeTeam] INT NULL,
[AwayTeam] INT NULL,
[OT?] INT NULL,
[BBOuts] INT NULL,
[Neutral] INT NULL,
[Attendance] NVARCHAR (50) NULL,
[BBGm#] INT NULL,
[BBD/N] NVARCHAR (2) NULL,
[FWC] INT NULL,
[FDivision] INT NULL,
[ConfCGm] INT NULL,
[NFLCGm] INT NULL,
[AFLCGm] INT NULL,
[SBGm] INT NULL,
[TBGm] INT NULL,
[WS] INT NULL,
[Stadium] INT NULL,
[Sport] INT NULL,
[OT#] INT NULL,
[HKSO] INT NULL,
[Outdoor] INT NULL,
[Forfeit] NVARCHAR (5) NULL,
[HKNoRegTie] INT NULL,
[Playoff] BIT NULL,
[HConf] INT NULL,
[HDiv] INT NULL,
[AConf] INT NULL,
[ADiv] INT NULL,
[BASG] BIT NULL,
[AASG] BIT NULL,
[Completion] NVARCHAR (MAX) NULL,
[Notes] NVARCHAR (MAX) NULL,
[H_SC] FLOAT (53) NULL,
[A_SC] FLOAT (53) NULL,
[RS] INT NULL,
[PO] INT NULL,
[BBInn] FLOAT NULL);
November 11, 2014 at 2:18 am
Drop GameYear, GameMonth and GameDay if they are derived from GameDate, and calculate them in your queries instead.
What are TxtYear and NumYear? Year (with century) is 4 characters, CHAR(4). Don't use VARCHAR or worse still NVARCHAR for storing 9999.
Don't use NVARCHAR unless you need to. Data takes twice as much storage space as VARCHAR. Look up in Books Online.
Don't use INT if the data better fits TINYINT/SMALLINT. It's not just storage savings, it also provides information about the data - and a simple constraint.
Don't use BLOB storage NVARCHAR(MAX) unless you need to. Measure the data.
Don't use FLOAT unless you know *exactly* what it's for. It's unlikely to be appropriate here and could lead to unexpected results from calculations.
With this structure, you could have rows in your table which are all null except for one column - Game_ID. Decide which columns are required to make a valid data set and change them to NOT NULL.
Your table has over 40 columns, of which only 1 looks to be correctly typed.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 11, 2014 at 8:14 am
CREATE TABLE [dbo].[Games] (
[Game_ID] INT IDENTITY (1, 1) NOT NULL,
[TxtYear] CHAR(9) NULL,
[NumYear] INT NULL,
[GameDate] DATE NOT NULL,
[HomeTeam] INT NOT NULL,
[AwayTeam] INT NOT NULL,
[OT?] TINYINT NULL,
[BBOuts] TINYINT NULL,
[Neutral] TINYINT NULL,
[Attendance] INT NULL,
[BBGm#] TINYINT NULL,
[BBD/N] CHAR(2) NULL,
[FWC] TINYINT NULL,
[FDivision] INT NULL,
[ConfCGm] TINYINT NULL,
[NFLCGm] TINYINT NULL,
[AFLCGm] TINYINT NULL,
[SBGm] TINYINT NULL,
[TBGm] TINYINT NULL,
[WS] TINYINT NULL,
[Stadium] INT NOT NULL,
[Sport] INT NOT NULL,
[OT#] TINYINT NULL,
[HKSO] TINYINT NULL,
[Outdoor] TINYINT NULL,
[Forfeit] CHAR NULL,
[HKNoRegTie] TINYINT NULL,
[Playoff] TINYINT NULL,
[HConf] INT NULL,
[HDiv] INT NULL,
[AConf] INT NULL,
[ADiv] INT NULL,
[BASG] TINYINT NOT NULL,
[AASG] TINYINT NOT NULL,
[Completion] NVARCHAR (255) NULL,
[Notes] NVARCHAR (255) NULL,
[H_SC] INT NULL,
[A_SC] INT NULL,
[RS] TINYINT NOT NULL,
[PO] TINYINT NOT NULL,
[BBInn] DECIMAL(3, 1) NULL,
[FBWK] TINYINT NULL );
November 12, 2014 at 7:58 am
Thank you for all the help with the table structure. However I am still looking for an answer to my original question - how can I query to find the "games behind" a team is on any given date?
November 12, 2014 at 10:37 am
Maybe something like this could help you.
WITH GamesWon AS(
SELECT Lge,
GameDate,
HomeTeam Team,
CASE WHEN Home_Score > Away_Score THEN 1.
WHEN Home_Score = Away_Score THEN .5
ELSE 0 END wins
FROM Games2
UNION ALL
SELECT Lge,
GameDate,
AwayTeam,
CASE WHEN Away_Score > Home_Score THEN 1.
WHEN Away_Score = Home_Score THEN .5
ELSE 0 END
FROM Games2
),
TeamsWins AS(
SELECT Lge,
Team,
SUM(wins) Totalwins
FROM GamesWon
GROUP BY Lge,
Team
)
SELECT Lge,
Team,
Totalwins,
MAX( Totalwins) OVER( PARTITION BY Lge) - Totalwins GamesBehind
FROM TeamsWins
ORDER BY Totalwins DESC
November 12, 2014 at 12:52 pm
Thank you for your reply.
Your query works but it only uses wins for the calculation.
In this example:
WLGB
B410
A253
The "games behind" are found by subtracting Team A wins from Team B plus ((Team A losses from Team B) * -1) = 6, then dividing by 2 to get the total of 3 games behind.
November 12, 2014 at 1:01 pm
measterbro (11/12/2014)
Thank you for your reply.Your query works but it only uses wins for the calculation.
In this example:
WLGB
B410
A253
The "games behind" are found by subtracting Team A wins from Team B plus ((Team A losses from Team B) * -1) = 6, then dividing by 2 to get the total of 3 games behind.
I don't really understand what do you need. :unsure:
November 12, 2014 at 1:32 pm
The results from your query are:
Lge Team TotalwinsGamesBehind
LeagueATeamB4.0 0.0
LeagueATeamA2.0 2.0
LeagueATeamC1.0 3.0
However the "GamesBehind" values don't take into account the losses for each team. If TeamB had one loss and Team A had five losses, they would actually be 3 games behind, not 2.
Lge Team TotalwinsTotal Losses GamesBehind
LeagueATeamB4.0 1 0.0
LeagueATeamC1.0 1 1.5
LeagueATeamA2.0 5 3.0
The value should be found by subtracting a team's total wins from the first place team's wins, then adding the value found by subtracting a team's losses from the first place team's losses multiplied by -1, and then dividing the whole thing by 2.
In this case for TeamA:
2 wins from 4 wins = 2
5 losses from 1 loss * -1 = 4
4 + 2 = 6, divided by 2 = 3 games behind
November 12, 2014 at 2:10 pm
I created this query:
WITH GamesWon AS(
SELECT Lge,
GameDate,
HomeTeam Team,
CASE WHEN Home_Score > Away_Score THEN 1.
WHEN Home_Score = Away_Score THEN .5
ELSE 0 END wins
FROM Games2
UNION ALL
SELECT Lge,
GameDate,
AwayTeam,
CASE WHEN Away_Score > Home_Score THEN 1.
WHEN Away_Score = Home_Score THEN .5
ELSE 0 END
FROM Games2
),
GamesLost AS(
SELECT Lge,
GameDate,
HomeTeam Team,
CASE WHEN Home_Score < Away_Score THEN 1.
WHEN Home_Score = Away_Score THEN .5
ELSE 0 END losses
FROM Games2
UNION ALL
SELECT Lge,
GameDate,
AwayTeam,
CASE WHEN Away_Score < Home_Score THEN 1.
WHEN Away_Score = Home_Score THEN .5
ELSE 0 END
FROM Games2),
TeamsWins AS(
SELECT Lge,
Team,
SUM(wins) Totalwins
FROM GamesWon
GROUP BY Lge,
Team
),
TeamsLosses AS(
SELECT GamesLost.Lge,
GamesLost.Team,
SUM(losses) Totallosses
FROM GamesLost
GROUP BY Lge,
Team
)
SELECT TeamsWins.Lge,
TeamsWins.Team,
Totalwins,
Totallosses,
MAX( Totalwins) OVER( PARTITION BY TeamsWins.Lge) - Totalwins GamesBehind
FROM TeamsWins
inner join
TeamsLosses
ON TeamsWins.Lge=TeamsLosses.Lge
and TeamsWins.Team=TeamsLosses.Team
ORDER BY Totalwins DESC
Which gives me this:
Lge TeamTotalwinsTotallosses GamesBehind
LeagueATeamB4.0 1.0 0.0
LeagueATeamA2.0 5.0 2.0
LeagueATeamC1.0 1.0 3.0
But I don't know how to include the Totallosses in the GamesBehind calculation.
November 12, 2014 at 4:28 pm
I think I have it working now:
WITH GamesWon AS(
SELECT Lge,
GameDate,
HomeTeam Team,
CASE WHEN Home_Score > Away_Score THEN 1.
WHEN Home_Score = Away_Score THEN .5
ELSE 0 END wins
FROM Games2
UNION ALL
SELECT Lge,
GameDate,
AwayTeam,
CASE WHEN Away_Score > Home_Score THEN 1.
WHEN Away_Score = Home_Score THEN .5
ELSE 0 END
FROM Games2
),
GamesLost AS(
SELECT Lge,
GameDate,
HomeTeam Team,
CASE WHEN Home_Score < Away_Score THEN 1.
WHEN Home_Score = Away_Score THEN .5
ELSE 0 END losses
FROM Games2
UNION ALL
SELECT Lge,
GameDate,
AwayTeam,
CASE WHEN Away_Score < Home_Score THEN 1.
WHEN Away_Score = Home_Score THEN .5
ELSE 0 END
FROM Games2),
TeamsWins AS(
SELECT Lge,
Team,
SUM(wins) Totalwins
FROM GamesWon
GROUP BY Lge,
Team
),
TeamsLosses AS(
SELECT GamesLost.Lge,
GamesLost.Team,
SUM(losses) Totallosses
FROM GamesLost
GROUP BY Lge,
Team
)
SELECT TeamsWins.Lge,
TeamsWins.Team,
Totalwins,
Totallosses,
(MAX( Totalwins) OVER( PARTITION BY TeamsWins.Lge) - Totalwins +
(MIN( Totallosses) OVER( PARTITION BY TeamsWins.Lge) - Totallosses) * -1) / 2 GamesBehind
FROM TeamsWins
inner join
TeamsLosses
ON TeamsWins.Lge=TeamsLosses.Lge
and TeamsWins.Team=TeamsLosses.Team
ORDER BY Totalwins DESC
November 14, 2014 at 7:58 am
Joe's back!
Seriously folks, The table structure may not be the best but you rounded on the guy pretty hard.
@joe specifically, I know we have had our run-ins in the past and that you are a fantastic theorist but sometimes we inherit a bad database design or the real-world likelyhood of corrupt data doesn't warrant the mental expenditure of defining complicated constraints. VARCHAR / NVARCHAR - who cares on a small adatabase that is only ever going to be used with Latin CP1 collation unless the number of records is going to be in the tens of millions. Disk space is cheap.
You catch more flies with sugar than vinegar. We all need to start somewhere and patiently showing him the error of his ways rather than yelling at him that he's an idiot will do more good in the end. Joe, I won't buy your books because I don't like your attitude even though I am sure you know what you are talking about.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply