Мне нужно определить, сколько раз был пройден определенный маршрут для каждого автомобиля в заданном определенный диапазон дат, но программное обеспечение для управления GPS, построенное на основе базы данных, не имеет этой функции.
База данных содержит несколько таблиц, в которых хранятся данные GPS, маршрута и местоположения. Маршрут состоит из нескольких мест и порядкового номера. Местоположение - это набор значений широты / долготы верхней и нижней границы, привязанный к имени. Транспортное средство загружает данные о своем местоположении по GPS несколько раз в минуту на сервер, который обновляет таблицу GPS.
например. Определенный маршрут «FOO» может состоять из локаций «Склад», «Школа», «Стадион» и «Парк» по порядку. «BAR» - это, по сути, тот же маршрут, но в обратном порядке (местоположения могут быть найдены на нескольких разных маршрутах).
Простой выбор возвращает этот тип информации из таблицы данных GPS (идентификатор транспортного средства, местоположение, дата и время):
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
и т. д.
При ручном осмотре видно, что ТС 34 проезжал маршрут «FOO» хотя бы один раз в тот день. Как я могу использовать SQL, чтобы определить общее количество раз, когда этот маршрут был пройден в тот день для одного транспортного средства?
У меня такое чувство, что мне придется использовать какую-то структуру прерывания контроля, но я надеюсь, что это произойдет ' s более простой способ. Это SQL, используемый для извлечения информации из таблицы GPS.
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: Возврат не считается частью одного и того же маршрута. Склад, школа, стадион, парк, склад, школа, стадион, парк . всего две поездки по маршруту FOO. Если есть отклонение от места, которое не является частью известного маршрута , например. Склад, Школа, Стадион, Паб, Парк , то это местоположение можно игнорировать. (т.е. он по-прежнему будет считаться маршрутом "FOO")