Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
Update SP - check if value exists first
Hi
I have the following SP
However, i've noticed an error in my data ... nEndEventInst is being put in multiple rows
It should only appear once in the table
What i need to say is something like this
how might I structure this?
I have the following SP
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
However, i've noticed an error in my data ... nEndEventInst is being put in multiple rows
It should only appear once in the table
What i need to say is something like this
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
how might I structure this?
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Yes it affects multiple rows
do you think I should loop through and update one at a time so it can look up previous values?
here is the create for both tables
as for the Model
[sp_Trip_UpdateIgnitionOff
the devices (tracking hardware) fire ignition on and ignition off
The purpose of [sp_Trip_UpdateIgnitionOff
These events are unique, but currently Ignition off ID is being re-used on the trip table... so the TRIP table is getting messed up with incorrect Trip END (ignition Off) data
...does that help ?
do you think I should loop through and update one at a time so it can look up previous values?
here is the create for both tables
/****** Object: Table [dbo].[tblTrip_Master] Script Date: 21/11/2014 12:55:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblTrip_Master](
[ipkTripID] [int] IDENTITY(1,1) NOT NULL,
[dStartTime] [datetime] NULL,
[dEndTime] [datetime] NULL,
[iMaxSpeed] [int] NULL,
[iDistance] [float] NULL,
[iAverageSpeed] [int] NULL,
[iTripType] [int] NULL,
[nStartEventInst] [numeric](18, 0) NULL,
[nEndEventInst] [numeric](18, 0) NULL,
[vpkDeviceID] [varchar](50) NULL,
[DriverIDNo] [nvarchar](100) NULL,
[ifkDriverID] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tblCommonTrackingData] Script Date: 21/11/2014 12:55:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblCommonTrackingData](
[ipkCommanTrackingID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[vSequenceID] [numeric](18, 0) NULL,
[vpkDeviceID] [varchar](50) NULL,
[vLongitude] [varchar](50) NULL,
[vLatitude] [varchar](50) NULL,
[vHeading] [int] NULL,
[vReportID] [int] NULL,
[vOdometer] [numeric](8, 2) NULL,
[vVehicleSpeed] [int] NULL,
[vTextMessage] [varchar](max) NULL,
[ifkDriverID] [int] NULL,
[bIsIgnitionOn] [bit] NULL,
[vEventName] [varchar](100) NULL,
[FuelVoltage] [float] NULL,
[vDistance] [float] NULL,
[bIsEngineOn] [bit] NULL,
[IsAlert] [varchar](50) NULL,
[dGPSDateTime] [datetime] NULL,
[cInput1] [char](1) NULL,
[cInput2] [char](1) NULL,
[cInput3] [char](1) NULL,
[cInput4] [char](1) NULL,
[cInput5] [char](1) NULL,
[cInput6] [char](1) NULL,
[vTempSensor1] [varchar](20) NULL,
[vTempSensor2] [varchar](20) NULL,
[iRpm] [int] NULL,
[nAltitude] [nvarchar](50) NULL,
[iHdop] [int] NULL,
[iBatteryBackup] [int] NULL,
[vFuelDigital] [varchar](50) NULL,
[vAnalog1] [varchar](20) NULL,
[vAnalog2] [varchar](20) NULL,
[cOutput1] [char](1) NULL,
[cOutput2] [char](1) NULL,
[iSoftwarePriority] [tinyint] NOT NULL DEFAULT ((0)),
[vRoadSpeed] [varchar](10) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
as for the Model
[sp_Trip_UpdateIgnitionOff
the devices (tracking hardware) fire ignition on and ignition off
The purpose of [sp_Trip_UpdateIgnitionOff
These events are unique, but currently Ignition off ID is being re-used on the trip table... so the TRIP table is getting messed up with incorrect Trip END (ignition Off) data
...does that help ?
Ok, I'll think I've found it: You'll need to use the trip end date/time in the CROSS APPLY. E.g.
ALTER PROCEDURE [dbo].[sp_Trip_UpdateIgnitionOff]
AS
SET NOCOUNT ON;
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.dGPSDateTime >= tm.dStartTime
AND td.dGPSDateTime <= tm.dEndTime -- Caveat: Overlapping possible.
AND td.vpkDeviceID = tm.vpkDeviceID
AND td.dGPSDateTime < DATEADD(SECOND, -30, SYSDATETIMEOFFSET())
ORDER BY td.dGPSDateTime ,
td.vSequenceId
) AS td_outer
WHERE tm.nEndEventInst IS NULL;
thanks
I tried that but the table is no longer being updated... there was no error when exec the SP
...just results not getting updated
The GpsDatetime of this update will actually be the dEndTime (we use that value for dEndTime)
...not really sure if that helps?
I tried that but the table is no longer being updated... there was no error when exec the SP
...just results not getting updated
The GpsDatetime of this update will actually be the dEndTime (we use that value for dEndTime)
...not really sure if that helps?
Please post some sample data. Imho it's a data/model problem. Thus it looks like we need another approach to determine the trip end date.
td.vReportId = 8 AND
td.dGPSDateTime >= tm.dStartTime AND
td.vpkDeviceID = tm.vpkDeviceID AND
td.dGPSDateTime < DATEADD(SECOND, -30, SYSDATETIMEOFFSET())
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
Hi Ste5an
Actually, I dont think the TripEndDate is the issue
see here for an example of records we need to update
the issue I have is the nEndEventInst gets inserted multiple times... so i would first need to check if the ID has been used
Actually, I dont think the TripEndDate is the issue
see here for an example of records we need to update
the issue I have is the nEndEventInst gets inserted multiple times... so i would first need to check if the ID has been used
I've requested that this question be deleted for the following reason:
Not enough information to confirm an answer.
Not enough information to confirm an answer.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server
From novice to tech pro — start learning today.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
Please post a concise and complete example. This means table DDL and sample data INSERT statements. In this case please explain also the model.