SQL count of differences of specific timestamps

Question

I have a table that looks like this:

UNIQUEID    FILEKEY DTTMSTAMP
-------------------------------------------
282           1     2012-07-19 09:02:00.000
283           1     2012-07-19 17:12:00.000
284           1     2012-07-20 08:53:00.000
285           1     2012-07-20 17:09:00.000
286           1     2012-07-23 08:54:00.000
287           1     2012-07-23 17:06:00.000
288           1     2012-07-24 09:00:00.000
289           1     2012-07-24 17:04:00.000
290           1     2012-07-25 08:59:00.000
291           1     2012-07-25 17:05:00.000

There are well over 50K rows. I need to get the following info out of this:

I need the count of the number days where there are exactly 4 timestamps for a given filekey and the difference between the fourth dttmstamp and the 3rd dttmstamp is greater than 3 hours.

It should ultimately look like this:

Filekey   Count 
----------------
1         650

etc.

Answer 1

In SQL Server 2012 you can use LAG:

;WITH cte AS (
    SELECT  FILEKEY,
            DTTMSTAMP,
            ROW_NUMBER() OVER (PARTITION BY FILEKEY, CAST(DTTMSTAMP as date) ORDER BY DTTMSTAMP) as RN,
            DATEDIFF(second,LAG(DTTMSTAMP,1,NULL) OVER (ORDER BY DTTMSTAMP),DTTMSTAMP)/3600 as SEQ
    FROM YourTable
)

SELECT  FILEKEY,
        COUNT(DTTMSTAMP) as [COUNT]
FROM cte
WHERE RN = 4 and SEQ >= 3
GROUP BY FILEKEY
HAVING MAX(RN) = 4

For SQL Server < 2012 this should work in cte part:

SELECT  t.FILEKEY,
        t.DTTMSTAMP,
        ROW_NUMBER() OVER (PARTITION BY t.FILEKEY, CAST(t.DTTMSTAMP as date) ORDER BY t.DTTMSTAMP) as RN,
        DATEDIFF(second,DTTMSTAMP_PREV,DTTMSTAMP)/3600 as SEQ
FROM YourTable t
OUTER APPLY (
    SELECT TOP 1 DTTMSTAMP as DTTMSTAMP_PREV
    FROM YourTable
    WHERE FILEKEY = t.FILEKEY AND DTTMSTAMP < t.DTTMSTAMP
    ORDER BY DTTMSTAMP DESC
    ) as d