Group Data Rows By Near Time

Here is the problem I am facing: I got a large table containing rows, I want to group them by near time, more specifically the time difference less than 2 minutes, example as follo

Solution 1:

If you're using SQL server 2012, you'r in luck and you can use lag function and rolling total sum:

with cte as (
    selectcasewhen datediff(mi, lag(data) over (orderby data), data) <= 1then0else1endas ch,
        data
    from test
), cte2 as (
    select
        data, sum(ch) over (orderby data) as grp
    from cte
)
select
    min(data) as data, count(*) as cn
from cte2
groupby grp

sql fiddle demo

Solution 2:

SELECTCONVERT(VARCHAR(8),
               DATEADD(minute, (DATEDIFF(n, 0, time) /2) *2, 0),
               108),
       COUNT(*)
FROM times
GROUPBY DATEDIFF(n, 0, time) /2

Explanation:CONVERT displays a DateTime in hh:mm:ss format (= 108). DATEDIFF converts to minutes and then divides by two, rounding to an integer so each GROUP of 2 minutes resolves to the same integer. DATEADD is used to convert this number of minutes back to a DateTime, having multiplied by 2 to get back to the correct (rounded) time.

See SQL Fiddle Demo

Solution 3:

Declare@m_TestTable table
(
DateRecorded datetime

)

Insertinto@m_TestTable Values ('16:01:01' )
Insertinto@m_TestTable Values ('16:01:20' )
Insertinto@m_TestTable Values ('16:14:02' )
Insertinto@m_TestTable Values ('16:15:01' )
Insertinto@m_TestTable Values ('16:20:01' );

With tblDifference as
(
SelectRow_Number() OVER (Orderby DateRecorded) as RowNumber,DateRecorded from@m_TestTable
)

select cur.DateRecorded as prvD, prv.DateRecorded as prvC, dateDiff(n, cur.DateRecorded,prv.DateRecorded)  from tblDifference cur LEFTOUTERJOIN tblDifference prv 
ON cur.RowNumber = prv.RowNumber +1

this will give you the time difference in minutes between 2 rows. You can select any row that has a time difference less then 2 mins. It will also give you the upper and lower value.

It should be usefull to find any values closer then 2 minutes apart.

prvDprvCDiff1900-01-01 16:01:01.000 NULLNULL1900-01-01 16:01:20.000 1900-01-01 16:01:01.000 01900-01-01 16:14:02.000 1900-01-01 16:01:20.000 -131900-01-01 16:15:01.000 1900-01-01 16:14:02.000 -11900-01-01 16:20:01.000 1900-01-01 16:15:01.000 -5