ALTER PROCEDURE [dbo].[sp_Trip_UpdateIgnitionOff] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here UPDATE tm SET nEndEventInst = td_outer.vSequenceId, dEndTime = td_outer.dGPSDateTime FROM tblTrip_Master tm CROSS APPLY ( SELECT TOP (1) td.vSequenceId, td.dGPSDateTime FROM tblCommonTrackingData td WHERE td.vReportId = 8 and --td.vSequenceId &gt; tm.nStartEventInst AND td.dGPSDateTime &gt;= tm.dStartTime and td.vpkDeviceID = tm.vpkDeviceID and td.dGPSDateTime &lt; DATEADD(SECOND, -30, SYSDATETIMEOFFSET()) --ORDER BY td.vSequenceId ORDER BY td.dGPSDateTime, td.vSequenceId ) AS td_outer WHERE tm.nEndEventInst IS NULL END
WHERE tm.nEndEventInst IS NULL --here is the bit i think needs changing AND --do any rows with tblTrip_Master.nEndEventInst already exist (also need to pass in [vpkDeviceID] with this) (if exists: Select count(*) from tblTrip_Master where tblTrip_Master.vpkDeviceID = td.vpkDeviceID AND tblTrip_Master.nEndEventInst = tm.nEndEventInst If COUNT = 0 then proceed with update Else - exit without updating
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”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.