How to calculate the number of times a route went through using SQL?

Question

I need to determine how many times a route traveled in one vehicle over a certain date range, but the GPS management software built on the database does not have this feature.

The database contains several tables that store GPS, route, and location data. The route consists of several places and a serial number. A location is a set of upper and lower bounds for longitudinal longitudinal values ​​attached to a name. The car uploads its GPS location data several times per minute to a server that updates the GPS table.

eg. A specific “FOO” route may consist of the locations “Warehouse”, “School”, “Stadium” and “Park” in order. A “BAR” is essentially the same route, but vice versa (places can be found on several different routes).

A simple choice returns this type of information from the GPS data table (vehicle ID, location, date and time):

34 Warehouse 2011-03-26 18:17:50.000 34 Warehouse 2011-03-26 18:18:30.000 34 Warehouse 2011-03-26 18:19:05.000 34 School 2011-03-26 18:21:34.000 34 School 2011-03-26 18:21:59.000 34 School 2011-03-26 18:22:42.000 34 School 2011-03-26 18:23:55.000 34 Stadium 2011-03-26 18:24:20.000 34 Stadium 2011-03-26 18:24:47.000 34 Park 2011-03-26 18:25:30.000 34 Park 2011-03-26 18:26:50.000 34 Warehouse 2011-03-26 18:28:50.000 

and etc.

From the manual inspection, it is clear that the vehicle 34 traveled the FOO route at least once that day. How can I use SQL to determine the total number of times a route was sent that day to a vehicle?

It seems to me that I will have to use some kind of control structure, but I hope that there will be an easier way. This is SQL used to retrieve information from a GPS table.

 SELECT v.VehicleID, ml.LocationName gps.Time, FROM dbo.Routes r INNER JOIN dbo.RoutePoints rp ON r.RouteId = rp.RouteId INNER JOIN dbo.MapLocations ml ON rp.LocationId = ml.LocationId INNER JOIN dbo.GPSData gps ON ml.LowerRightLatitude < gps.Latitude AND ml.UpperLeftLatitude > gps.Latitude AND ml.UpperLeftLongitude < gps.Longitude AND ml.LowerRightLongitude > gps.Longitude INNER JOIN dbo.Vehicles v ON gps.VehicleID = v.VehicleID WHERE r.Desc = @routename AND gps.Time BETWEEN @startTime AND @endTime ORDER BY v.VehicleId, gps.Time 

EDIT: Rollback is not considered part of the same route. Warehouse, School, Stadium, Park, Warehouse, School, Stadium, Park. these are just two trips on the FOO route. If there is a deviation from a location that is not part of a known route , for example. Warehouse, School, Stadium, Pub, Park, then this place can be ignored. (i.e., it will still be considered the “FOO” route)

Answer 1

 DECLARE @route TABLE ( route INT NOT NULL, step INT NOT NULL, destination INT NOT NULL, PRIMARY KEY (route, step) ) INSERT INTO @route VALUES (1, 1, 1), (1, 2, 2), (1, 3, 3), (1, 4, 4), (2, 1, 3), (2, 2, 4) DECLARE @gps TABLE ( vehicle INT NOT NULL, destination INT NOT NULL, ts DATETIME NOT NULL ) INSERT INTO @gps VALUES (1, 1, '2011-03-30 00:00:00'), (1, 2, '2011-03-30 00:00:01'), (1, 1, '2011-03-30 00:00:02'), (1, 3, '2011-03-30 00:00:03'), (1, 3, '2011-03-30 00:00:04'), (1, 3, '2011-03-30 00:00:05'), (1, 4, '2011-03-30 00:00:06'), (1, 1, '2011-03-30 00:00:07'), (1, 3, '2011-03-30 00:00:08'), (1, 4, '2011-03-30 00:00:09'), (1, 1, '2011-03-30 00:00:10'), (1, 2, '2011-03-30 00:00:11'), (1, 2, '2011-03-30 00:00:12'), (1, 3, '2011-03-30 00:00:13'), (1, 3, '2011-03-30 00:00:14'), (1, 4, '2011-03-30 00:00:15'), (1, 3, '2011-03-30 00:00:16'), (1, 4, '2011-03-30 00:00:17') ; WITH iteration (vehicle, destination, ts, route, edge, step, cnt) AS ( SELECT vehicle, destination, ts, route, 1, step, cnt FROM ( SELECT g.vehicle, r.destination, ts, route, step, cnt, ROW_NUMBER() OVER (PARTITION BY route, vehicle ORDER BY ts) rn FROM ( SELECT *, COUNT(*) OVER (PARTITION BY route) cnt FROM @route ) r JOIN @gps g ON g.destination = r.destination WHERE r.step = 1 ) q WHERE rn = 1 UNION ALL SELECT vehicle, destination, ts, route, edge, step, cnt FROM ( SELECT i.vehicle, r.destination, g.ts, i.route, edge + 1 AS edge, r.step, cnt, ROW_NUMBER() OVER (PARTITION BY i.route, g.vehicle ORDER BY g.ts) rn FROM iteration i JOIN @route r ON r.route = i.route AND r.step = (i.step % cnt) + 1 JOIN @gps g ON g.vehicle = i.vehicle AND g.destination = r.destination AND g.ts > i.ts ) q WHERE rn = 1 ) SELECT route, vehicle, MAX(edge / cnt) FROM iteration GROUP BY route, vehicle 

Here we have two routes: (1, 2, 3, 4) and (3, 4)

The car made 2 trips along the route (1, 2, 3, 4) and 4 trips along the route (3, 4) .

It is important that each route has steps with a number starting with 1 and without spaces (although if this is not the case, you can easily get around it with the optional CTE with ROW_NUMBER() )

Answer 2

So, I submit my attempt to this question for three reasons ...

• To make Quassnoi's answer more elegant,
• Because I spent about two hours, and I do not want to throw it away.
• With the rare possibility of using something earlier than SQL 2005, this can be useful

There are three routes here ...

 Foo: Locations 1, 2, 3, 4 Bar: Locations 4, 3, 2, 1 Quicky: Locations 2, 3 

This query, although long, gives me the correct results for the test data:

 CREATE TABLE #Routes ( RouteID INT, RouteName VARCHAR(50), ) CREATE TABLE #RoutePoints ( RouteID INT, LocationID INT, SequenceNumber INT ) CREATE TABLE #MapLocations ( LocationID INT, LocationName VARCHAR(50) ) CREATE TABLE #GPSData ( VehicleID INT, LocationID INT, Time DATETIME ) INSERT INTO #Routes (RouteID, RouteName) VALUES (1, 'Foo') INSERT INTO #Routes (RouteID, RouteName) VALUES (2, 'Bar') INSERT INTO #Routes (RouteID, RouteName) VALUES (3, 'Quicky') INSERT INTO #MapLocations (LocationID, LocationName) VALUES (1, 'Warehouse') INSERT INTO #MapLocations (LocationID, LocationName) VALUES (2, 'School') INSERT INTO #MapLocations (LocationID, LocationName) VALUES (3, 'Stadium') INSERT INTO #MapLocations (LocationID, LocationName) VALUES (4, 'Park') INSERT INTO #RoutePoints (RouteID, LocationID, SequenceNumber) VALUES (1, 1, 1) INSERT INTO #RoutePoints (RouteID, LocationID, SequenceNumber) VALUES (1, 2, 2) INSERT INTO #RoutePoints (RouteID, LocationID, SequenceNumber) VALUES (1, 3, 3) INSERT INTO #RoutePoints (RouteID, LocationID, SequenceNumber) VALUES (1, 4, 4) INSERT INTO #RoutePoints (RouteID, LocationID, SequenceNumber) VALUES (2, 4, 1) INSERT INTO #RoutePoints (RouteID, LocationID, SequenceNumber) VALUES (2, 3, 2) INSERT INTO #RoutePoints (RouteID, LocationID, SequenceNumber) VALUES (2, 2, 3) INSERT INTO #RoutePoints (RouteID, LocationID, SequenceNumber) VALUES (2, 1, 4) INSERT INTO #RoutePoints (RouteID, LocationID, SequenceNumber) VALUES (3, 2, 1) INSERT INTO #RoutePoints (RouteID, LocationID, SequenceNumber) VALUES (3, 3, 2) INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 1, '2011-03-26 12:17:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 1, '2011-03-26 12:18:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 1, '2011-03-26 12:19:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 2, '2011-03-26 12:20:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 2, '2011-03-26 12:21:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 2, '2011-03-26 12:22:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 2, '2011-03-26 12:23:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 3, '2011-03-26 12:24:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 3, '2011-03-26 12:25:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 1, '2011-03-26 18:17:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 1, '2011-03-26 18:18:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 1, '2011-03-26 18:19:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 2, '2011-03-26 18:20:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 2, '2011-03-26 18:21:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 2, '2011-03-26 18:22:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 2, '2011-03-26 18:23:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 3, '2011-03-26 18:24:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 3, '2011-03-26 18:25:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 4, '2011-03-26 18:26:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 4, '2011-03-26 18:27:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 1, '2011-03-26 18:28:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 1, '2011-03-26 19:17:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 1, '2011-03-26 19:18:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 1, '2011-03-26 19:19:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 2, '2011-03-26 19:20:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 2, '2011-03-26 19:21:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 2, '2011-03-26 19:22:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 2, '2011-03-26 19:23:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 3, '2011-03-26 19:24:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 3, '2011-03-26 19:25:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 4, '2011-03-26 19:26:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 4, '2011-03-26 19:27:50.000') INSERT INTO #GPSData (VehicleID, LocationID, Time) VALUES (1, 1, '2011-03-26 19:28:50.000') CREATE TABLE #GPSRoute ( RouteID INT, VehicleID INT, LocationID INT, SequenceNumber INT, Time DATETIME, StepsInRoute INT ) INSERT INTO #GPSRoute ( RouteID, VehicleID, LocationID, SequenceNumber, Time, StepsInRoute ) SELECT r.RouteID, gps.VehicleID, rp.LocationID, rp.SequenceNumber, gps.Time, ( SELECT COUNT(*) FROM #RoutePoints WHERE RouteID = r.RouteID ) FROM #Routes r JOIN #RoutePoints rp ON r.RouteID = rp.RouteID JOIN #GPSData gps ON gps.LocationID = rp.LocationID SELECT r.RouteID, r.RouteName, previousRouteStep.VehicleID, COUNT(*) / NULLIF(previousRouteStep.StepsInRoute - 1, 0) AS TimesRouteCompleted FROM #Routes r JOIN #GPSRoute previousRouteStep ON r.RouteID = previousRouteStep.RouteID JOIN #GPSRoute nextRouteStep ON previousRouteStep.RouteID = nextRouteStep.RouteID AND previousRouteStep.VehicleID = nextRouteStep.VehicleID AND previousRouteStep.SequenceNumber + 1 = nextRouteStep.SequenceNumber AND previousRouteStep.Time < nextRouteStep.Time AND -- Only include the step if it is followed by the subsequent step. nextRouteStep.Time = ( SELECT MIN(Time) FROM #GPSRoute WHERE RouteID = nextRouteStep.RouteID AND VehicleID = nextRouteStep.VehicleID AND sequenceNumber = nextRouteStep.SequenceNumber AND Time > previousRouteStep.Time ) AND -- Only include the step if it is the latest step in the sequence that meets the criteria. previousRouteStep.Time = ( SELECT MAX(Time) FROM #GPSRoute WHERE RouteID = previousRouteStep.RouteID AND VehicleID = previousRouteStep.VehicleID AND sequenceNumber = previousRouteStep.sequenceNumber AND Time < nextRouteStep.Time ) WHERE -- This step is only valid if every preceding step has been done. NOT EXISTS ( SELECT 1 FROM #RoutePoints rp LEFT JOIN #GPSRoute gpsr ON gpsr.RouteID = rp.RouteID AND gpsr.LocationID = rp.LocationID AND gpsr.SequenceNumber = rp.SequenceNumber AND gpsr.Time < previousRouteStep.Time AND gpsr.VehicleID = previousRouteStep.VehicleID WHERE rp.SequenceNumber < previousRouteStep.SequenceNumber AND gpsr.RouteID IS NULL AND rp.RouteID = previousRouteStep.RouteID ) GROUP BY r.RouteID, r.RouteName, previousRouteStep.VehicleID, previousRouteStep.StepsInRoute