SQLTeam.com | Weblogs | Forums

Max records from multiple columns with a conditions

I have a table with 36 columns (transaction).
from the 36 fields 3 fields populate the timestamp: time_field1, time_field2 and time_field3.
I want to populate all the records in the transaction table that meet the following condition:

if the greatest value among the following fields: time_field1, time_field2 and time_field3 is behind the current time by 2 h

in an other word

Compare the values of time_field1, time_field2 and time_field3, consider the latest (Max), compare that Max to current timestamp, if the difference is > 2h then populate the records

Thanks

Welcome!

Something like below should work efficiently. Note that you can use all the columns in the original table even though the CROSS APPLY uses MAX to get a single value.

INSERT INTO dbo.new_table ( ... )
SELECT yt.* /* or, of course, yt.column_name1, ... */
FROM dbo.your_table yt
CROSS APPLY (
    SELECT MAX(time_field) AS max_time_field
    FROM (
        VALUES(time_field1),(time_field2),(time_field3)
    ) AS time_fields(time_field)
) AS ca1
WHERE ca1.max_time_field < DATEADD(HOUR, -2, GETDATE())
1 Like

Thanks It did work. I think there was no need for the Alias "yt" so I removed it
Thanks