SQL Trigger - If statement

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?
websssCEOAsked:
Who is Participating?
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.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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

websssCEOAuthor Commented:
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)
websssCEOAuthor Commented:
See here ...the bottom one is what i would need if @endDistance = 0

vsequenceid issue
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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

websssCEOAuthor Commented:
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

Vitor MontalvãoMSSQL Senior EngineerCommented:
You need to verify first and update after. You can't do it in the same step.
websssCEOAuthor Commented:
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

Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
websssCEOAuthor Commented:
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

Vitor MontalvãoMSSQL Senior EngineerCommented:
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

websssCEOAuthor Commented:
I think the i.nEndEVentIsnt etc can't go above the UPDATE, see here
triggererror
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

websssCEOAuthor Commented:
here is the latest error
trigeror
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

websssCEOAuthor Commented:
ooh, so close
just this one thing left
trig123
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

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
Scott PletcherSenior DBACommented:
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)
websssCEOAuthor Commented:
sorry for the delay in responded, needed to test it properly
works great thanks!
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.