SQL to determine minimum sequential days of access?

How about (and please make sure the previous statement ended with a semi-colon):

WITH numberedrows     AS (SELECT ROW_NUMBER() OVER (PARTITION BY UserID                                        ORDER BY CreationDate)                - DATEDIFF(day,'19000101',CreationDate) AS TheOffset,                CreationDate,                UserID         FROM   tablename)SELECT MIN(CreationDate),       MAX(CreationDate),       COUNT(*) AS NumConsecutiveDays,       UserIDFROM   numberedrowsGROUP  BY UserID,          TheOffset  

The idea being that if we have list of the days (as a number), and a row_number, then missed days make the offset between these two lists slightly bigger. So we're looking for a range that has a consistent offset.

You could use "ORDER BY NumConsecutiveDays DESC" at the end of this, or say "HAVING count(*) > 14" for a threshold...

I haven't tested this though - just writing it off the top of my head. Hopefully works in SQL2005 and on.

...and would be very much helped by an index on tablename(UserID, CreationDate)

Edited: Turns out Offset is a reserved word, so I used TheOffset instead.

Edited: The suggestion to use COUNT(*) is very valid - I should've done that in the first place but wasn't really thinking. Previously it was using datediff(day, min(CreationDate), max(CreationDate)) instead.

Rob

The answer is obviously:

SELECT DISTINCT UserIdFROM UserHistory uh1WHERE (       SELECT COUNT(*)        FROM UserHistory uh2        WHERE uh2.CreationDate        BETWEEN uh1.CreationDate AND DATEADD(d, @days, uh1.CreationDate)      ) = @days OR UserId = 52551

EDIT:

Okay here's my serious answer:

DECLARE @days intDECLARE @seconds bigintSET @days = 30SET @seconds = (@days * 24 * 60 * 60) - 1SELECT DISTINCT UserIdFROM (    SELECT uh1.UserId, Count(uh1.Id) as Conseq    FROM UserHistory uh1    INNER JOIN UserHistory uh2 ON uh2.CreationDate         BETWEEN uh1.CreationDate AND             DATEADD(s, @seconds, DATEADD(dd, DATEDIFF(dd, 0, uh1.CreationDate), 0))        AND uh1.UserId = uh2.UserId    GROUP BY uh1.Id, uh1.UserId    ) as TblWHERE Conseq >= @days

EDIT:

[Jeff Atwood] This is a great fast solution and deserves to be accepted, but Rob Farley's solution is also excellent and arguably even faster (!). Please check it out too!

If you can change the table schema, I'd suggest adding a column LongestStreak to the table which you'd set to the number of sequential days ending to the CreationDate. It's easy to update the table at login time (similar to what you are doing already, if no rows exist of the current day, you'll check if any row exists for the previous day. If true, you'll increment the LongestStreak in the new row, otherwise, you'll set it to 1.)

The query will be obvious after adding this column:

if exists(select * from table          where LongestStreak >= 30 and UserId = @UserId)   -- award the Woot badge.