Join Percona’s Senior Technical Services Engineer, Adamo Tonete as he presents “MongoDB Index Types, How, When and Where Should They be Used?” on Wednesday, July 12, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).
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
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.