COUNT(*) group by similar timestamps

Question

I have some data in my table (tb1):

PK   datetime1               datetime2               grp
--------------------------------------------------------
 1   2016-01-01 00:30:10     2016-01-01 00:33:10      1
 2   2013-01-01 00:30:10     2013-01-01 00:34:10      2

I am trying to find the number of events based on datetime1, datetime2, and location.

So this is my query:

 select count(*), datetime1, datetime2, grp 
 from tb1
 group by datetime1, datetime2, grp

No issue here.

To an extent, I would like to find the count based on:

when the datetime1 difference between the two consecutive (can be achieved by lead function) row is less than 1 sec and the datetime2 difference between the two consecutive row is less than 1 sec, and grp.

I can use lead to finding the difference between two consecutive row, but not sure how I can apply count function here to group by time similarity between two consecutive rows.

To make this simpler, I am looking for something like this:

if

 select 
     count(*), grp, ....
 from 
     tb1
 where 
     datediff(s, lead(datetime1, 1, 1)  over (partition by grp order by datetime1)) = 1 
     and datediff(s, lead(datetime2, 1, 1) = 1 over (partition by grp order by datetime2) = 1 
group by
    lead(datetime1, 1, 1) over (partition by grp order by datetime1), 
    lead(datetime2, 1, 1) over (partition by grp order by datetime2), 
    grp 

Please let me know if there is a need for additional clarification.

Answer 1

This is based on int, but same approach would work with datetime

declare @T table (pk int identity primary key, val int);
insert into @T values ('1'), ('9'), ('9'), ('11'), ('2'), ('2'), ('3'), ('5'), ('7'), ('8');
select tt.pk, tt.val 
     , sum(ll) over (order by val, pk) as grp
from  ( select * 
             , case when lag(val,1) over (order by val, pk) is null 
                      or val - lag(val,1) over (order by val, pk) <= 1 then 0 
                    else 1 
               end as ll
          from @T t
      ) tt
order by val, pk;

pk          val         grp
----------- ----------- -----------
1           1           0
5           2           0
6           2           0
7           3           0
8           5           1
9           7           2
10          8           2
2           9           2
3           9           2
4           11          3

Answer 2

I generated some sample data. Check if this is what you are looking for. I put necessary comments in code. Query can be more concise, but I wanted to explain as much as I can on every step:

declare @table table (PK int, datetime1 datetime, datetime2 datetime, grp int)
insert into @table values
(1, '2016-01-01 00:30:14.000', '2016-01-01 00:33:15.000', 1),
(2, '2016-01-01 00:30:10.232', '2016-01-01 00:33:10.000', 1),
(3, '2016-01-01 00:30:10.111', '2016-01-01 00:33:10.234', 1),
(4, '2016-01-01 00:30:12.000', '2016-01-01 00:33:15.000', 2),
(5, '2016-01-01 00:30:10.000', '2016-01-01 00:33:10.234', 2),
(6, '2016-01-01 00:30:10.222', '2016-01-01 00:33:10.000', 2)

select min(pk), min(datetime1), count(*) from (
    --in this query, based on differences, we will generate grouping column called IsClose
    select *, case when (diff1 <= 1000 and diff2 <= 1000) or (diff3 <= 1000 and diff4 <= 1000) then 1 else 0 end [IsClose] from (
        --this query gives to additionals columns with absolute differences between consecutive rows ordered by PK column
        select *,
               abs(datediff(ms, datetime1, lag(datetime1) over (order by pk))) [diff1],
               abs(datediff(ms, datetime2, lag(datetime2) over (order by pk))) [diff2],
               abs(datediff(ms, datetime1, lead(datetime1) over (order by pk))) [diff3],
               abs(datediff(ms, datetime2, lead(datetime2) over (order by pk))) [diff4]  
        from @table
    ) [a]
) [a] group by grp, IsClose