November 5, 2014 at 12:12 pm
I have a Games table in a SQL Server 2012 database with game results from our league, including home and away scores, dates, and teams. How can I query to list streaks - that is, wins or losses of 2 more games in a row throughout a season?
November 5, 2014 at 12:22 pm
That all depends on how your data is structured.
People here will take the time to provide you with a working query, if you are prepared to take the time to write out your question in the way described in the link in my signature.
In a nutshell, we need sample DDL, sample data, desired output (based on the sample data provided), in the form of SSMS-friendly consumable T-SQL statements please.
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
November 5, 2014 at 1:10 pm
The 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)
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'
,'TeamB'
,2
,'TeamA'
,5)
INSERT INTO [dbo].[Games2]
([Lge]
,[GameDate]
,[HomeTeam]
,[Home_Score]
,[AwayTeam]
,[Away_Score])
VALUES
('LeagueA'
,'06/02/2014'
,'TeamB'
,3
,'TeamA'
,5)
Sample Data:
IDLge GameDate HomeTeamHome_ScoreAwayTeam Away_Score
1LeagueA2014-01-02TeamA5 TeamB 6
2LeagueA2014-02-02TeamA3 TeamB 6
3LeagueA2014-02-03TeamB8 TeamA 6
4LeagueA2014-03-02TeamB7 TeamA 6
5LeagueA2014-04-02TeamB1 TeamA 6
6LeagueA2014-05-02TeamB2 TeamA 5
7LeagueA2014-06-02TeamB3 TeamA 5
I would like to create a query to show that TeamA had a 4 game losing streak from 02/02/2014 to 03/02/2014, and a 3 game winning streak from 04/02/2014 to 06/02/2014.
November 5, 2014 at 1:40 pm
Do you need something like this?
DECLARE @Team nvarchar(255) = 'TeamA';
WITH Winners AS(
SELECT *,
CASE WHEN Home_Score > Away_Score THEN HomeTeam
WHEN Home_Score < Away_Score THEN AwayTeam
ELSE 'Tie' END winner
FROM Games
),
Streaks AS(
SELECT GameDate,
CASE WHEN winner = @Team THEN 'W'
WHEN winner = 'Tie' THEN 'T'
ELSE 'L' END streakdesc,
ROW_NUMBER() OVER( ORDER BY GameDate) -
ROW_NUMBER() OVER( PARTITION BY winner ORDER BY GameDate) streak
FROM Winners
)
SELECT streakdesc,
MIN(GameDate) StreakStart,
MAX(GameDate) StreakEnd,
COUNT(*) Games
FROM Streaks
GROUP BY streakdesc, streak
ORDER BY StreakStart
You can find more information on this kind of queries if you search for gaps and islands. Here's an article I like: https://www.simple-talk.com/sql/t-sql-programming/the-sql-of-gaps-and-islands-in-sequences/
Or this one: http://blog.waynesheffield.com/wayne/archive/2012/04/sql-server-2012-performance-test-gap-detection/
November 5, 2014 at 2:28 pm
Thank you very much
November 5, 2014 at 2:48 pm
Now, the question would be, do you understand it?
Remember that you'll be the one supporting it. Feel free to ask any questions that you have.
November 5, 2014 at 2:53 pm
I will have to look into it. I understand everything except the "ROW_NUMBER() OVER( ORDER BY GameDate) -
ROW_NUMBER() OVER( PARTITION BY winner ORDER BY GameDate) streak" section. However I have tested it and it does do what I need.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply