Twisted SQL Query
Posted on 2010-11-10
I have a sql query I am trying to build that I may be overthinking. Please help.
Here is the SItuation...I am working on a GPS tracking system for my company and we want to track the time the Trucks are in a holding or loading zone.
I have 4 tables.
1. Vehicles - idVehicle, vehicleName, vehicleType, vehicleGroup
2. GPSUnits - idUnitAssign, UnitName, idVehicle
3. Zones - idPosition, ZoneName, UnitName, TimeStamp
4. LastPosition - UnitName, Latitude, Longitude, Speed, TimeStamp
The part that throws me is that the Zones table holds an EntranceZone and an ExitZone for each area. For example, when a vehicle enters the loading zone, an entry is made in the Zones table with a ZoneName of EnterLoading. When a vehicle exits the loading Zone, an entry is made in the Zones table with a ZoneName of ExitLoading.
I need a query that pulls:
VehicleName, EntranceTime, ExitTime, Time in Loading, Current Position (Lat, Long), Current Speed
WHERE Vehicles.vehicleType = @VehicleType and Vehicles.vehicleGroup = @vehicleGroup
Thanks in advance!