How to group data if difference between rows is mothe than 1 minute

Question

Im trying to create a new table on SQL which will have summarized data.

For example. In the original table i have this data:

If you see rows 9, 10 and 11 they are consecutive, so i want to group them on one row with

Begin: 2020-07-02 20:11:00.000
End:   2020-07-02 20:13:00.000

Ignore rows 12 and 13. I made a mistake colorizing them

Answer 1

This is a gaps-and-islands problem. You want to group consecutive rows whose gap between begin_date is less than 1 minute.

Here, I would recommend you lag() and a cumulative sum to define the groups, then aggregation:

select id, min(begin_date) begin_date, max(begin_date) end_date
from (
    select t.*,
        sum(case when begin_date <= dateadd(minute, 1, lag_begin_date) then 0 else 1 end) 
            over(partition by id order by begin_date) grp
    from (
        select t.*, lag(begin_date) over(partition by id order by begin_date) lag_begin_date
        from mytable t
    ) t
) t
group by id, grp