How to calculate the time of a vehicle continuously at a location in sql

Question

I have tried with row number with min and max logic. but its fail in case when a vehicle visited the same location second time.

Answer 1

This is a gaps-and-islands problem, where you want to group together adjacent rows that have the same vehicle and location.

Here is one approach that uses the difference between row numbers to identify the islands:

select vehicle_no, location, min(time) starttime, max(time) endtime,
    max(time) - min(time) timediff
from (
    select t.*, 
        row_number() over(partition by vehicle_no order by time) rn1,
        row_number() over(partition by vehicle_no, location order by time) rn2
    from mytable t
) t
group by vehicle_no, location, rn1 - rn2