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

CAMPzxzxDeathzxzxAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

CAMPzxzxDeathzxzxAuthor Commented:
sorry
0
CAMPzxzxDeathzxzxAuthor Commented:
Thank you
0
CAMPzxzxDeathzxzxAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.