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
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())