Obrigado pela ajuda Daniel.
Consegui resolver o problema parcialmente.
Utilizei "Conecty by level" mais estou tendo problemas de performance
as vezes uma consulta com 100 registros demora aproximadamente 9 minutos.
outras com o mesmo volume demoram segundos.
Devo estar fazendo algum agrupamento errado.
Selecionar tudo
select dtreal
, hora
, replace(wm_concat(dessc),',',' / ')as salas
, count(hora) total_salas
from (select dtreal
, hora
, count (codsc) totsalcir
, codsc
, dessc
, count(hora) total
from (select distinct (atend)
, trunc(dtrealizado) dtreal
, horaini - 1 + level as hora
, codsc
, dessc
, horaini
, horafim
from ( select bb.cd_atendimento atend
, bb.cd_sal_cir codsc
, sc.ds_sal_cir dessc
, bb.dt_realizacao dtrealizado
, bb.dt_fim_limpeza
, substr((cast(bb.dt_fim_limpeza as timestamp) - cast(bb.dt_realizacao as timestamp)),12,2) ||':'||
substr((cast(bb.dt_fim_limpeza as timestamp) - cast(bb.dt_realizacao as timestamp)),15,2) ||':'||
substr((cast(bb.dt_fim_limpeza as timestamp) - cast(bb.dt_realizacao as timestamp)),18,2) as duracao
, to_char(bb.dt_realizacao, 'hh24') as horaini
, to_char(bb.dt_fim_limpeza,'hh24') as horafim
from dbamv.aviso_cirurgia bb
, dbamv.sal_cir sc
where bb.cd_sal_cir = sc.cd_sal_cir(+)
and bb.dt_realizacao between to_date ('10/07/2015 00:00:00','dd/mm/yyyy hh24:mi:ss')
and to_date ('10/07/2015 23:59:59','dd/mm/yyyy hh24:mi:ss')
and bb.dt_cancelamento is null
and bb.tp_situacao = 'R'
order
by bb.dt_realizacao
)
connect
by level <= horafim - horaini + 1
order
by atend
, hora
)
group
by dtreal
, hora
, codsc
, dessc
order
by dtreal
, hora
, codsc
, dessc
)
group
by dtreal
, hora
having count(hora) >= 2
order
by dtreal
, hora
Resultado:
Selecionar tudo
DTREAL HORA SALAS SALAS
08/07/2015 19 SALA 6 (CC II) / SALA 3 (CC II) 2
09/07/2015 15 SALA 6 (CC II) / SALA 7 (CC II) 2
09/07/2015 16 SALA 6 (CC II) / SALA 7 (CC II) 2
09/07/2015 17 SALA 6 (CC II) / SALA 7 (CC II) 2
09/07/2015 18 SALA 4 (CC II) / SALA 7 (CC II) 2
09/07/2015 19 SALA 4 (CC II) / SALA 7 (CC II) 2
08/07/2015 19 SALA 6 (CC II) / SALA 3 (CC II) 2
09/07/2015 15 SALA 6 (CC II) / SALA 7 (CC II) 2
09/07/2015 16 SALA 6 (CC II) / SALA 7 (CC II) 2
09/07/2015 17 SALA 6 (CC II) / SALA 7 (CC II) 2
09/07/2015 18 SALA 4 (CC II) / SALA 7 (CC II) 2
09/07/2015 19 SALA 4 (CC II) / SALA 7 (CC II) 2