The source data is in a table (Teradata), please reference the attachment;the non-yellow part is original columns while yellow part are derived columns. I want to use SQL statement of Teradata to get a derived column (the column of "final_result"): Now the data of this table is order by operator, activity_finish_date

1. The column of 'induce_duration1' comes from: the current row of "activity_finish_date" minus the previous row of "activity_finish_date"
2. The column of 'induce_duration2' comes from: the current row of "activity_finish_date" minus the current row of "activity_start_date"
3. The column of "final_result" comes from: min(induce_duration1, induce_duration2)

CodePudding user response：

Assuming your Teradata release supports LEAST/GREATEST on Timestamps:

activity_finish_date -                -- current finish
GREATEST(activity_start_date          -- current start
        ,LAG(activity_finish_date)    -- previous end
         OVER (PARTITION BY operator
               ORDER BY activity_finish_date)) HOUR(4) TO SECOND