-- 아래의 쿼리 참조하세요.

with v_data1 as (
  select 'a' as key1 from dual
  union all select 'b' as key1 from dual
  union all select 'c' as key1 from dual
), v_data2 as (
  select 'a' as key1,'에러' as key2,'201501011100' as key3 from dual
  union all select 'a' as key1,'에러' as key2,'201501011000' as key3 from dual
  union all select 'a' as key1,'정상' as key2,'201501010900' as key3 from dual
  union all select 'a' as key1,'에러' as key2,'201501010800' as key3 from dual
  union all select 'b' as key1,'정상' as key2,'201501011100' as key3 from dual
  union all select 'b' as key1,'정상' as key2,'201501011000' as key3 from dual
  union all select 'b' as key1,'에러' as key2,'201501010900' as key3 from dual
), v_data3 as (
  select a.key1,a.key3 as max_key3 ---> 정상인것의 최신시간을 구한다.
  from   v_data2 a
  where  a.key2 = '정상'
  and    not exists (
    select 1 from v_data2 e1
    where a.key1 = e1.key1
    and   a.key2 = e1.key2
    and   a.key3 < e1.key3
  )
  union
  select a.key1,'201401010000' as max_key3 --> 정상이 없는경우 기준시간을 임의로 셋팅함
  from   v_data1 a
  where  not exists (
    select 1 from v_data2 e1
    where a.key1 = e1.key1
    and   e1.key2 = '정상'
  )
), v_data4 as (
  select a.key1,b.key2,b.key3 --> key1별로 최신 데이타를 가져온다.
  from   v_data1 a
         ,v_data2 b
  where  a.key1 = b.key1
  and    not exists (
    select 1 from v_data2 e1 where b.key1 = e1.key1 and b.key3 < e1.key3
  )
), v_data5 as (
  select  --> 에러구간에 대한 시간구하기
    m.key1
    ,to_char(floor((sysdate - to_date(min(p.key3),'yyyymmddhh24mi'))*24 )) || ' 시간' as hour_txt --> 에러일경우 현재시간부터 에러min시간까지 구하였음. 분단위는 버림
    -- ,to_char(floor((to_date(max(p.key3),'yyyymmddhh24mi') - to_date(min(p.key3),'yyyymmddhh24mi'))*24 )) || ' 시간' as hour_txt --> 에러Max부터 에러min까지의 시간
  from v_data4 m
       ,v_data3 n
       ,v_data2 p
  where m.key1 = n.key1
  and   m.key1 = p.key1
  and   m.key2 = '에러'
  and   p.key3 <= m.key3
  and   p.key3 > n.max_key3
  group by m.key1
)
select
  *
from (
  select --> 데이타가 없는경우 데이타생성
    a.key1,'에러' as key2,'' as hour_txt
  from v_Data1 a
  where not exists (
    select 1 from v_data2 where a.key1 = b.key1
  )
    union all
  select
    a.key1,a.key2,b.hour_txt
  from v_Data4 a
      ,v_data5 b
  where a.key1 = b.key1(+)
)
order by key1

 감사합니다.