troubleshooting Question

Loop through one table and update data in another table

Avatar of websss
websssFlag for Kenya asked on
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
35 Comments2 Solutions966 ViewsLast Modified:
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:
trip3There 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
Trip4The 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.nEndEventInst - 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
SELECT ipkTripID
      ,dStartTime
      ,dEndTime
      ,nStartEventInst
      ,nEndEventInst
      ,vpkDeviceID as DeviceID
  FROM tblTrip_Master

SELECT ipkCommanTrackingID
      ,dGPSDateTime
      ,vSequenceID
      ,vReportID
	  ,vEventName
      ,vpkDeviceID as DeviceId
  FROM tblCommonTrackingData



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.....
ASKER CERTIFIED SOLUTION
Koen Van Wielink
Business Intelligence Specialist

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 35 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 35 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros