title: How to group rows by their DATEDIFF?
body: <p>I hope you can help me.  </p>
  
  <p>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:</p>
  
  <ol>
  <li>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.</li>
  <li>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. </li>
  </ol>
  
  <p><strong>HH_Solution_Audit Table Details:</strong>  </p>
  
  <ol>
  <li>ID - PK  </li>
  <li>STAFF_GUID - staff id</li>
  <li>LAST_UPDATED - datetime when a staff enters a room</li>
  </ol>
  
  <p>Here's what I got so far. This satisfies requirement # 1 only.</p>
  
  <pre><code>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')
  </code></pre>
  
  <hr>
  
  <pre><code>    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 &gt;= @dateFrom AND LAST_UPDATED &lt;= @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) 
           &lt;= @lengthOfStay
           OR
           DATEDIFF(MINUTE, [current].LAST_UPDATED, [next].LAST_UPDATED) 
           &lt;= @lengthOfStay  
      ORDER BY [current].ID, [current].LAST_UPDATED    
  </code></pre>
  
  <hr>
  
  <p>Running the query returns IDs:<br>
  <strong>1, 2, 3, 5, 6, 7, 10, 11, 12, 13, 14</strong><br>
  That satisfies requirement # 1 of having less than or equal to 10 minutes interval between the previous row, current row and next row.   </p>
  
  <p>Can you help me with the 2nd requirement? If it's applied, the returned IDs should only be:<br>
  <strong>13, 14</strong></p>