CAMPzxzxDeathzxzx
asked on
SQL Trigger has Max date issue.
Below is a trigger that works but has a bug. If 2 weighing events are on the same day it chooses the older weighing event of that day to populate the current gas weight. It should take the newer date from the same day. How the heck can you can that be done? Maybe Max date but minimum time?
CREATE TRIGGER [dbo].[WeighingEvents2]
ON [dbo].[WeighingEvents]
AFTER UPDATE
AS
SET NOCOUNT ON;
UPDATE [cgw]
SET
[GasWeight] = [ins].GasWeight,
[ContainerType] = IIF([c].ContainerNameplateCapacity > 0, 2, 1),
[Active] = [ins].Active
FROM inserted [ins]
INNER JOIN [dbo].[CurrentGasWeight] [cgw] ON [cgw].ContainerID = [ins].ContainerID
OUTER APPLY (
SELECT MAX(DateStamp) AS MaxDate
FROM WeighingEvents
WHERE [ContainerID] = [ins].ContainerID
) AS [we]
LEFT OUTER JOIN [dbo].[Container] c ON c.[ContainerID] = cgw.ContainerID
WHERE
[we].MaxDate IS NULL OR
[we].MaxDate <= ins.DateStamp
INSERT INTO [dbo].[CurrentGasWeight]
(
[CompanyID],
[ContainerID],
[GasWeight],
[ContainerType],
[Active]
)
SELECT
[ins].CompanyID,
[ins].ContainerID,
[ins].GasWeight,
c.ContainerTypeID,
c.Active
FROM inserted [ins]
LEFT OUTER JOIN [dbo].[CurrentGasWeight] [cgw] ON [cgw].ContainerID = [ins].ContainerID
OUTER APPLY (
SELECT MAX(DateStamp) AS MaxDate
FROM WeighingEvents
WHERE [ContainerID] = [ins].ContainerID
) AS [we]
LEFT OUTER JOIN [dbo].[Container] c ON c.[ContainerID] = ins.ContainerID
WHERE
[cgw].ContainerID IS NULL AND
([we].MaxDate IS NULL OR
[we].MaxDate <= ins.DateStamp)
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I did specify TOP.
ASKER
sorry
ASKER
Thank you
ASKER
Thanks
ASKER
Open in new window