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 > tm.nStartEventInst AND
td.dGPSDateTime >= tm.dStartTime and
td.vpkDeviceID = tm.vpkDeviceID and
td.dGPSDateTime < 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.
Our community of experts have been thoroughly vetted for their expertise and industry experience.