Twisted SQL Query

You need to include the zones table twice, one for the enter event and the other one for the exit event:

select ...
from vehicles, gpsunits, lastposition, ZONES ENTRYZONE, ZONES EXITZONE
etc.

It should be easy once you base the query on that idea. Note you might do the same with some other tables: lastposition might also need to appear twice if you have one row for ENTER_LASTPOSITION and EXIT_LASTPOSITION.

You will need to select from the zones table two times. Once for the entry once for the exit.

SELECT v.vehicleName, z1.Timestamp as EntranceTime, z2.TimeStamp asExitTime, z2.Timestamp - z1.TimeStamp as TimeInLoading, p.Latitude, p.Longitude, p.Speed
FROM Vehicles v
JOIN GPSUnits g on v.idVehicle = g.idVehicle
JOIN Zones z1 on (z1.UnitName = g.UnitName and z1.ZoneName='EnterLoading')
JOIN Zones z2 on (z2.UnitName = g.UnitName and z2.ZoneName='ExitLoading')
JOIN LastPositon p on p.UnitName=g.UnitName
WHERE v.vehicleType = @VehicleType and v.vehicleGroup = @vehicleGroup

Thank you all.  Greyfairer, this looks like the answer.  I am waiting for test data.

I was assuming you could enter and leave multiple times.  That would work if you will only have one Enter and one Exit row.

Well, I guess they could enter and leave the zone multiple times.  I just need to pull the latest zone entry and exit.

And joining that way wont do that for you...

nearly there, but if z2 is exit then need to make sure the datetime is greater than the current entry - other wise you end of with previous ones, and ones yet to be entered as well...

The one thing that is missing is linking zones to position - sure they have the same unitname, but either they use the sametime stamp  (or id is not quite evident...) as the entry point into that "zone" - meaning those time stamps will match.

So, lets assume that Zones matches positions by timestamp coincedences... Meaning the exit is the greatest timestamp for that specific location (on that day - unless they can return there again).

Would be good to understand the number / frequency of data points in "lastPosition".

Given the following :

   1.  Vehicles - idVehicle, vehicleName, vehicleType, vehicleGroup
   2.  GPSUnits - idUnitAssign, UnitName, idVehicle
   3.  Zones - idPosition, ZoneName, UnitName, TimeStamp
   4.  LastPosition - UnitName, Latitude, Longitude, Speed, TimeStamp

would be inclined to do something more like :


SELECT V.VehicleName, L.EnterPosition, L.ExitPosition, ZE.timestamp as EnterZoneTime, ZX.timestamp as ExitZoneTime, datediff(seconds, ZE.timestamp, ZX.timestamp) as Time_In_Zone, L.Latitude, L.Longitude, L.Speed as CurrentSpeed  

FROM Vehicles V
INNER JOIN GPSUnits G on G.idvehicle = V.idvehicle
INNER JOIN (select UnitName, Latitude, Longitude, avg(Speed) as speed, min(TimeStamp) as EnterPosition, max(TimeStamp) as ExitPosition from LastPosition group by UnitName, Latitude, Longitude) L on L.UnitName = V.unitName
Inner join Zones ZE on ZE.unitname = L.unitname and ZE.timestamp between L.EnterPosition and L.ExitPosition and ZE.ZoneName='EnterLoading'
Inner join Zones ZX on ZX.unitname = L.unitname and ZX.timestamp between L.EnterPosition and L.ExitPosition and ZX.ZoneName='ExitLoading'

WHERE Vehicles.vehicleType = @VehicleType and Vehicles.vehicleGroup = @vehicleGroup


If you could provide some dummy data it would help significantly...

Here is a tweak to the earlier SQL, accounting for the possible entrances and exits.

SELECT v.vehicleName, z1.Timestamp as EntranceTime, z2.TimeStamp asExitTime, z2.Timestamp - z1.TimeStamp as TimeInLoading, p.Latitude, p.Longitude, p.Speed
FROM Vehicles v
JOIN GPSUnits g on v.idVehicle = g.idVehicle
JOIN Zones z1 on (z1.UnitName = g.UnitName and z1.ZoneName='EnterLoading')
JOIN Zones z2 on (z2.UnitName = g.UnitName and z2.ZoneName='ExitLoading')
JOIN LastPositon p on p.UnitName=g.UnitName
WHERE v.vehicleType = @VehicleType and v.vehicleGroup = @vehicleGroup
and z1.Timestamp = (select max(z1a.Timestamp) from Zones z1a where z1a.UnitName = z1.UnitName and z1a.ZoneName = z1.ZoneName and z1a.Timestamp < z2.Timestamp)
and z2.Timestamp = (select max(z2a.Timestamp) from Zones z2a where z2a.UnitName = z2.UnitName and z2a.ZoneName = z2.ZoneName)

Mark,

I see your point.

Test Data is Attached.  You will see that the trucks move in and out of the zones.  I am only interested in the last entrance and exit and dont care about any earlier movements.  

Thanks,

TestData.xls

I see that there is probably no real relationship other than DATE (as opposed to datetime) and UnitName.

Thats because lastpostion is really the very last position :)

So, what about considering a couple of inline queries ? does this give you the basic results ? If it does then we can also consider changing the inline queries to joins...


SELECT V.VehicleName, L.timestamp, L.Latitude, L.Longitude, L.Speed as CurrentSpeed,
(select max(timestamp) from Zones Z on Z.unitname = L.unitname and cast(Z.timestamp as date) = cast (L.timestamp as date) and Z.ZoneName='LoadingZoneIn') as EnterLoading,
(select max(timestamp) from Zones Z on Z.unitname = L.unitname and cast(Z.timestamp as date) = cast (L.timestamp as date) and Z.ZoneName='LoadingZoneOut') as ExitLoading

FROM Vehicles V
INNER JOIN GPSUnits G on G.idvehicle = V.idvehicle
INNER JOIN LastPosition L on L.UnitName = V.unitName
WHERE V.vehicleType = @VehicleType and V.vehicleGroup = @vehicleGroup

JRB1...Your solution is giving the correct results when I add a group by statement, but it is reacting really slow.  Is there any way to speed up this query?

Because of the condition in the WHERE clause.

Did you ever try my query ? Any reason why you didnt respond to my questions ? Just curious...

Thought I might have got an assist for helping to point all the other experts on the right path (which was clearly missing before hand) :)

Mark...I did try it.  I could not get it to work.  I tried to tweak it but I couldn't get it to pull data.

I would think that indexing will be key to performance.  On zones, maybe a primary key of:

UnitName, ZoneName, Timestamp (descending)

Thanks for the response.  I have access to the DB but cannot alter table.  Is there a way to speed it up just through the query.  It will work for now but it takes 30 sec. for the page to load.

Can you see what primary keys exist, if any?  We might be able to add some join criteria to the query to make it run faster.