Solved

SQL Trigger - If statement

Posted on 2014-11-19
18
184 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
  • 9
  • 8
18 Comments
 
LVL 47

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 47

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 47

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 47

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 47

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 47

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 47

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 47

Accepted Solution

by:
Vitor Montalvão earned 450 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 50 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

831 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