Help in a select that brings hours

madmax · Tue, 08 May 2007 11:33 am

Dear friends I am with this select below: But when the second exceeds 2 decimal places it is like this ### How can I treat this ???

I accept any possible help because I already thought about almost everything rsrsrs ....

hugs friends .....: D

Select all

 
SELECT abs(Ltrim(To_Char(Trunc(((TO_DATE('01/12/2006 11:05:45','DD/MM/YYYY HH24:MI:SS')  
- TO_DATE('02/12/2006 13:25:40','DD/MM/YYYY HH24:MI:SS')))*24),'999999999999999'),' ')) 
	      ||':'|| 
	      Ltrim(To_Char(abs(Round((((TO_DATE('01/12/2006 11:05:45','DD/MM/YYYY HH24:MI:SS') - TO_DATE('02/12/2006 13:25:40','DD/MM/YYYY HH24:MI:SS')))*24  
	      - Trunc(((TO_DATE('01/12/2006 11:05:45','DD/MM/YYYY HH24:MI:SS')  
        - TO_DATE('02/12/2006 13:25:40','DD/MM/YYYY HH24:MI:SS')))*24))*60)),'00'),' ')  
        ||':'|| 
	      Ltrim(To_Char(abs(Round((((TO_DATE('01/12/2006 11:05:45','DD/MM/YYYY HH24:MI:SS') - TO_DATE('02/12/2006 13:25:40','DD/MM/YYYY HH24:MI:SS')))*24  
	      - Trunc(((TO_DATE('01/12/2006 11:05:45','DD/MM/YYYY HH24:MI:SS')  
        - TO_DATE('02/12/2006 13:25:40','DD/MM/YYYY HH24:MI:SS')))*24))*60))*360,'00'),' ')  
        horas 
 
        from dual

rogenaro · Tue, 08 May 2007 1:49 pm

I know you do not answer your question, but there's a similar consultation I did a while ago:

Select all

 
select horas||':'||minutos||':'||segundos 
from 
( 
    select lpad(trunc(           abs(data1-data2) * 24          )       , 2, '0') horas 
         , lpad(trunc((mod(trunc(abs(data1-data2) * 24 * 60     ), 60))), 2, '0') minutos 
         , lpad(trunc((mod(round(abs(data1-data2) * 24 * 60 * 60), 60))), 2, '0') segundos 
    from 
    ( 
        select to_date('01/12/2006 11:05:45','DD/MM/YYYY HH24:MI:SS') data1 
             , to_date('02/12/2006 13:25:40','DD/MM/YYYY HH24:MI:SS') data2 
        from dual 
    ) 
);

madmax · Tue, 08 May 2007 4:56 pm

Dear friend Rogenaro
Your SELECT fell like a glove rsrsrs ...

very sheltered friend ..: -O

Toad · Tue, 15 May 2007 11:55 am

Look, since the subject is this, a time ago vindalencio posted a select that selected several fields that calculated in various forms, dates and hours:

Select all

 
SELECT SYSDATE DATA_ATUAL, 
               ADD_MONTHS(SYSDATE,-1) MENOS_UM_mês, 
               ADD_MONTHS(SYSDATE,1) MAIS_UM_mês, 
               LAST_DAY(SYSDATE) ULTIMO_DIA_mês, 
               TRUNC(SYSDATE,'MONTH') PRIMEIRO_DIA_mês, 
               MONTHS_BETWEEN(SYSDATE,'01-JAN-2001') QTD_mês, 
               FLOOR(MONTHS_BETWEEN(SYSDATE,'01-JAN-2001')) ARREDONDA_ABAIXO, 
               CEIL(MONTHS_BETWEEN(SYSDATE,'01-JAN-2001')) ARREDONDA_ACIMA, 
               ABS(MONTHS_BETWEEN(SYSDATE,'01-JAN-2001')) VALOR_ABSOLUTO, 
               ROUND((MONTHS_BETWEEN(SYSDATE,'01-JAN-2001')),3) ARREDONDA, 
               TRUNC(SYSDATE,'year') PRIMEIRO_DIA_ANO, 
               to_char(SYSDATE,'dd " de " FMMONTH " de " YYYY','nls_date_language=portuguese')DIAMES_EXTENSO, 
               to_char(SYSDATE,'FMMONTH " DE " YYYY','nls_date_language=portuguese') mês_EXTENSO, 
               TO_CHAR(SYSDATE,'HH24:MI') HORA_MINUTO, 
               to_char(sysdate,'FMMonth','nls_date_language=portuguese') mês_corrente, 
               to_date(lpad(to_char(1234),4,'0'),'hh24mi') dias_data, 
               Floor(floor(months_between(SYSDATE,to_date('11/02/1982'))) / 12) IDADE, 
               decode(TO_NUMBER(TO_CHAR(SYSDATE,'D')),2,'segunda-feira', 
                                                      3,'terça-feira', 
                                                      4,'quarta-feira', 
                                                      5,'quinta-feira', 
                                                      6,'sexta-feira', 
                                                      7,'sabado', 
                                                      1,'domingo') dia_da_semana 
  FROM DUAL

99]]: D

FMMS · Wed, 24 Oct 2018 10:00 am

Good morning, I'm new here and in PL / SQL issue.

I have a query, but is returning an error that returns "the subquery returns more than one line," I believe I do not know how to allocate the SUM or NVL in the correct location.

follows the code:

Select all

SELECT  ap.nr_atendimento, 
        ap.DT_ALTA, 
        ap.DT_ENTRADA, 
            (select lpad(trunc(           abs(apu.dt_saida_unidade - apu.dt_entrada_unidade) * 24          )       , 2, '0') || ':' || 
                    lpad(trunc((mod(trunc(abs(apu.dt_saida_unidade - apu.dt_entrada_unidade) * 24 * 60     ), 60))), 2, '0')|| ':' || 
                    lpad(trunc((mod(round(abs(apu.dt_saida_unidade - apu.dt_entrada_unidade) * 24 * 60 * 60), 60))), 2, '0') 
            FROM atend_paciente_unidade apu 
                WHERE apu.cd_setor_atendimento = 129 
                    AND apu.nr_atendimento = ap.nr_atendimento) unid_inter_3_andar 
         
FROM atendimento_paciente_v ap, 
     conta_paciente c, 
     cirurgia r 
 
WHERE ap.nr_atendimento = c.nr_atendimento 
   and ap.NR_ATENDIMENTO = r.nr_atendimento(+) 
   AND ap.cd_estabelecimento = 1 --:cd_estabelecimento 
   AND ap.dt_entrada between '20/10/2018' AND '24/10/2018' --:dt_inicial and :dt_final + 86399 / 86400 
   AND exists (select 1 
          from conta_paciente 
         where nr_atendimento = ap.NR_ATENDIMENTO) 
   AND ap.NR_ATENDIMENTO = 1844338 
 
GROUP BY ap.nr_atendimento,ap.DT_ALTA,ap.DT_ENTRADA 
 
ORDER BY ap.DT_ENTRADA;

FMMS · Wed, 24 Oct 2018 10:31 am

Madmax wrote: Dear friend Rogenaro
Your Select fell like a glove rsrsrs ...

very sheltered friend ..: -o

]]
Friend, good morning. In my case, it is returning "Error more than one line in the query of a single line"

I can not allocate the SUM or NVL in the code below:

Select all

SELECT  ap.nr_atendimento, 
        ap.DT_ALTA, 
        ap.DT_ENTRADA, 
            (select lpad(trunc(           abs(apu.dt_saida_unidade - apu.dt_entrada_unidade) * 24          )       , 2, '0') || ':' || 
                    lpad(trunc((mod(trunc(abs(apu.dt_saida_unidade - apu.dt_entrada_unidade) * 24 * 60     ), 60))), 2, '0')|| ':' || 
                    lpad(trunc((mod(round(abs(apu.dt_saida_unidade - apu.dt_entrada_unidade) * 24 * 60 * 60), 60))), 2, '0') 
            FROM atend_paciente_unidade apu 
                WHERE apu.cd_setor_atendimento = 129 
                    AND apu.nr_atendimento = ap.nr_atendimento) unid_inter_3_andar 
         
FROM atendimento_paciente_v ap, 
     conta_paciente c, 
     cirurgia r 
 
WHERE ap.nr_atendimento = c.nr_atendimento 
   and ap.NR_ATENDIMENTO = r.nr_atendimento(+) 
   AND ap.cd_estabelecimento = 1 --:cd_estabelecimento 
   AND ap.dt_entrada between '20/10/2018' AND '24/10/2018' --:dt_inicial and :dt_final + 86399 / 86400 
   AND exists (select 1 
          from conta_paciente 
         where nr_atendimento = ap.NR_ATENDIMENTO) 
   AND ap.NR_ATENDIMENTO = 1844338 
 
GROUP BY ap.nr_atendimento,ap.DT_ALTA,ap.DT_ENTRADA 
 
ORDER BY ap.DT_ENTRADA;

Toad · Wed, 24 Oct 2018 1:32 pm

Hi @fmms, if your problem is that the select is returning more than one line and you only need the first line, you can add the following clause to the main SELECT:

But you should expand these subcursions there to understand what is happening because it may be that you have some problem related to repeated data in your database.

In more, when you can dismember this select and have any questions about this, post the code here by using the Code and / Code tag ] to format and get better for us to read and understand.

Renato Menezes Viana · Sun, 03 Feb 2019 3:26 pm

Staff, only another optics to work with difference of dates, hours and similar that may be simpler than multiplications and multiplications and multiplications, etc.
http://en.glufke.net/oracle/viewtopic.p ... 992#p38992

Help in a select that brings hours

Who is online