Sql-server – Add column to show rows with close time differences without cursor

sql serversql server 2014

I have a table with a Date_Time column and want to identify groups of close date differences and identify them by incrementing numbers if the difference is greater than 10 minutes.

I could do this with a cursor, but want something more efficient.

Sample data:

Foo               Bar
----------------  ---  
8/23/12 12:05 PM    1  
8/23/12 12:08 PM    1  
8/23/12 12:11 PM    1  
8/23/12 12:15 PM    1  
8/23/12 12:20 PM    1  
8/23/12 12:24 PM    1  
8/23/12 12:28 PM    1  
8/23/12 12:32 PM    1  
8/23/12 12:36 PM    1  
8/23/12 12:39 PM    1  
8/23/12 12:42 PM    1  
8/23/12 12:45 PM    1  
8/23/12 4:01 PM     2  
8/23/12 4:05 PM     2  
8/23/12 4:08 PM     2  
8/23/12 4:11 PM     2  
8/23/12 4:14 PM     2  
8/23/12 4:20 PM     2  
8/23/12 4:23 PM     2  
8/23/12 4:28 PM     2  
8/23/12 4:33 PM     2  
8/23/12 4:36 PM     2  
8/23/12 4:40 PM     2  
8/23/12 4:44 PM     2  
8/23/12 4:47 PM     2  
8/23/12 8:01 PM     3  

Best Answer

Based on the question at this time, I assume you have a list of dates and times, and you want to assign a sequential number to them, with the same number assigned to all values where the previous value is no more than 10 minutes less than the current value, as shown in the desired results.

Let's assume your table of date time values is myDtTm, and the column name is theDateTime

SELECT theDateTime
      ,SUM(TimeDiff) OVER (ORDER BY theDateTime)
  FROM (
        SELECT
               theDateTime
              ,CASE WHEN COALESCE(DATEDIFF( minute
                                           ,LAG(theDateTime, 1)
                                             OVER (ORDER BY theDateTime)
                                           ,theDateTime
                                          ), 100000) > 10 --< this is the max time diff
                 THEN 1
                 ELSE 0
               END as TimeDiff
          FROM myDtTm
       ) sq
 ORDER BY theDateTime
;

This dbfiddle.uk link shows this in action, providing precisely the results shown in the question.

Explanation:

In the sub-query, we use the window function LAG to compare each date and time to the one before it, and figure out how many minutes' difference there is. We assign 1 to TimeDiff for the current date if the difference more than the target (ten minutes), and 0 otherwise. (Note: we get back NULL for the first row, because there is no previous row at that point; if you have NULL datetime values in your data, you may need to change this).

Then, we simply do a rolling total (SUM of TimeDiff over our datetime value gives us a total of TimeDiff for this row and all previous rows).

Note: I took the idea from this DBA.SE question; the differences were notable enough that I don't think this is a duplicate.