• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 41
  • Last Modified:

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

0
CAMPzxzxDeathzxzx
Asked:
CAMPzxzxDeathzxzx
  • 4
  • 2
1 Solution
 
Scott PletcherSenior DBACommented:
It should take the newer date from the same day.

That's not really clear.  But assuming you do want the earliest time on the last day:
...
     OUTER APPLY (
         SELECT TOP (1) DateStamp AS MaxDate
         FROM WeighingEvents
         WHERE [ContainerID] = [ins].ContainerID
         ORDER BY CAST(DateStamp AS date) DESC, CAST(DateStamp AS time) ASC
     ) AS [we]        
...
0
 
CAMPzxzxDeathzxzxAuthor Commented:
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

0
 
Scott PletcherSenior DBACommented:
I did specify TOP.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
CAMPzxzxDeathzxzxAuthor Commented:
sorry
0
 
CAMPzxzxDeathzxzxAuthor Commented:
Thank you
0
 
CAMPzxzxDeathzxzxAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now