?
Solved

SQL Trigger - If statement

Posted on 2014-11-19
18
Medium Priority
?
188 Views
Last Modified: 2014-11-21
Hi

I have the following in a trigger
	@endDistance = (SELECT TOP(1) vOdometer FROM tblCommonTrackingData WHERE vSequenceID = i.nEndEventInst and vpkDeviceID = i.vpkDeviceID order by ipkCommanTrackingID desc )
	,@startDistance = (SELECT TOP(1) vOdometer FROM tblCommonTrackingData WHERE vSequenceID = i.nStartEventInst  and vpkDeviceID = i.vpkDeviceID order by ipkCommanTrackingID desc )

Open in new window


If the value of @endDistance and @startDistance is 0

i would like have the following instead

@endDistance = (SELECT TOP(1) vOdometer FROM tblCommonTrackingData WHERE vSequenceID = i.nEndEventInst and vpkDeviceID = i.vpkDeviceID order by ipkCommanTrackingID desc )
if @endDistance = 0 then
@endDistance = (similar select statement, only choose the the Previous vSequenceID that exists)

....and same for @startDistance but instead of PREVIOUS record, choose NEXT record

how would i do this?
0
Comment
Question by:websss
[X]
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
  • 9
  • 8
18 Comments
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40452069
Just add the criteria to not include the previous vOdomoter (and vOdometer<>@endDistance):
if @endDistance = 0 then
	@endDistance = (SELECT TOP(1) vOdometer FROM tblCommonTrackingData WHERE vSequenceID = i.nEndEventInst and vpkDeviceID = i.vpkDeviceID and vOdometer<>@endDistance order by ipkCommanTrackingID desc )

Open in new window

0
 

Author Comment

by:websss
ID: 40452081
thanks
the issue with the sql statement is this bit:
i.e.
vSequenceID = 39580

It will always return 0 for odometer

What I need to do is select the PREVIOUS one (i.e. 39579)
0
 

Author Comment

by:websss
ID: 40452087
See here ...the bottom one is what i would need if @endDistance = 0

vsequenceid issue
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40452089
I see. What about using the criteria vSequenceID = (i.nEndEventInst-1)?
if @endDistance = 0 then
	@endDistance = (SELECT TOP(1) vOdometer FROM tblCommonTrackingData WHERE vSequenceID = (i.nEndEventInst-1) and vpkDeviceID = i.vpkDeviceID order by ipkCommanTrackingID desc )

Open in new window

0
 

Author Comment

by:websss
ID: 40452154
yes that might work,

how would i put it in the UPDATE:

i.e.

UPDATE tm
set 
	
	@endDistance = (SELECT TOP(1) vOdometer FROM tblCommonTrackingData WHERE vSequenceID = i.nEndEventInst and vpkDeviceID = i.vpkDeviceID order by ipkCommanTrackingID desc )
	,@startDistance = (SELECT TOP(1) vOdometer FROM tblCommonTrackingData WHERE vSequenceID = i.nStartEventInst  and vpkDeviceID = i.vpkDeviceID order by ipkCommanTrackingID desc )
	,iDistance = (@endDistance - @startDistance)

Open in new window

0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40452167
You need to verify first and update after. You can't do it in the same step.
0
 

Author Comment

by:websss
ID: 40452175
hmm
this doesn't seem to work

	@endDistance = (if (SELECT TOP(1) vOdometer FROM tblCommonTrackingData WHERE vSequenceID = i.nEndEventInst and vpkDeviceID = i.vpkDeviceID order by ipkCommanTrackingID desc) = 0 then (SELECT TOP(1) vOdometer FROM tblCommonTrackingData WHERE vSequenceID = (i.nEndEventInst-1) and vpkDeviceID = i.vpkDeviceID order by ipkCommanTrackingID desc ) )

Open in new window

0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40452186
Can't be like that but first tell which columns do you want to update, since by your example then only column being updated is iDistance. @endDistance and @startDistance aren't table columns but variables.
0
 

Author Comment

by:websss
ID: 40452285
here is the full trigger:

ALTER TRIGGER [dbo].[tblTrip_Master__TRG_UPD]
ON [dbo].[tblTrip_Master]
AFTER UPDATE
AS
SET NOCOUNT ON;

			declare @SecondsInHour float = 3600
			declare @startDistance float = 0
			declare @endDistance float = 0


UPDATE tm
  
set 
	
	@endDistance = (SELECT TOP(1) vOdometer FROM tblCommonTrackingData WHERE vSequenceID = i.nEndEventInst and vpkDeviceID = i.vpkDeviceID order by ipkCommanTrackingID desc )
	--@endDistance = (if (SELECT TOP(1) vOdometer FROM tblCommonTrackingData WHERE vSequenceID = i.nEndEventInst and vpkDeviceID = i.vpkDeviceID order by ipkCommanTrackingID desc) = 0 then (SELECT TOP(1) vOdometer FROM tblCommonTrackingData WHERE vSequenceID = (i.nEndEventInst-1) and vpkDeviceID = i.vpkDeviceID order by ipkCommanTrackingID desc ) )
	,@startDistance = (SELECT TOP(1) vOdometer FROM tblCommonTrackingData WHERE vSequenceID = i.nStartEventInst  and vpkDeviceID = i.vpkDeviceID order by ipkCommanTrackingID desc )
	,iDistance = (@endDistance - @startDistance)
 
	,iAverageSpeed = CASE WHEN DATEDIFF(second, i.dStartTime, i.dEndTime) > 0
		THEN ((SELECT TOP(1) vOdometer FROM tblCommonTrackingData WHERE vSequenceID = i.nEndEventInst      and vpkDeviceID = i.vpkDeviceID order by ipkCommanTrackingID desc                 ) - (SELECT TOP(1) vOdometer FROM tblCommonTrackingData WHERE vSequenceID = i.nStartEventInst      and vpkDeviceID = i.vpkDeviceID order by ipkCommanTrackingID desc)) / (convert(float, DATEDIFF(second, i.dStartTime, i.dEndTime)) / @SecondsInHour) 
			ELSE 999 
			END
	,iMaxSpeed  = (SELECT TOP(1) vVehicleSpeed FROM tblCommonTrackingData WHERE vSequenceID >= i.nStartEventInst and  vSequenceID <= i.nEndEventInst and vpkDeviceID = i.vpkDeviceID order by vVehicleSpeed desc)		

	
FROM tblTrip_Master tm
INNER JOIN inserted i ON
    i.ipkTripID = tm.ipkTripID --AND i.key_col2 = tm.key_col2
WHERE
    i.dEndTime IS NOT NULL AND 
    i.nEndEventInst IS NOT NULL AND
    i.dStartTime >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 13, 0)

Open in new window

0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40452298
I think you won't need to update any column with the @endDistance and @startDistance values, so this may be what you are looking for:
ALTER TRIGGER [dbo].[tblTrip_Master__TRG_UPD]
ON [dbo].[tblTrip_Master]
AFTER UPDATE
AS
SET NOCOUNT ON;

			declare @SecondsInHour float = 3600
			declare @startDistance float = 0
			declare @endDistance float = 0

@endDistance = (SELECT TOP(1) vOdometer FROM tblCommonTrackingData WHERE vSequenceID = i.nEndEventInst and vpkDeviceID = i.vpkDeviceID order by ipkCommanTrackingID desc)
if @endDistance = 0 then
	@endDistance = (SELECT TOP(1) vOdometer FROM tblCommonTrackingData WHERE vSequenceID = (i.nEndEventInst-1) and vpkDeviceID = i.vpkDeviceID order by ipkCommanTrackingID desc )

@startDistance = (SELECT TOP(1) vOdometer FROM tblCommonTrackingData WHERE vSequenceID = i.nStartEventInst  and vpkDeviceID = i.vpkDeviceID order by ipkCommanTrackingID desc)


UPDATE tm
SET
	iDistance = (@endDistance - @startDistance) 
	,iAverageSpeed = CASE WHEN DATEDIFF(second, i.dStartTime, i.dEndTime) > 0
		THEN (@endDistance - @startDistance) / (convert(float, DATEDIFF(second, i.dStartTime, i.dEndTime)) / @SecondsInHour) 
			ELSE 999 
			END
	,iMaxSpeed  = (SELECT TOP(1) vVehicleSpeed FROM tblCommonTrackingData WHERE vSequenceID >= i.nStartEventInst and  vSequenceID <= i.nEndEventInst and vpkDeviceID = i.vpkDeviceID order by vVehicleSpeed desc)	
FROM tblTrip_Master tm
INNER JOIN inserted i ON
    i.ipkTripID = tm.ipkTripID --AND i.key_col2 = tm.key_col2
WHERE
    i.dEndTime IS NOT NULL AND 
    i.nEndEventInst IS NOT NULL AND
    i.dStartTime >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 13, 0)

Open in new window

0
 

Author Comment

by:websss
ID: 40452347
I think the i.nEndEVentIsnt etc can't go above the UPDATE, see here
triggererror
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40452355
I forgot to add the Inserted table when I took it out of the Update statement:
ALTER TRIGGER [dbo].[tblTrip_Master__TRG_UPD]
ON [dbo].[tblTrip_Master]
AFTER UPDATE
AS
SET NOCOUNT ON;

			declare @SecondsInHour float = 3600
			declare @startDistance float = 0
			declare @endDistance float = 0

SELECT TOP(1) @endDistance=vOdometer 
FROM tblCommonTrackingData
	INNER JOIN inserted i ON i.ipkTripID = tm.ipkTripID 
WHERE vSequenceID = i.nEndEventInst and vpkDeviceID = i.vpkDeviceID 
order by ipkCommanTrackingID desc

if @endDistance = 0
	SELECT TOP(1) @endDistance=vOdometer 
	FROM tblCommonTrackingData
		INNER JOIN inserted i ON i.ipkTripID = tm.ipkTripID 
	WHERE vSequenceID = (i.nEndEventInst-1) and vpkDeviceID = i.vpkDeviceID 
	order by ipkCommanTrackingID desc
	
SELECT TOP(1) @startDistance = vOdometer 
FROM tblCommonTrackingData
	INNER JOIN inserted i ON i.ipkTripID = tm.ipkTripID 
WHERE vSequenceID = i.nStartEventInst  and vpkDeviceID = i.vpkDeviceID 
order by ipkCommanTrackingID desc

UPDATE tm
SET
	iDistance = (@endDistance - @startDistance) 
	,iAverageSpeed = CASE WHEN DATEDIFF(second, i.dStartTime, i.dEndTime) > 0
		THEN (@endDistance - @startDistance) / (convert(float, DATEDIFF(second, i.dStartTime, i.dEndTime)) / @SecondsInHour) 
			ELSE 999 
			END
	,iMaxSpeed  = (SELECT TOP(1) vVehicleSpeed FROM tblCommonTrackingData WHERE vSequenceID >= i.nStartEventInst and  vSequenceID <= i.nEndEventInst and vpkDeviceID = i.vpkDeviceID order by vVehicleSpeed desc)	
FROM tblTrip_Master tm
INNER JOIN inserted i ON
    i.ipkTripID = tm.ipkTripID --AND i.key_col2 = tm.key_col2
WHERE
    i.dEndTime IS NOT NULL AND 
    i.nEndEventInst IS NOT NULL AND
    i.dStartTime >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 13, 0)

Open in new window

0
 

Author Comment

by:websss
ID: 40452362
here is the latest error
trigeror
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40452376
Alias missed:
ALTER TRIGGER [dbo].[tblTrip_Master__TRG_UPD]
ON [dbo].[tblTrip_Master]
AFTER UPDATE
AS
SET NOCOUNT ON;

			declare @SecondsInHour float = 3600
			declare @startDistance float = 0
			declare @endDistance float = 0

SELECT TOP(1) @endDistance = tm.vOdometer 
FROM tblCommonTrackingData tm
	INNER JOIN inserted i ON i.ipkTripID = tm.ipkTripID 
WHERE tm.vSequenceID = i.nEndEventInst and tm.vpkDeviceID = i.vpkDeviceID 
order by tm.ipkCommanTrackingID desc

if @endDistance = 0
	SELECT TOP(1) @endDistance = tm.vOdometer 
	FROM tblCommonTrackingData tm
		INNER JOIN inserted i ON i.ipkTripID = tm.ipkTripID 
	WHERE tm.vSequenceID = (i.nEndEventInst-1) and tm.vpkDeviceID = i.vpkDeviceID 
	order by tm.ipkCommanTrackingID desc
	
SELECT TOP(1) @startDistance = tm.vOdometer 
FROM tblCommonTrackingData tm
	INNER JOIN inserted i ON i.ipkTripID = tm.ipkTripID 
WHERE tm.vSequenceID = i.nStartEventInst  and tm.vpkDeviceID = i.vpkDeviceID 
order by tm.ipkCommanTrackingID desc

UPDATE tm
SET
	iDistance = (@endDistance - @startDistance) 
	,iAverageSpeed = CASE WHEN DATEDIFF(second, i.dStartTime, i.dEndTime) > 0
		THEN (@endDistance - @startDistance) / (convert(float, DATEDIFF(second, i.dStartTime, i.dEndTime)) / @SecondsInHour) 
			ELSE 999 
			END
	,iMaxSpeed  = (SELECT TOP(1) vVehicleSpeed FROM tblCommonTrackingData WHERE vSequenceID >= i.nStartEventInst and  vSequenceID <= i.nEndEventInst and vpkDeviceID = i.vpkDeviceID order by vVehicleSpeed desc)	
FROM tblTrip_Master tm
INNER JOIN inserted i ON
    i.ipkTripID = tm.ipkTripID --AND i.key_col2 = tm.key_col2
WHERE
    i.dEndTime IS NOT NULL AND 
    i.nEndEventInst IS NOT NULL AND
    i.dStartTime >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 13, 0)

Open in new window

0
 

Author Comment

by:websss
ID: 40452381
ooh, so close
just this one thing left
trig123
0
 
LVL 51

Accepted Solution

by:
Vitor Montalvão earned 1800 total points
ID: 40452397
Hummm....
My mistake. I copied the INNER JOIN from the UPDATE statement but there's no tblTrip_Master table in those SELECT's:
ALTER TRIGGER [dbo].[tblTrip_Master__TRG_UPD]
ON [dbo].[tblTrip_Master]
AFTER UPDATE
AS
SET NOCOUNT ON;

			declare @SecondsInHour float = 3600
			declare @startDistance float = 0
			declare @endDistance float = 0

SELECT TOP(1) @endDistance = tm.vOdometer 
FROM tblCommonTrackingData tm, inserted i 
WHERE tm.vSequenceID = i.nEndEventInst and tm.vpkDeviceID = i.vpkDeviceID 
order by tm.ipkCommanTrackingID desc

if @endDistance = 0
	SELECT TOP(1) @endDistance = tm.vOdometer 
	FROM tblCommonTrackingData, inserted i 
	WHERE tm.vSequenceID = (i.nEndEventInst-1) and tm.vpkDeviceID = i.vpkDeviceID 
	order by tm.ipkCommanTrackingID desc
	
SELECT TOP(1) @startDistance = tm.vOdometer 
FROM tblCommonTrackingData tm, inserted i 
WHERE tm.vSequenceID = i.nStartEventInst  and tm.vpkDeviceID = i.vpkDeviceID 
order by tm.ipkCommanTrackingID desc

UPDATE tm
SET
	iDistance = (@endDistance - @startDistance) 
	,iAverageSpeed = CASE WHEN DATEDIFF(second, i.dStartTime, i.dEndTime) > 0
		THEN (@endDistance - @startDistance) / (convert(float, DATEDIFF(second, i.dStartTime, i.dEndTime)) / @SecondsInHour) 
			ELSE 999 
			END
	,iMaxSpeed  = (SELECT TOP(1) vVehicleSpeed FROM tblCommonTrackingData WHERE vSequenceID >= i.nStartEventInst and  vSequenceID <= i.nEndEventInst and vpkDeviceID = i.vpkDeviceID order by vVehicleSpeed desc)	
FROM tblTrip_Master tm
INNER JOIN inserted i ON
    i.ipkTripID = tm.ipkTripID --AND i.key_col2 = tm.key_col2
WHERE
    i.dEndTime IS NOT NULL AND 
    i.nEndEventInst IS NOT NULL AND
    i.dStartTime >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 13, 0)

Open in new window

0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 200 total points
ID: 40452545
I'd add the lookups into the main query rather than using separate variables:


ALTER TRIGGER [dbo].[tblTrip_Master__TRG_UPD]
ON [dbo].[tblTrip_Master]
AFTER UPDATE
AS
SET NOCOUNT ON;

declare @SecondsInHour float = 3600

UPDATE tm
SET
      iDistance = (endDistance - startDistance)
      ,iAverageSpeed = CASE WHEN DATEDIFF(second, i.dStartTime, i.dEndTime) > 0
            THEN (endDistance - startDistance) / (convert(float, DATEDIFF(second, i.dStartTime, i.dEndTime)) / @SecondsInHour)
                  ELSE 999
                  END
      ,iMaxSpeed  = (SELECT TOP(1) vVehicleSpeed FROM tblCommonTrackingData WHERE vSequenceID >= i.nStartEventInst and  vSequenceID <= i.nEndEventInst and vpkDeviceID = i.vpkDeviceID order by vVehicleSpeed desc)      
FROM tblTrip_Master tm
INNER JOIN inserted i ON
    i.ipkTripID = tm.ipkTripID --AND i.key_col2 = tm.key_col2
--get last non-zero end distance
OUTER APPLY (
    SELECT TOP (1) CAST(tm.vOdometer AS float) AS endDistance
    FROM tblCommonTrackingData tm
    WHERE
        tm.vpkDeviceID = i.vpkDeviceID AND
        tm.vSequenceID IN ( i.nEndEventInst, i.nEndEventInst-1 ) AND
        tm.vOdometer <> 0 --ignore 0 distance
    ORDER BY
        tm.vSequenceID DESC, tm.ipkCommanTrackingID DESC
) AS tm_end
--get start distance
OUTER APPLY (
    SELECT TOP (1) CAST(tm.vOdometer AS float) AS startDistance
    FROM tblCommonTrackingData tm
    WHERE
        tm.vpkDeviceID = i.vpkDeviceID AND
        tm.vSequenceID = i.nStartEventInst
    ORDER BY
        tm.ipkCommanTrackingID DESC
) AS tm_start
WHERE
    i.dEndTime IS NOT NULL AND
    i.nEndEventInst IS NOT NULL AND
    i.dStartTime >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 13, 0)
0
 

Author Closing Comment

by:websss
ID: 40456860
sorry for the delay in responded, needed to test it properly
works great thanks!
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

770 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