Use the below code:
AS (SELECT ROW_NUMBER() OVER (PARTITION BY UserID
ORDER BY CreationDate)
- DATEDIFF(day,'19000101',CreationDate) AS TheOffset,
COUNT(*) AS NumConsecutiveDays,
GROUP BY UserID,
Note: Make sure the previous statement is ending with a semi-colon.
If you have the list of the days (as a number), and the row_number, then missed days will make the offset between these two lists slightly bigger.
Therefore, you should look for the range that has a consistent offset.
You can also use "ORDER BY NumConsecutiveDays DESC" at the end of this, or for a threshold you can use "HAVING count(*) > 14".
It will work in SQL2005 and above and will be helped by an index on tablename(UserID, CreationDate).
Note: As Offset is a reserved word, so it's recommended to use TheOffset.