Winning Losing 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

SQL Server 2000 Forums

Transact-SQL (2000)

Winning Losing Streaks

Author

Topic

jwize
Starting Member

7 Posts

Posted - 2006-12-28 : 01:50:08

-- These are going to be out parameters.
DECLARE @win_streak AS INT;
DECLARE @lose_streak AS INT;

WITH PlayerGames AS
(
SELECT win, [date]
FROM dbo.Games
WHERE player = 'jwize'
),
PlayerGames2 AS
(
SELECT [date], win, (
SELECT COUNT(*)
FROM PlayerGames AS G
WHERE G.win <> GR.win
AND G.[date] <= GR.[date])as RunGroup
FROM PlayerGames AS GR
),
Streaks AS
(
SELECT win, Min([date]) as StartDate,
MAX([date]) as EndDate,
COUNT(*) as Games
FROM PlayerGames2
GROUP BY win, RunGroup
),
WinStreak AS
(
SELECT TOP(1) Games AS win_streak,
ROW_NUMBER() OVER(ORDER BY Games) as rn
FROM Streaks
WHERE win = 1
ORDER BY Games DESC
)
,
LoseStreak AS
(
SELECT TOP(1) Games AS lose_streak ,
ROW_NUMBER() OVER(ORDER BY Games) as rn
FROM Streaks
WHERE win = 0
ORDER BY Games DESC
)
SELECT rn,win_streak,
lose_streak
FROM WinStreak as W JOIN LoseStreak as L
ON W.rn = L.rn

PRINT @win_streak;
PRINT @lose_streak;

Why doesn't the join return anything on the Win_Streak LoseStreak At the end? When I remove the JOIN and query either of the tables I get

WinStreak
rn win
1 3(num of wins)

LoseStreak
rn win
1 4

I should be able to join these on rn but it doesn't work.

Thanks for any advice.

Jaime

jwize
Starting Member

7 Posts

Posted - 2006-12-28 : 02:17:46

Silly me. I didnt' need the row_number after all, which by the way was returning 2 for the LoseStreak and one for the WinStreak table.

changed ROW_NUMBER() OVER(ORDER BY Games) as rn

to

1 AS matching_column

for both the WinStreak and LoseStreak tables.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources