Grouping closer times in SQL Server I want group closer times (time gap should not exceeded 2 min). What I mean is to group the records which have a time gap less than 10 min. And return min and max time of each group.
How it is possible in SQL Server?
This is my sample data:




message_time
user_id




2020-12-14 10:07:49.358739
11


2020-12-14 10:08:16.156553
11


2020-12-14 10:08:32.195645
11


2020-12-14 13:36:16.103253
12


2020-12-14 13:46:43.871769
12


2020-12-14 13:50:42.867178
12


2020-12-14 13:55:30.530697
12


2020-12-14 13:56:42.472823
12


2020-12-14 13:57:05.880580
12


2020-12-14 13:57:40.377021
4


2020-12-14 13:58:12.723215
12


2020-12-14 14:34:30.633401
4


2020-12-15 10:16:18.188387
4


2020-12-15 10:16:30.735493
4


2020-12-15 10:18:03.501372
4


2020-12-15 12:50:46.953427
4


2020-12-15 12:51:11.772787
4


2020-12-15 12:59:34.264237
11


2020-12-15 13:02:10.622184
12


2020-12-14 10:28:27.489880
12


2020-12-14 10:28:43.793308
12


2020-12-14 19:29:56.208375
12


2020-12-14 19:45:34.401379
12




This is my expected result




Min
Max
user_id




2020-12-14 10:07:49.358739
2020-12-14 10:08:32.195645
11


2020-12-14 13:36:16.103253
2020-12-14 13:36:16.103253
12


2020-12-14 13:46:43.871769
2020-12-14 13:58:12.723215
12


2020-12-14 13:57:40.377021
2020-12-14 13:57:40.377021
4


2020-12-14 14:34:30.633401
2020-12-14 14:34:30.633401
4


2020-12-15 10:16:18.188387
2020-12-15 10:18:03.501372
4


2020-12-15 12:50:46.953427
2020-12-15 12:51:11.772787
4


2020-12-15 12:59:34.264237
2020-12-15 12:59:34.264237
11


2020-12-15 13:02:10.622184
2020-12-15 13:02:10.622184
12


2020-12-14 10:28:27.489880
2020-12-14 19:29:56.208375
12


2020-12-14 19:45:34.401379
2020-12-14 19:45:34.401379
12




*In this table diffreents between the dates in each group should not exceed 10 min
a couple of notes, seems Rob Farley would also write something similar (I just read his comment now). Secondly I missed your 10 minute rule - I'm going to leave some of the fun for you too
/*
create table #t (msgtime datetime2, userid int)

insert #t values ('2020-12-14 10:07:49.358739',11)
insert #t values ('2020-12-14 10:08:16.156553',11)
insert #t values ('2020-12-14 10:08:32.195645',11)
insert #t values ('2020-12-14 13:36:16.103253',12)
insert #t values ('2020-12-14 13:46:43.871769',12)
insert #t values ('2020-12-14 13:50:42.867178',12)
insert #t values ('2020-12-14 13:55:30.530697',12)
insert #t values ('2020-12-14 13:56:42.472823',12)
insert #t values ('2020-12-14 13:57:05.880580',12)
insert #t values ('2020-12-14 13:57:40.377021',4)
insert #t values ('2020-12-14 13:58:12.723215',12)
insert #t values ('2020-12-14 14:34:30.633401',4)
insert #t values ('2020-12-15 10:16:18.188387',4)
insert #t values ('2020-12-15 10:16:30.735493',4)
insert #t values ('2020-12-15 10:18:03.501372',4)
insert #t values ('2020-12-15 12:50:46.953427',4)
insert #t values ('2020-12-15 12:51:11.772787',4)
insert #t values ('2020-12-15 12:59:34.264237',11)
insert #t values ('2020-12-15 13:02:10.622184',12)
insert #t values ('2020-12-14 10:28:27.489880',12)
insert #t values ('2020-12-14 10:28:43.793308',12)
insert #t values ('2020-12-14 19:29:56.208375',12)
insert #t values ('2020-12-14 19:45:34.401379',12)

select * from #t order by userid, msgtime

*/

;with times as 
(select t1.userid, t1.msgtime as prevmsgtime , 
    LEAD(msgtime, 1, NULL) OVER
    (
       Partition By UserID
        ORDER BY
            msgtime
    ) AS nextmsgtime 
from #t t1
)
, seq as
(
select 
userid, 
prevmsgtime , 
nextmsgtime , 
datediff(mi, prevmsgtime , nextmsgtime ) as timediff,
-- If nextmsgtime is null wwe have changed userid 
-- or it the time difference is more than 2 mins - a new group
case when (nextmsgtime  is null) or (datediff(mi, prevmsgtime , nextmsgtime ) &gt; 2)
then convert(int,1)
else convert(int,0)
end as newseq
from times
where datediff(mi, prevmsgtime , nextmsgtime ) is not null

)
,
grouper as 
(
select 
userid, 
prevmsgtime  as prevmsgtime ,
nextmsgtime  as nextmsgtime ,
timediff,
newseq,
-- we can calculate a column to group by by summing up by user the number of times we start a new group
sum(newseq) over (partition by userid order by prevmsgtime ) as groupcol
from seq
)
-- get the min and max msgtime when we have a group
select 
userid,
min(prevmsgtime ) as prevmsgtime ,
max(nextmsgtime ) as nextmsgtime , 
count(*)+1 as nrows
from grouper
where newseq = 0
group by userid,groupcol
union all
-- just get the values if there isn't a group
select 
userid, 
prevmsgtime ,
nextmsgtime ,
1 as nrows
from grouper where newseq = 1
order by userid, prevmsgtime