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.
--Jeff Moden
Change is inevitable... Change for the better is not.