I have the following problem between viewing the days, hours, minutes and seconds between two dates.
I will put the code below and I will explain later:
select sep.solicitacao_exame_id
,pck_util.fnc_pega_class_descricao(se.centro_custo_id_origem) ccusto
,sep.pro_id pro_id
,((((((cl.dt_coleta - sep.dt_incl) * 24 * 60 * 60) / 3600)))-
(FLOOR((((cl.dt_coleta - sep.dt_incl) * 24 * 60 * 60) / 3600) /24) *24)) "HORAS_COLETA"
,FLOOR((((cl.dt_coleta - sep.dt_incl) * 24 * 60 * 60) / 3600)/24) ||' DIAS '||
((ROUND((((cl.dt_coleta - sep.dt_incl) * 24 * 60 * 60) / 3600)))-
(FLOOR((((cl.dt_coleta - sep.dt_incl) * 24 * 60 * 60) / 3600) /24) *24)) || ':' ||
ROUND((((cl.dt_coleta - sep.dt_incl) * 24 * 60 * 60) -
FLOOR(((cl.dt_coleta - sep.dt_incl) * 24 * 60 * 60) / 3600) * 3600) / 60) || ':' ||
ROUND((((cl.dt_coleta - sep.dt_incl) * 24 * 60 * 60) -
FLOOR(((cl.dt_coleta - sep.dt_incl) * 24 * 60 * 60) / 3600) * 3600 -
(FLOOR((((cl.dt_coleta - sep.dt_incl) * 24 * 60 * 60) -
FLOOR(((cl.dt_coleta - sep.dt_incl) * 24 * 60 * 60) / 3600) * 3600) / 60) * 60))) "TEMPO_COLETA"
,((((((re.hr_recepcao - cl.dt_coleta) * 24 * 60 * 60) / 3600)))-
(FLOOR((((re.hr_recepcao - cl.dt_coleta) * 24 * 60 * 60) / 3600) /24) *24)) "HORAS_RECEPCAO"
,FLOOR((((re.hr_recepcao - cl.dt_coleta) * 24 * 60 * 60) / 3600)/24) ||' DIAS '||
((ROUND((((re.hr_recepcao - cl.dt_coleta) * 24 * 60 * 60) / 3600)))-
(FLOOR((((re.hr_recepcao - cl.dt_coleta) * 24 * 60 * 60) / 3600) /24) *24)) || ':' ||
ROUND((((re.hr_recepcao - cl.dt_coleta) * 24 * 60 * 60) -
FLOOR(((re.hr_recepcao - cl.dt_coleta) * 24 * 60 * 60) / 3600) * 3600) / 60) || ':' ||
ROUND((((re.hr_recepcao - cl.dt_coleta) * 24 * 60 * 60) -
FLOOR(((re.hr_recepcao - cl.dt_coleta) * 24 * 60 * 60) / 3600) * 3600 -
(FLOOR((((re.hr_recepcao - cl.dt_coleta) * 24 * 60 * 60) -
FLOOR(((re.hr_recepcao - cl.dt_coleta) * 24 * 60 * 60) / 3600) * 3600) / 60) * 60))) "TEMPO_RECEPCAO"
,sep.dt_incl
,cl.dt_coleta
,re.hr_recepcao
from solicitacao_exame_procedimento SEP
,solicitacao_exame SE
,procedimento pro
,parametros_procedimento pp
,classificacao c
,coleta_laboratorio cl
,realizacao_exame re
,laudo_html lh
where sep.dt_efetivacao between '01012010' and '01012010'
and sep.efetivado = 'S'
and sep.status_id = 6
and sep.solicitacao_exame_id = 1570476
and se.solicitacao_exame_id = sep.solicitacao_exame_id
and pro.pro_id = sep.pro_id
and pp.pro_id = pro.pro_id
AND C.CLASSIFICACAO_ID = PP.CLASSIFICACAO_ID
and cl.solic_exame_proc_id = sep.solic_exame_proc_id
and cl.dt_coleta = (Select max(cla.dt_coleta)
from coleta_laboratorio cla
where cla.solic_exame_proc_id = sep.solic_exame_proc_id)
and re.solic_exame_proc_id = sep.solic_exame_proc_id
and re.filial_id = 1
and lh.solic_exame_proc_id = sep.solic_exame_proc_id
order by 1,3
SOLICITACAO_EXAME_ID CCUSTO PRO_ID HORAS_COLETA TEMPO_COLETA HORAS_RECEPCAO 1570476 PEDIATRIA 1019 21,67777778 6 DIAS 22:41:40 0,73 TEMPO_RECEPCAO DT_INCL DT_COLETA HR_RECEPCAO
0 DIAS 1:44:48 22/12/2009 09:26:41 29/12/2009 07:07:21 29/12/2009 07:51:09
Using this SELECT works normal the time_coleta, but the time_recupation automatically adds 1 hour more, this occurs due to the use of round () in the second line of the select of the function To get this time.
But when I change the round () by Trunc () or Floor () the result is as follows:
SOLICITACAO_EXAME_ID CCUSTO PRO_ID HORAS_COLETA TEMPO_COLETA HORAS_RECEPCAO 1570476 PEDIATRIA 1019 21,67777778 6 DIAS 21:41:40 0,73 TEMPO_RECEPCAO DT_INCL DT_COLETA HR_RECEPCAO
0 DIAS 0:44:48 22/12/2009 09:26:41 29/12/2009 07:07:21 29/12/2009 07:51:09
I will use this code to bring 1 period of requisitions, but in case I had a problem with it, if someone has a solution thank you, I'm desperate because I discovered that they are using functions in my work and All in this error.
Thanks
