Solved

SQL Trigger - If statement

Posted on 2014-11-19
18
186 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 49

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

 
LVL 49

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 49

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 49

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 49

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 49

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 49

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 49

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Table where row act as column 11 70
SQL 2012 AOG and SQL2014 AOG 76 60
SQL 2014 missing dll from Bin? 3 33
How to trim a value in SQL 2 25
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

749 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