websss
asked on
SQL Trigger - If statement
Hi
I have the following in a trigger
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?
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 )
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?
ASKER
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)
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)
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 )
ASKER
yes that might work,
how would i put it in the UPDATE:
i.e.
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)
You need to verify first and update after. You can't do it in the same step.
ASKER
hmm
this doesn't seem to work
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 ) )
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.
ASKER
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)
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)
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)
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
sorry for the delay in responded, needed to test it properly
works great thanks!
works great thanks!
Open in new window