Combinated query in Oracle SQL

Question

i have table Employess:

|id|datetime           |in-out|
|1 |2015-03-03 06:00:00|in    |
|1 |2015-03-03 14:15:00|out   |
|1 |2015-03-04 06:00:00|in    |
|1 |2015-03-04 15:00:00|out   |

I want create view with information how long work employees(id) such that

|id|datetime_in        |datetime_out       |how_log|
|1 |2015-03-03 06:00:00|2015-03-03 14:00:00|08:15  |
|1 |2015-03-04 06:00:00|2015-03-03 15:00:00|09:00  |

Could you help me?

Answer 1

Here is another way to get the result you are after - by using pivot clause(Oracle 11g and up):

select id
     , to_char(in1, 'yyyy-mm-dd hh24:mi:ss')  as datetime_in
     , to_char(out1, 'yyyy-mm-dd hh24:mi:ss') as datetime_out
     , to_char(extract(hour from numtodsinterval(out1-in1, 'day'))
              , 'fm00') || ':' || 
       to_char(extract(minute from numtodsinterval(out1-in1, 'day'))
              , 'fm00')                       as how_long
  from ( select id
              , datetime
              , in_out 
              , row_number() over(partition by id, in_out
                                  order by datetime) as rn
          from tb1   
         order by datetime
      )
pivot (
  max(datetime) for in_out in ('in' as in1, 'out' as out1)
)
order by id, datetime_in

Result:

        ID DATETIME_IN         DATETIME_OUT        HOW_LONG
---------- ------------------- ------------------- --------
         1 2015-03-03 06:00:00 2015-03-03 14:15:00 08:15    
         1 2015-03-04 06:00:00 2015-03-04 15:00:00 09:00 

SQLFiddle Demo