I'm basically calculating the turn around time between an arrival time and departure time (as you may have seen from my previous threads). However, I have now been asked to store these as intervals of hours (i.e. 0-1,1-2,2-3,3-4 etc). Everything works fine and the structure to the select statement is as follows:
WITH source AS (SELECT arrival_time AS Arrival, departure_time AS Departure, [big load of decode statements and calculations to calculate the time in minutes seconds etc.] AS TAT FROM table)
SELECT Arrival, Departure, TAT
case
when to_number(TAT) < 100 then '0-1'
when to_number(TAT) < 200 then '1-2'
etc.
end
from source
/
So the question is, rather than just displaying this data in an output, how can I apply this to an update statement to a new column in an existing table? So the hours and minutes are used to calculate the case statement. I could just split this into 2 statements (TAT (turn around time) being calculated and stored) and then use this for the case statement as part of a second process. But it would be ideal to have this done in just one clean statement.
Thanks a lot in advance,
Dan