Write Oracle Db View Based On Different Column Time Stamp Difference

I am trying to write a view where one of the column is a business logic which is like a SLA time difference based on received message state. Example is below.. where for same MSG_G

Solution 1:

If you want the difference with the created_time that has no time component, then you want logic like this:

select msg_guid,
       (max(casewhen payload_type =1then created_time end) -
        (casewhenmax(casewhen payload_type =2then created_time end) <>
                   trunc(max(casewhen payload_type =2then created_time end))
              thenmax(casewhen payload_type =2then created_time end) 
              whenmax(casewhen payload_type =3then created_time end) <>
                   trunc(max(casewhen payload_type =3then created_time end))
              thenmax(casewhen payload_type =3then created_time end) 
        end)
        ) as diff              
fromtable t
groupby msg_guid;

This returns the result as fractions of a day.