I have 2 tables relating to tracking a Lorry/Truck trip
1. TRIP- start and end Dates/ID's
2. TRACKING_DATA- This contains all data i.e. start time/position, end time/position, and in-between time/positions
I want to be able to EXEC an SP every 30 seconds to update the TRIP table, it will look at the TRIP Table WHERE END ID is NULL - and pull the correct ID/DATE from the TRACKINGDATA table
Here is a pic of the trip table:
There are 2 trips which aren't "completed" so i need to look up the "Ignition Off" Event in the TrackingData Table (ReportId = 8)
Here is the TrackingData Table
It show Ignition on and Ignition off
The selected Row is the Ignition Off for the bottom row of first image.
Essentially, i just need to "complete" the trip in the top table with the information from selected row of 2nd image
So I guess i would need something like this
Select * from tblTrip_Master
Where nEndEventInst = NULL
-- Loop through each result and get the correct Ignition Off ID from TrackingData table
-- HOWEVER....IF the Ignition Off ID has already been used, do NOT re use it to "complete" a trip)!
-- The vSequenceID's aren't reliable enough (they dont always come in order), so we have order by dGPSDateTime
--on current loop:
Update current Trip row
where dGpsDateTime > dStartTime
And DeviceID = currentRowDeviceID
AND vReportID = 8
AND vSequenceID(This is the row we are picking and think has the correct END ID) does not already exist in tblTrip_Master.nEndEventIn
st - i.e. it hasn't been used before
The above is what i need initally
However, its worth mentioning, that due to trucks going out of Cellular signal and other factors, sometimes the "correct" igntion off ID may not be in the TrackingData table when this SP runs..... but a later ignition off might be.... this would cause the trip table to go out of sync as it would marry the wrong ignition on and off
...however, this is out of my control....so i was thinking of having some sort of CLEANING AGENT which grabs the start and end GPSdate ... and looks up if any other ignition offs are inbetween these for the deviceID.... and then tries to sort it out.....however, i'd like to get the first part working first, and can tackle this bit later
Any help appreciated!
I've uploaded a .bak file of the stripped down database with these 2 table....get it here: Download bak file
the correct table name/columns are here
,vpkDeviceID as DeviceID
,vpkDeviceID as DeviceId
I've tried doing something similar to what's explained here, but its not working so i'm taking a new approach and hoping this will work.....