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?
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
in time but has no out time (or the other way around)? What result do you expect in this situation?nvl() or coalesce() function to deal with null's here is an exampleof "copying" in time if the out one happens to be null.If you assume that the "in"s and "out"s alternate, you can just get the next value:
select e.id, e.datetime as datetime_in, datetime_out,
(floor((datetime_out - datetime_in)*24) || ':' ||
mod(floor((datetime_out - datetime_in * 24*60), 60)
) as timediff
from (select e.*, lead(datetime) over (order by partition by id order by datetime) as datetime_out
from employees e
) e
where in_out = 'in'
