Jeff Moden (11/5/2012)

---

Mark-101232 (11/5/2012)

---

You don't need to use recursion to find the ranges of 1753s starts and their lengths. Try this instead

DECLARE @CutOffDate DATETIME = '2009-01-01';

WITH CTE AS (

SELECT ID, VisitDate,

ROW_NUMBER() OVER(ORDER BY ID) -

ROW_NUMBER() OVER(PARTITION BY CASE WHEN VisitDate < @CutOffDate THEN 1 ELSE 0 END ORDER BY ID) AS rnDiff

FROM WebRequest)

SELECT MIN(ID) AS StartID,

COUNT(*) AS RunLength

FROM CTE

WHERE VisitDate < @CutOffDate

GROUP BY rnDiff

ORDER BY StartID;

Nicely done, Mark.