I'm learning some basic SQL skills and I was toying around with some data to see what I can do, however, I'm a bit stuck on how to proceed with my current problem.

I have a table that looks like this:

As you can see, we have a bunch of cars and the routes they have driven. A route is simply a sequence of traveled roadsegments. Furthermore, we have the timestamps of entering the segments and leaving the segments.

The goal

What I want to do is to create an easy origin-destination analysis. I'm given a list of segments that belong to location A and similarly, I'm given a list of segments belonging to location B.

My solution

Alright, this is easy. I have made a new table that for each route lists the first and last segment. Using this table, it is then super easy to get the answer.

The problem

The routes themselves are not generated as they should be. In the above table for example, you'll see that car 1 has two routes. The first route end at 10:02:00 whereas the second route begins at 10:05:00. As the this time difference is less that 5 minutes apart, I'd very much like to consider this as 'one route'. I'm not necessarily interested in generating a new table where we glue all routes of the same car together provided that they lie within 5 minutes of each other (although, I'd be very interested in the type of queries one would have to write to accomplish this).

For now, I'd be very happy to get a clue/answer on how to somehow glue these routes together and to make a table where the origin and destination of each route is listed and routes of the same car with at most 5 minutes in between are considered as one route.

I thank you in advance. Although I had some close attempts, a proper solution seems beyond my current (very basic) SQL-skills.

CodePudding user response：

You can compare ordered rows to flag a new route start for a car. And next get a virtual route number within a car. For example

select routeID, SegmentID,  [Order], BeginTime, EndTime, CarID,
  -- virtual route nbr within a car
  sum(newRouteFlag)  over(partition by CarID order by BeginTime) rtNmbr
from (
  select routeID, SegmentID, [Order], BeginTime, EndTime, CarID,
          case when lag(routeID) over(partition by CarID order by BeginTime) = routeID 
           or dateadd(minute, 5, lag(BeginTime) over(partition by CarID order by BeginTime)) >= BeginTime then 0 else 1 end newRouteFlag
  from tbl
) t