Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Update SP - check if value exists first

Posted on 2014-11-21
9
268 Views
Last Modified: 2015-05-06
Hi

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

Open in new window


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

Open in new window



how might I structure this?
0
Comment
Question by:websss
  • 3
  • 3
9 Comments
 
LVL 33

Expert Comment

by:ste5an
ID: 40456883
So your UPDATE sproc effects multiple rows in tm? Then imho the condition AND td.vpkDeviceID = tm.vpkDeviceID in the CROSS APPLY is not sufficient.

Please post a concise and complete example. This means table DDL and sample data INSERT statements. In this case please explain also the model.
0
 

Author Comment

by:websss
ID: 40456897
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
/****** 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

Open in new window



as for the Model
[sp_Trip_UpdateIgnitionOff] gets called by an agent every x secs as sometimes there is a delay waiting for records to be entered into the tblCommonTrackingData table
the devices (tracking hardware) fire ignition on and ignition off
The purpose of [sp_Trip_UpdateIgnitionOff] is to marry the ignition off with correct ignition on the TRIP table
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 ?
0
 
LVL 33

Expert Comment

by:ste5an
ID: 40457018
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;

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:websss
ID: 40457086
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?
0
 
LVL 33

Accepted Solution

by:
ste5an earned 500 total points
ID: 40457148
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())

Open in new window

0
 

Author Comment

by:websss
ID: 40457236
Hi Ste5an

Actually, I dont think the TripEndDate is the issue

see here for an example of records we need to update
captrip
the issue I have is the nEndEventInst gets inserted multiple times... so i would first need to check if the ID has been used
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40762098
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question