Link to home
Start Free TrialLog in
Avatar of CAMPzxzxDeathzxzx
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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of CAMPzxzxDeathzxzx
CAMPzxzxDeathzxzx

ASKER

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

DROP TRIGGER [dbo].[WeighingEvents2]
GO
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
		 ORDER BY CAST(DateStamp AS date) DESC, CAST(DateStamp AS time) ASC
     ) 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
		 ORDER BY CAST(DateStamp AS date) DESC, CAST(DateStamp AS time) ASC
     ) 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

Open in new window

I did specify TOP.
sorry
Thank you
Thanks