It has been some years since this article was posted http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data and things has evolved since then. So I thought about using XML to solve the case. If the number of records are large, maybe this approach http://weblogs.sqlteam.com/peterl/archive/2009/08/12/Calculating-Running-Streak-over-many-records.aspx will be better for you?
Below, I am using the same sample data as the original.
DECLARE @Sample TABLE
(
GameDate DATETIME,
Result CHAR(1)
)
INSERT @Sample
SELECT '1/1/2000', 'W' UNION ALL
SELECT '1/12/2000', 'L' UNION ALL
SELECT '1/15/2000', 'W' UNION ALL
SELECT '1/17/2000', 'W' UNION ALL
SELECT '1/22/2000', 'W' UNION ALL
SELECT '2/1/2000', 'L' UNION ALL
SELECT '2/5/2000', 'W' UNION ALL
SELECT '2/8/2000', 'L' UNION ALL
SELECT '2/16/2000', 'W' UNION ALL
SELECT '2/19/2000', 'L' UNION ALL
SELECT '2/25/2000', 'L' UNION ALL
SELECT '2/28/2000', 'L' UNION ALL
SELECT '3/15/2000', 'L' UNION ALL
SELECT '3/19/2000', 'W' UNION ALL
SELECT '3/25/2000', 'W'
For this to work, you must have a Tally table present. A tally table is nothing more than a table having numbers sequentially stored, ranging from 1 (or 0) to about 100,000. I am using 100,000 and it's very rarely I have had use for a bigger tally numbers table. So, if you don't have a Tally numbers table, go create one.
And now on to the suggestion.
DECLARE @Results VARCHAR(MAX)
SET @Results = (
SELECT '' + Result
FROM @Sample
ORDER BY GameDate
FOR XML PATH('')
)
;WITH Yak(Start, Streak)
AS (
SELECT MIN(Number) AS Start,
COUNT(*) AS Streak
FROM (
SELECT Number,
ROW_NUMBER() OVER (ORDER BY Number) AS recID
FROM dbo.TallyNumbers
WHERE Number BETWEEN 1 AND DATALENGTH(@Results)
AND SUBSTRING(@Results, Number, 1) = 'W'
-- Or <> 'L' for Undefeated (if t for "tie" is used)
) AS d
GROUP BY Number - recID
)
SELECT s.GameDate,
y.Streak
FROM Yak AS y
INNER JOIN (
SELECT ROW_NUMBER() OVER (ORDER BY GameDate) AS recID,
GameDate
FROM @Sample
) AS s ON s.recID = y.Start
ORDER BY s.GameDate