Solved

Twisted SQL Query

Posted on 2010-11-10
17
521 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
  • 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
 

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now