asked on

SP FOR Loop?

I have 2 tables
1. tblTrip_Master
2. tblTrackingData

The columns are in trip master are
[ipkTripID]
,[dStartTime]
,[dEndTime]
,[nStartEventInst]
,[nEndEventInst]
,[vpkDeviceID]

I need a stored procedure to select all records where: nEndEventInst IS NULL (on tblTrip_Master)
then update all these records in TRIP table with data found in the tblTrackingData table

i.e.
--get correct END ID
set @EndID = (select top(1) vSequenceId
from tblTrackingData
where vReportId = 8
and vSequenceId > tblTrip_Master.nStartEventInst
Order by vSequenceId)

Update tblTrip_Master
Set nEndEventInst = @EndID
-- , dEndTime = @EndTIme

*I need to also get the EndTime using the first statement, not sure how i would do that without doing separate selects?

Can someone help me get it working/started

I'm not sure how to go about this.... there could be 1 - 100 records in tblTrip_Master that will need updating everytime its run

I hope there is enough info here to explain myself

any help appreciated

Kyle Abrahams, PMP

Sounds like a perfect use for a cursor:
http://stevestedman.com/2013/04/t-sql-a-simple-example-using-a-cursor/

websss

ASKER

Thanks, sounds interesting

Performance /locking wise, are there any issues

are there any better ways of doing it?, I just want to know all the options first!

Vitor Montalvão

I will avoid cursors. They aren't good for performance.
Here's the code for first update:

UPDATE tblTrip_Master m
SET nEndEventInst = (SELECT MAX(d.vSequenceId)
			FROM tblTrackingData d
			WHERE d.vReportId = 8 and d.vSequenceId  > m.nStartEventInst)
WHERE m.nEndEventInst IS NULL

Open in new window

How will you get the EndTime? You can use a similar solution as above one.

websss

ASKER

Thanks Vitor

EndTime comes from:
tblTrackingData.GPSDate
And will be on the same row as tblTrackingData.vSequenceId

How would I change the code to reflect this?

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

awking00

Can you describe tblTrackingData?