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  
  • 1
    Please specify your version of SQL Server; if 2012 or newer, then you can do this with LAG.
    – RDFozz
    Apr 5 '18 at 15:13
  • Sorry, I forgot to mention. SQL 2014
    – Tad
    Apr 5 '18 at 15:17
  • 1
    Was a cursor inefficient, or do you just think a cursor would be inefficient? Did you try it? Could you show what you tried? Also it's unclear to me if Bar is the output column you want, or Bar is in the table and needs to be used in some way, or something else. If you've shown the data in the table, could you show the desired results? If those are the desired results based on the column Foo, could you explain why Bar doesn't increment between 12:05 PM and 12:45 PM? That's more than 10 minutes. Apr 5 '18 at 15:34
  • I think a cursor would be inefficient. Bar is what I am looking to add and is not part of the data in the table. The table is the desired output. I'd like to set the threshold for time gaps. The 12:05pm to 12:45pm is a mistake in my example. Sorry for the confusion.
    – Tad
    Apr 5 '18 at 16:53
  • i feel like this is an XY problem. what are you trying to solve with this? meta.stackexchange.com/questions/66377/what-is-the-xy-problem
    – DForck42
    Apr 5 '18 at 17:49

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 (
              ,CASE WHEN COALESCE(DATEDIFF( minute
                                           ,LAG(theDateTime, 1)
                                             OVER (ORDER BY 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.


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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.