Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 547
  • Last Modified:

Twisted SQL Query

Hello Experts,

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!
0
dtechfish
Asked:
dtechfish
  • 6
  • 5
  • 3
  • +3
1 Solution
 
AkenathonCommented:
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.
0
 
Tyler LaczkoCommented:
You will need to select from the zones table two times. Once for the entry once for the exit.
0
 
greyfairerCommented:
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dtechfishAuthor Commented:
Thank you all.  Greyfairer, this looks like the answer.  I am waiting for test data.
0
 
jrb1Commented:
I was assuming you could enter and leave multiple times.  That would work if you will only have one Enter and one Exit row.
0
 
dtechfishAuthor Commented:
Well, I guess they could enter and leave the zone multiple times.  I just need to pull the latest zone entry and exit.
0
 
Mark WillsTopic AdvisorCommented:
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...
0
 
jrb1Commented:
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)
0
 
dtechfishAuthor Commented:
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
0
 
jrb1Commented:
With that test data, my query gives this result:


vehicleName      EntranceTime      ExitTime      TimeInLoading      Latitude      Longitude      Speed
SouthQuad2      11/11/2010 11:31:40 AM      11/11/2010 4:41:40 PM      0.215277777781012      25.656514      -80.381438      42.1 mph
SouthQuad1      11/11/2010 2:22:40 PM      11/11/2010 5:02:40 PM      0.111111111109494      25.646575      -80.394099      25.5 mph

2 Vehicles are returned with the correct entrance and exit times, and I believe the other fields are correct as well.  Here is the updated SQL with those table/column names:

SELECT v.vehicleName, z1.Timestamp AS EntranceTime, z2.TimeStamp AS ExitTime, z2.Timestamp-z1.TimeStamp AS TimeInLoading, p.Latitude, p.Longitude, p.Speed
FROM Vehicles v
INNER JOIN GPSUnits g
ON v.idVehicle=g.idVehicle
INNER JOIN Zones AS z1
ON z1.UnitName=g.UnitName
AND z1.ZoneName='LoadingZoneIn'
INNER JOIN Zones AS z2
ON z2.UnitName=g.UnitName
AND z2.ZoneName='LoadingZoneOut'
INNER JOIN LastPosition AS 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);
0
 
Mark WillsTopic AdvisorCommented:
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

0
 
dtechfishAuthor Commented:
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?
0
 
Mark WillsTopic AdvisorCommented:
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) :)
0
 
dtechfishAuthor Commented:
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.
0
 
jrb1Commented:
I would think that indexing will be key to performance.  On zones, maybe a primary key of:

UnitName, ZoneName, Timestamp (descending)

0
 
dtechfishAuthor Commented:
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.
0
 
jrb1Commented:
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.
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 6
  • 5
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now