Oracle query - without temporary table

Question

I'm new to Oracle and I need to help with this query. I have table with data samples /records like:

name | datetime
-----------
A    | 20140414 10:00
A    | 20140414 10:30
A    | 20140414 11:00
B    | 20140414 11:30
B    | 20140414 12:00
A    | 20140414 12:30
A    | 20140414 13:00
A    | 20140414 13:30

And I need to "group"/get informations into this form:

name | datetime_from  | datetime_to
----------------------------------
A    | 20140414 10:00 | 20140414 11:00
B    | 20140414 11:30 | 20140414 12:00
A    | 20140414 12:30 | 20140414 13:30

I couldnt find any solution for query similar to this. Could anyone please help me?

Note: I dont want do use temporary tables.

Thanks, Pavel

Answer 1

SQL> with t (name, datetime) as
  2  (
  3  select 'A', to_date('20140414 10:00','YYYYMMDD HH24:MI') from dual union all
  4  select 'A', to_date('20140414 10:30','YYYYMMDD HH24:MI') from dual union all
  5  select 'A', to_date('20140414 11:00','YYYYMMDD HH24:MI') from dual union all
  6  select 'B', to_date('20140414 11:30','YYYYMMDD HH24:MI') from dual union all
  7  select 'B', to_date('20140414 12:00','YYYYMMDD HH24:MI') from dual union all
  8  select 'A', to_date('20140414 12:30','YYYYMMDD HH24:MI') from dual union all
  9  select 'A', to_date('20140414 13:00','YYYYMMDD HH24:MI') from dual union all
 10  select 'A', to_date('20140414 13:30','YYYYMMDD HH24:MI') from dual
 11  )
 12  select name, min(datetime) datetime_from, max(datetime) datetime_to
 13  from (
 14  select name, datetime,
 15  datetime-(1/48)*(row_number() over(partition by name order by datetime)) dt
 16  from t
 17  )
 18  group by name,dt
 19  order by 2,1
 20  /

N DATETIME_FROM  DATETIME_TO                                                    
- -------------- --------------                                                 
A 20140414 10:00 20140414 11:00                                                 
B 20140414 11:30 20140414 12:00                                                 
A 20140414 12:30 20140414 13:30     

Answer 2

You need to find periods where the values are the same. The easiest way in Oracle is to use the lag() function, some logic, and aggregation:

select name, min(datetime), max(datetime)
from (select t.*,
             sum(case when name <> prevname then 1 else 0 end) over (order by datetime) as cnt
      from (select t.*, lag(name) over (order by datetime) as prevname
            from table t
           ) t
     ) t
group by name, cnt;

What this does is count, for a given value of datetime, the number of times that the name has switched on or before that datetime. This identifies the periods of "constancy", which are then used for aggregation.

Answer 3

As 9000 is suggesting you can have a query like the following:

select
  a.name, 
  Max(a.datetime),
  Min(b.datetime)
  from
    table a,
    table b
  group by
    a.name
 where a.name = b.name