Take the interval in hours between two dates

[gutoalex]

: D friends would like a little help.
I need to take the interval in hours between the TimeStemp fields
Example:

Select all

Data Inicial >= 01/09/2015 08:00:00 
Data final  <=  01/09/2015 11:00:00

Result:

Select all

01/09/2015 08:00:00 
01/09/2015 09:00:00 
01/09/2015 10:00:00 
01/09/2015 11:00:00

You need to count as many surgeons are being used at the same time.
ex. Room 1 -> CIR.Initiated at 8 hrs and final at 11 hrs
Room 2 -> CIR.Initiated at 9 hrs and final at 10 hrs.

Result -> Room 1 and room 2 used in the same period in hours 9 and 10.

I thank all the help.

[DanielNN]

OPA,

Take a look at: http://en.glufke.net/oracle/viewtopic.p ... A7a#p32892
and http://en.glufke.net/oracle/viewtopic.p ... A7a#p33353

Any doubt is still only to speak.

[gutoalex]

Thanks for the help Daniel.

I was able to solve the problem partially.
I used "CONECTY by LEVEL" but I am having performance problems
Sometimes a query with 100 records takes approximately 9 minutes.
Other with the same volume take seconds.
I must be doing some wrong grouping.

Select all

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 

Result:

Select all

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

[Renato Menezes Viana]

Friends, it's been a long time for the doubt, but I find it very good to observe at the link below as a starting point of a solution involving dates:
http://en.glufke.net/oracle/viewtopic.p ... 992#p38992
ABS, Renato Viana