How to partition datetime with 5 minutes interval in SQL Server?

Question

How to group datetime with 5 minutes interval.

This is my table

id  Belongs_to            dt
----------------------------------------
102 2020-08-07  2020-08-07 09:48:01.000
102 2020-08-07  2020-08-07 09:48:03.000
102 2020-08-07  2020-08-07 16:24:01.000
102 2020-08-07  2020-08-07 17:22:03.000
102 2020-08-07  2020-08-07 17:23:01.000
102 2020-08-07  2020-08-07 17:23:01.000
102 2020-08-07  2020-08-07 21:28:03.000
102 2020-08-07  2020-08-07 21:28:04.000

And I tried below query to group

select 
    t.emp_Reader_id, Belongs_to, 
    format(dt, 'yyyy-MM-dd HH:mm') as dt,
    row_number() over (partition by t.emp_reader_id, Belongs_to order by FORMAT(dt, 'yyyy-MM-dd HH:mm')) as seqnum
from
    trnevents t
where 
    T.emp_reader_id = 102
group by 
    t.emp_reader_id, format(dt, 'yyyy-MM-dd HH:mm'), Belongs_to

Now time seconds is neglected fine and also I want to group by 5 minutes interval if its in same time

Expected Output:

id  Belongs_to           dt        seqnum
102 2020-08-07  2020-08-07 09:48    1
102 2020-08-07  2020-08-07 16:24    2
102 2020-08-07  2020-08-07 17:22    3
102 2020-08-07  2020-08-07 21:28    4

Answer 1

How about something like:

with groupCTE As (
select T.emp_Reader_id, 
       T.belongs_to, 
       T.dt, 
       rowN = ROW_NUMBER() OVER(PARTITION BY dateadd(minute, datediff(minute,0,T.dt) / 5 * 5, 0) ORDER BY T.dt)
from
    trnevents T
where 
    T.emp_reader_id = 102
)
select emp_Reader_id as id, 
       Belongs_to, 
       dt, 
       ROW_NUMBER() OVER(ORDER BY dt) as seqnum
from groupCTE   
where rowN = 1
order by dt

Answer 2

You can try this:

SELECT *,
       ROW_NUMBER() OVER (ORDER BY dt) AS seqno
FROM
(
    SELECT DISTINCT
           K.emp_Reader_id,
           K.Belongs_to,
           K.newDT AS dt
    FROM
    (
        SELECT emp_Reader_id, Belongs_to,
               CASE
                   WHEN DATEDIFF(MINUTE, K.checker, dt) > 5 THEN
                       dt
                   ELSE
                       K.checker
               END AS newDT
        FROM
        (
            SELECT *,
                   FIRST_VALUE(dt) OVER (PARTITION BY CAST(dt AS DATE), DATEPART(HOUR, dt)
                                         ORDER BY dt
                                         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                                        ) AS checker
            FROM #r
        ) AS K
    ) AS K
) AS K;

Read more about FIRST_VALUE

Answer 3

other solution :

with DataWithUniQID as (
select f0.emp_Reader_id, f0.Belongs_to, f0.dt, format(f0.dt, 'yyyy-MM-dd HH:mm') as fmtdt,
    row_number() over (partition by f0.emp_reader_id order by f0.dt) as ID
from trnevents f0
)
select f1.*,
row_number() over (partition by f1.emp_reader_id order by f1.ID) as seqnum 
from DataWithUniQID f1
outer apply
(
    select top 1 f1.id, f2.dt from DataWithUniQID f2
    where f1.emp_Reader_id=f2.emp_Reader_id and f2.ID<f1.ID
    order by f2.ID desc
) f3
where f3.ID is null or DATEDIFF(MINUTE, f1.dt, f3.dt) >= 5