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?
websssAsked:
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

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

vsequenceid issue
0
Ultimate Tool Kit for Technology Solution Provider

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.

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

0
websssAuthor 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

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

0
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.
0
websssAuthor 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

0
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

0
websssAuthor Commented:
I think the i.nEndEVentIsnt etc can't go above the UPDATE, see here
triggererror
0
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

0
websssAuthor Commented:
here is the latest error
trigeror
0
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

0
websssAuthor Commented:
ooh, so close
just this one thing left
trig123
0
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

0

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