Find longest series - 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 2005 Forums

Transact-SQL (2005)

Find longest series

Author

Topic

Heinz23
Yak Posting Veteran

84 Posts

Posted - 2008-08-23 : 20:26:39

Hi all,

I have a table like this (Important is only the Criteria. Row and Username are just the values I need returned in my result set).

Criteria - Row - Username
1...........1.......Peter
1...........2.......John
2...........3.......Peter
1...........4.......Peter
3...........5.......Paul
3...........6.......Paul
2...........7.......Paul
2...........8.......John
1...........9.......Peter
3...........10.......John
3...........11.......Peter

Now I need to filter by criteria. I need the longest uninterfered series, e.g. for criteria 1 it would be row 1 & 2, for criteria it would be row 7 & 8, for criteria 3 it would be 5&6 and 10&11 (this is a special case as there are 2 'series' with the same length, see below).
I have no idea how to do it, searching this forum and using google did also not help. Any ideas?
Additionally I need also a stored procedure which access 2 input parameters (both of 'criteria'), so I could supply criteria 1 and criteria 2 and would get back rows 1 to 4.
As said above there is a special case when there is no exact longest series but e.g. 2 series with the same length, in above example it is criteria 3. So the script should return row 5 & 6 and row 10 & 11, but my program needs to be able to differ. Having an additional column 'seriesNumber' in the result set would be very helpful, so row 5 & 6 is returned with seriesNumber 1 and row 10 & 11 is returned with seriesNumber 2 (because it's the second series). Of course the seriesNumber might be added everytime, even if there is only 1 series to return.

Input: 1
Returned Lines: Row 1 & 2

Input: 2
Returned Lines: Row 7 & 8.

Input: 3
Returned Lines: Row 5 & 6 and Row 10 & 11 (with 'SeriesNumber')

Input: 1 , 3
Returned Lines: Row 4-6 and Row 9-11 (with SeriesNumber)

Input: 1 , 2
Returned Lines: Row 1-4

Input: 2 , 3
Returned Lines: Row 5 - 8

Many thanks in advance!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-23 : 20:45:18

This might be what you need:
http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Heinz23
Yak Posting Veteran

84 Posts

Posted - 2008-08-24 : 04:19:45

Hi Tara,

this works brillant, thanks a lot! Sometimes it's only using the right words, e.g. 'streaks' instead of 'series'

. I've also modified it a bit as I have 3 criterias so in Jeffs Example it would be Win, Loss and Draw. I'm not an SQL-Expert but at least it works

Feel free to post any improvements!

I had to create 2 new columns 'WinDraw' and 'LossDraw' which contain a '1' if the result is a Win or a Draw (or, for LossDraw, which is a Loss ora Draw). These columns are needed later on for grouping. Additionally I have 2 new rungroups, RunGroupLS (Loss or Draw) and RunGroupWD (Win or Draw).


SELECT MatchDate, Win,
	 (CASE WHEN Win = 'W' OR Win = 'D' Then 1 ELSE 0 END) as WinDraw,
	 (CASE WHEN Win = 'L' OR Win = 'D' Then 1 ELSE 0 END) as LossDraw,
  (SELECT COUNT(*) FROM #TempLeagueMatches TL 
		WHERE TL.Win <> TLM.Win AND TL.MatchDate <= TLM.MatchDate) as RunGroup,
(SELECT COUNT(*) FROM #TempLeagueMatches TL 
		WHERE TL.Win <> TLM.Win AND TL.MatchDate <= TLM.MatchDate  AND NOT 
			((TL.Win = 'D' and TLM.Win = 'L') OR (TL.Win = 'L' and TLM.Win = 'D') OR
			TLM.Win = ''))
			as RunGroupLD,
(SELECT COUNT(*) FROM #TempLeagueMatches TL 
		WHERE TL.Win <> TLM.Win AND TL.MatchDate <= TLM.MatchDate  AND NOT 
			((TL.Win = 'D' and TLM.Win = 'W') OR (TL.Win = 'W' and TLM.Win = 'D') OR 
			TLM.Win = ''))
			as RunGroupWD
INTO #Streaks1
FROM #TempLeagueMatches TLM 


-- Comparing only Single Results: W, L or D --
SELECT Win, 
  MIN(MatchDate) as StartDate, 
  MAX(MatchDate) as EndDate, 
  COUNT(*) as Games
INTO #Streaks2
FROM #Streaks1 A
GROUP BY Win, RunGroup
ORDER BY Min(MatchDate) DESC

-- Comparing double Results: Loss or Draw --
SELECT LossDraw, 
  MIN(MatchDate) as StartDate, 
  MAX(MatchDate) as EndDate, 
  COUNT(*) as Games
INTO #StreaksLD
FROM #Streaks1 A
GROUP BY LossDraw, RunGroupLD
ORDER BY Min(MatchDate) DESC

-- Comparing Double results : Wins or Draws --
SELECT WinDraw, 
  MIN(MatchDate) as StartDate, 
  MAX(MatchDate) as EndDate, 
  COUNT(*) as Games
INTO #StreaksWD
FROM #Streaks1 A
GROUP BY WinDraw, RunGroupWD
ORDER BY Min(MatchDate) DESC

SELECT TOP 10 Win, StartDate, EndDate, Games as StreakWinsLeague
FROM #Streaks2 
WHERE Win = 'W'
ORDER BY Games DESC 

SELECT TOP 10 Win, StartDate, EndDate, Games as StreakDrawsLeague
FROM #Streaks2 
WHERE Win = 'D'
ORDER BY Games DESC 

SELECT TOP 10 Win, StartDate, EndDate, Games as StreakLossesLeague
FROM #Streaks2 
WHERE Win = 'L'
ORDER BY Games DESC 

SELECT TOP 10 'WD' as Win, StartDate, EndDate, Games as StreakWinsDrawsLeague
FROM #StreaksWD 
ORDER BY Games DESC 

SELECT TOP 10 'LD' as Win, StartDate, EndDate, Games as StreakLossesDrawsLeague
FROM #StreaksLD
ORDER BY Games DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources