Solved

Twisted SQL Query

Posted on 2010-11-10
17
535 Views
Last Modified: 2012-08-14
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
Comment
Question by:dtechfish
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
  • +3
17 Comments
 
LVL 11

Expert Comment

by:Akenathon
ID: 34104043
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
 
LVL 10

Expert Comment

by:Tyler Laczko
ID: 34104100
You will need to select from the zones table two times. Once for the entry once for the exit.
0
 
LVL 4

Expert Comment

by:greyfairer
ID: 34104114
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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

Author Comment

by:dtechfish
ID: 34105705
Thank you all.  Greyfairer, this looks like the answer.  I am waiting for test data.
0
 
LVL 25

Expert Comment

by:jrb1
ID: 34105738
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
 

Author Comment

by:dtechfish
ID: 34107663
Well, I guess they could enter and leave the zone multiple times.  I just need to pull the latest zone entry and exit.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 34108705
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
 
LVL 25

Expert Comment

by:jrb1
ID: 34112562
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
 

Author Comment

by:dtechfish
ID: 34112710
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
 
LVL 25

Accepted Solution

by:
jrb1 earned 500 total points
ID: 34113264
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 34114250
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
 

Author Comment

by:dtechfish
ID: 34146756
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 34147180
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
 

Author Comment

by:dtechfish
ID: 34147579
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
 
LVL 25

Expert Comment

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

UnitName, ZoneName, Timestamp (descending)

0
 

Author Comment

by:dtechfish
ID: 34148078
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
 
LVL 25

Expert Comment

by:jrb1
ID: 34168355
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question