SQL to determine minimum sequential days of access ?
Have a look at this solution,
SELECT DISTINCT UserId FROM UserHistory uh1 WHERE ( SELECT COUNT(*) FROM UserHistory uh2 WHERE uh2.CreationDate BETWEEN uh1.CreationDate AND DATEADD(d, @days, uh1.CreationDate) ) = @days OR UserId = 52551
We can also do as follows:
DECLARE @days int DECLARE @seconds bigint SET @days = 30 SET @seconds = (@days * 24 * 60 * 60) - 1 SELECT DISTINCT UserId FROM ( 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 Tbl WHERE Conseq >= @days
That can be done much simpler,
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, UserID FROM numberedrows GROUP BY UserID, TheOffset
We’re looking for a range that has a consistent offset. Because 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
Also have use something called “ORDER BY NumConsecutiveDays DESC” at the end of this, or say “HAVING count(*) > 14” for a threshold…
Keep in mind that it is works in SQL2005 and on, helped by an index on tablename
Here , we are using TheOffset. Because the Turns out Offset is a reserved word.
There is better to use COUNT(*) is very valid – Here it shold have done that in the first place but wasn’t really thinking. but Previously instead of using this, it was using datediff(day, min(CreationDate), max(CreationDate))