How to group rows by their DATEDIFF?

Question

I hope you can help me.

I need to display the records in HH_Solution_Audit table -- if 2 or more staffs enter the room within 10 minutes. Here are the requirements:

1. Display only the events that have a timestamp (LAST_UPDATED) interval of less than or equal to 10 minutes. Therefore, I must compare the current row to the next row and previous row to check if their DATEDIFF is less than or equal to 10 minutes. I’m done with this part.
2. Show only the records if the number of distinct STAFF_GUID inside the room for less than or equal to 10 minutes is at least 2.

HH_Solution_Audit Table Details:

1. ID - PK
2. STAFF_GUID - staff id
3. LAST_UPDATED - datetime when a staff enters a room

Here's what I got so far. This satisfies requirement # 1 only.

CREATE TABLE HH_Solution_Audit (
ID INT PRIMARY KEY,
STAFF_GUID NVARCHAR(1),
LAST_UPDATED DATETIME
)
GO
INSERT INTO HH_Solution_Audit VALUES (1, 'b', '2013-04-25 9:01')
INSERT INTO HH_Solution_Audit VALUES (2, 'b', '2013-04-25 9:04')
INSERT INTO HH_Solution_Audit VALUES (3, 'b', '2013-04-25 9:13')
INSERT INTO HH_Solution_Audit VALUES (4, 'a', '2013-04-25 10:15')
INSERT INTO HH_Solution_Audit VALUES (5, 'a', '2013-04-25 10:30')
INSERT INTO HH_Solution_Audit VALUES (6, 'a', '2013-04-25 10:33')
INSERT INTO HH_Solution_Audit VALUES (7, 'a', '2013-04-25 10:41')
INSERT INTO HH_Solution_Audit VALUES (8, 'a', '2013-04-25 11:02')
INSERT INTO HH_Solution_Audit VALUES (9, 'a', '2013-04-25 11:30')
INSERT INTO HH_Solution_Audit VALUES (10, 'a', '2013-04-25 11:45')
INSERT INTO HH_Solution_Audit VALUES (11, 'a', '2013-04-25 11:46')
INSERT INTO HH_Solution_Audit VALUES (12, 'a', '2013-04-25 11:51')
INSERT INTO HH_Solution_Audit VALUES (13, 'a', '2013-04-25 12:24')
INSERT INTO HH_Solution_Audit VALUES (14, 'b', '2013-04-25 12:27')
INSERT INTO HH_Solution_Audit VALUES (15, 'b', '2013-04-25 13:35')

---

    DECLARE @numOfPeople INT = 2,   
              --minimum number of people that must be inside 
              --the room for @lengthOfStay minutes
            @lengthOfStay INT = 10, 
              --number of minutes of stay
            @dateFrom DATETIME = '04/25/2013 00:00',
            @dateTo DATETIME = '04/25/2013 23:59';
    WITH cteSource AS
    (
         SELECT ID, STAFF_GUID, LAST_UPDATED, 
              ROW_NUMBER() OVER (ORDER BY LAST_UPDATED) AS row_num
         FROM HH_SOLUTION_AUDIT 
              WHERE LAST_UPDATED >= @dateFrom AND LAST_UPDATED <= @dateTo
    )
    SELECT [current].ID, [current].STAFF_GUID, [current].LAST_UPDATED
    FROM
         cteSource AS [current]
    LEFT OUTER JOIN
         cteSource AS [previous] ON [current].row_num = [previous].row_num + 1
    LEFT OUTER JOIN
         cteSource AS [next] ON [current].row_num = [next].row_num - 1
    WHERE
         DATEDIFF(MINUTE, [previous].LAST_UPDATED, [current].LAST_UPDATED) 
         <= @lengthOfStay
         OR
         DATEDIFF(MINUTE, [current].LAST_UPDATED, [next].LAST_UPDATED) 
         <= @lengthOfStay  
    ORDER BY [current].ID, [current].LAST_UPDATED    

---

Running the query returns IDs:
1, 2, 3, 5, 6, 7, 10, 11, 12, 13, 14
That satisfies requirement # 1 of having less than or equal to 10 minutes interval between the previous row, current row and next row.

Can you help me with the 2nd requirement? If it's applied, the returned IDs should only be:
13, 14

Answer 1

Here's an idea. You don't need ROW_NUMBER and previous and next records. You just need to queries unioned - one looking for everyone that have someone checked X minutes behind, and another looking for X minutes upfront. Each uses a correlated sub-query and COUNT(*) to find number of matching people. If number is greater then your @numOfPeople - that's it.

EDIT: new version: Instead of doing two queries with 10 minutes upfront and behind, we'll only check for 10 minutes behind - selecting those that match in cteLastOnes. After that will go in another part of query to search for those that actually exist within those 10 minutes. Ultimately again making union of them and the 'last ones'

WITH cteSource AS
(
    SELECT ID, STAFF_GUID, LAST_UPDATED
    FROM HH_SOLUTION_AUDIT 
    WHERE LAST_UPDATED >= @dateFrom AND LAST_UPDATED <= @dateTo
)
,cteLastOnes AS 
(
    SELECT * FROM cteSource c1
    WHERE @numOfPeople -1 <= (SELECT COUNT(DISTINCT STAFF_GUID) 
                              FROM cteSource c2 
                              WHERE DATEADD(MI,@lengthOfStay,c2.LAST_UPDATED) > c1.LAST_UPDATED 
                                AND C2.LAST_UPDATED <= C1.LAST_UPDATED 
                                AND c1.STAFF_GUID <> c2.STAFF_GUID) 
)
SELECT * FROM cteLastOnes
UNION
SELECT * FROM cteSource s
WHERE EXISTS (SELECT * FROM cteLastOnes l 
               WHERE DATEADD(MI,@lengthOfStay,s.LAST_UPDATED) > l.LAST_UPDATED 
                AND s.LAST_UPDATED <= l.LAST_UPDATED 
                AND s.STAFF_GUID <> l.STAFF_GUID)

SQLFiddle DEMO - new version

SQLFiddle DEMO - old version