Streaks - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Streaks

Author

Topic

warberg
Starting Member

3 Posts

Posted - 2008-11-26 : 04:51:39

I'm trying to work in http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data , the output is exactly what I'm trying to achieve.

I'm trying to work it around two tables, res and pgam

res:
kampid (id for each match)
hjemmemaal (home score)
udemaal (away score)

pgram:
kampid (id for each match corresponding to res.kampid)
dato (date)

I find wins:

SELECT SUM(CASE WHEN res.hjemmemaal > res.udemaal THEN 1 ELSE 0 END) as Wins

FROM pgram AS p
INNER JOIN res AS res
ON p.kampid = res.kampid

Now, challenge 1; I'm trying to work in Jeffs RunGroup:

SELECT GameDate,
Result,
(SELECT COUNT(*)
FROM GameResults G
WHERE G.Result <> GR.Result
AND G.GameDate <= GR.GameDate) as RunGroup
FROM GameResults GR

combining my two tabels pgram and res

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-26 : 04:56:01

Are you using SQL Server 2005 and later, or SQL Server 2000 or earlier?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-26 : 05:44:49

And maybe more important, how can you tell wether or not a team was home team or away team for a game (kamp).

E 12°55'05.63"
N 56°04'39.26"

warberg
Starting Member

3 Posts

Posted - 2008-11-26 : 06:25:55

I'm using SQL Server 2005

pgram:
kampid (id for each match corresponding to res.kampid)
dato (date)
hjemme (id for home team)
ude (id for away team)

Id for teams corresponding to a team-table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-26 : 07:20:10

[code]-- Prepare sample data
SET NOCOUNT ON

DECLARE @GameResults TABLE
(
gameID INT,
homeScore INT,
awayScore INT
)

INSERT @GameResults
SELECT 1, 2, 1 UNION ALL
SELECT 2, 4, 1 UNION ALL
SELECT 3, 4, 3

DECLARE @Program TABLE
(
gameID INT,
gameDate DATETIME,
homeID INT,
awayID INT
)

INSERT @Program
SELECT 1, '2008-05-12', 101, 102 UNION ALL
SELECT 2, '2008-05-20', 106, 101 UNION ALL
SELECT 3, '2008-05-14', 107, 101

-- Prepare staging data
DECLARE @Stage TABLE
(
teamID INT,
gameDate DATETIME,
outcome CHAR(3),
streak INT,
PRIMARY KEY CLUSTERED
(
teamID,
gameDate
)
)

INSERT @Stage
(
teamID,
gameDate,
outcome
)
SELECT t.teamID,
t.gameDate,
CASE
WHEN t.homeScore = t.awayScore THEN 'tie'
WHEN t.theRoute = 'homeID' AND t.homeScore > t.awayScore THEN 'win'
WHEN t.theRoute = 'homeID' THEN 'los'
WHEN t.theRoute = 'awayID' AND t.homeScore < t.awayScore THEN 'win'
WHEN t.theRoute = 'awayID' THEN 'los'
END AS outcome
FROM (
SELECT p.homeID,
gr.homeScore,
p.gameDate,
p.awayID,
gr.awayScore
FROM @GameResults AS gr
INNER JOIN @Program AS p ON p.gameID = gr.gameID
) AS g
UNPIVOT (
teamID
FOR theRoute IN (g.homeID, g.awayID)
) AS t
ORDER BY teamID,
gameDate

-- Fix the streak numbering
DECLARE @teamID INT,
@outcome CHAR(3),
@streak INT

SELECT TOP 1 @teamID = teamID,
@outcome = outcome,
@streak = 1
FROM @Stage
ORDER BY teamID,
gameDate

UPDATE @Stage
SET @streak = streak = CASE
WHEN teamID = @teamID AND @outcome = outcome THEN @streak
ELSE @streak + 1
END,
@teamID = teamID,
@outcome = outcome

SELECT teamID,
outcome,
COUNT(*) AS streaks,
MIN(gameDate) AS fromDate,
MAX(gameDate) AS toDate
FROM @Stage
GROUP BY teamID,
outcome
ORDER BY teamID,
outcome[/code]

E 12°55'05.63"
N 56°04'39.26"

warberg
Starting Member

3 Posts

Posted - 2008-11-26 : 14:46:00

Thank you very much for your help.

In a few weeks I have to convert all my code to mySQL, any idea how this would look?
Or links to the differences in general?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources