SQL Assistance for Report

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
jiopra
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 13
Joined: Mon, 29 Aug 2016 11:10 am

I have a SQL here q is the basis of a reproduction of my system related to medical care waiting time:

Select all

 select	 
	a.nr_atendimento, 
substr(obter_min_entre_datas(a.dt_entrada, a.dt_atend_medico,null),1,50)  qt_tempo_espera, 
substr(obter_min_entre_datas(a.dt_atend_medico, a.dt_fim_consulta, null),1,50) qt_temp_atend, 
	substr(obter_nome_pf(a.cd_pessoa_fisica),1,50) nm_paciente, 
	substr(obter_nome_pf(a.cd_medico_resp),1,50) nm_medico, 
	a.dt_entrada, 
	a.dt_inicio_atendimento, 
	a.dt_fim_triagem, 
	a.dt_alta, 
	substr(obter_dif_data(a.dt_inicio_atendimento,a.dt_fim_triagem,null),1,20) teste, 
	substr(obter_dif_data(a.dt_entrada,a.dt_alta,null),1,20) qt_tempo_atendimento 
 
	from	atendimento_paciente a 
 
where	a.dt_entrada between trunc(:dt_inicial) and fim_dia(:dt_final) 
and	((:cd_setor_atendimento = obter_setor_atendimento(a.nr_atendimento)) or (:cd_setor_atendimento = '0')) 
  order by 
	a.dt_entrada
in the column - qt_tempo_ space and column qt_temp_atend, I need to make the sum of the minutes of the report and at the end of the columns to display a total of minutes, how could I do this sum with the substr?
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

Good afternoon,

You can try using the extract (minute.

There are already some topics here in the forum.
geovani
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 20
Joined: Sat, 06 Aug 2016 9:22 am
Location: Joinville-SC
Contact:
Geovani Cristofolini
Consultor de Business intelligence
http://useweknow.com

Oops!

From what I have seen, you are using a function that calculates the time between two dates and returns in the text format, and then you want to add those two times of the text type, is that it?

To take full time, why do not you use this function using a.dt_entada and a.dt_fim_consulta?

Example:

Select all

substr(obter_min_entre_datas(a.dt_entrada, a.dt_fim_consulta,null),1,50) qt_tempo_total
jiopra
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 13
Joined: Mon, 29 Aug 2016 11:10 am

Oops Geovani,

Then mano is this, I need to display the sum in the two functions, I did this way because I need the data of the two separate columns, the first displays the time in minutes that the doctor took To call the patient since your entrance to the hospital, and the other displays the time the doctor took to answer the patient after the call until the time is high.

If you can give yourself a force in how I can do this somatoriation of the total of each separate column thank you, because I need to display this data at a meeting day 5/9

99] Thanks abração.
geovani
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 20
Joined: Sat, 06 Aug 2016 9:22 am
Location: Joinville-SC
Contact:
Geovani Cristofolini
Consultor de Business intelligence
http://useweknow.com

See if so you need:

Select all

trunc((((a.dt_atend_medico - a.dt_entrada) + (a.dt_fim_consulta - a.dt_atend_medico)) * 86400)/3600) hora, 
trunc(mod(((((a.dt_atend_medico - a.dt_entrada) + (a.dt_fim_consulta - a.dt_atend_medico)) * 86400)/60),60)) minuto, 
 
lpad(trunc((((a.dt_atend_medico - a.dt_entrada) + (a.dt_fim_consulta - a.dt_atend_medico)) * 86400)/3600),2,0) ||':'|| 
lpad(trunc(mod(((((a.dt_atend_medico - a.dt_entrada) + (a.dt_fim_consulta - a.dt_atend_medico)) * 86400)/60),60)),2,0) hhmm
jiopra
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 13
Joined: Mon, 29 Aug 2016 11:10 am

Good morning Geovani,

I applied the code on my SQL, but did not perform the total calculation of the columns, I think I applied the code in the wrong place of SQL, could inform me in which part of the SQL applicant Coding, just to see if I applied in the right place.

Thank you

Embrace
geovani
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 20
Joined: Sat, 06 Aug 2016 9:22 am
Location: Joinville-SC
Contact:
Geovani Cristofolini
Consultor de Business intelligence
http://useweknow.com

Just include the fields below, follows the code:

Select all

select 
	a.nr_atendimento, 
	substr(obter_min_entre_datas(a.dt_entrada,a.dt_atend_medico,null),1,50) qt_tempo_espera, 
	substr(obter_min_entre_datas(a.dt_atend_medico,a.dt_fim_consulta,null),1,50) qt_temp_atend, 
	substr(obter_nome_pf(a.cd_pessoa_fisica),1,50) nm_paciente, 
	substr(obter_nome_pf(a.cd_medico_resp),1,50) nm_medico, 
	a.dt_entrada, 
	a.dt_inicio_atendimento, 
	a.dt_fim_triagem, 
	a.dt_alta, 
	substr(obter_dif_data(a.dt_inicio_atendimento,a.dt_fim_triagem,null),1,20) teste, 
	substr(obter_dif_data(a.dt_entrada,a.dt_alta,null),1,20) qt_tempo_atendimento, 
	trunc((((a.dt_atend_medico - a.dt_entrada) + (a.dt_fim_consulta - a.dt_atend_medico)) * 86400)/3600) hora, 
	trunc(mod(((((a.dt_atend_medico - a.dt_entrada) + (a.dt_fim_consulta - a.dt_atend_medico)) * 86400)/60),60)) minuto, 
	lpad(trunc((((a.dt_atend_medico - a.dt_entrada) + (a.dt_fim_consulta - a.dt_atend_medico)) * 86400)/3600),2,0) ||':'|| 
    	lpad(trunc(mod(((((a.dt_atend_medico - a.dt_entrada) + (a.dt_fim_consulta - a.dt_atend_medico)) * 86400)/60),60)),2,0) hhmm     
from 
	atendimento_paciente a     
where 
	a.dt_entrada between trunc(:dt_inicial) and fim_dia(:dt_final)   
	and (:cd_setor_atendimento = obter_setor_atendimento(a.nr_atendimento) or :cd_setor_atendimento = '0') 
order by 
	a.dt_entrada
jiopra
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 13
Joined: Mon, 29 Aug 2016 11:10 am

Eitaaaa geovani

I discovered the problem mano, my report can only add numerics values, so it is not working, I need to convert the values ??of the two columns

Select all

   substr(obter_min_entre_datas(a.dt_entrada,a.dt_atend_medico,null),1,50) qt_tempo_espera, 
   substr(obter_min_entre_datas(a.dt_atend_medico,a.dt_fim_consulta,null),1,50) qt_temp_atend,
For a numerical value so I can do the total, you know how to make this change? I know the date field is date.

Abração
geovani
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 20
Joined: Sat, 06 Aug 2016 9:22 am
Location: Joinville-SC
Contact:
Geovani Cristofolini
Consultor de Business intelligence
http://useweknow.com

I do not understand what you need.

This function gets_min_entre_dates returns in which format? If it is numeric simply add:

Select all

substr(obter_min_entre_datas(a.dt_entrada,a.dt_atend_medico,null),1,50) +  
substr(obter_min_entre_datas(a.dt_atend_medico,a.dt_fim_consulta,null),1,50) 
jiopra
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 13
Joined: Mon, 29 Aug 2016 11:10 am

Thus Geovani, see if this is possible,

Select all

substr(obter_min_entre_datas(a.dt_entrada,a.dt_atend_medico,null),1,50) qt_tempo_espera
This function gets the two fields that I am using a.dt_entada and a.dt_atend_medico both are type date Soon the column qt_tempo_ space is also date, would need to convert the data before being shown in the column for a number type that I can perform the sum of the column all by my own system reprintion manager.

managed to understand me?

kkkkk
geovani
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 20
Joined: Sat, 06 Aug 2016 9:22 am
Location: Joinville-SC
Contact:
Geovani Cristofolini
Consultor de Business intelligence
http://useweknow.com

Are you sure that the return is a date? By the function name it seems that it returns a number, lol.

passes the function to understand better.

Returns even a date, just multiply by 86400 to convert in seconds.

Ex:

Select all

obter_min_entre_datas(a.dt_entrada,a.dt_atend_medico,null) * 86400 qt_tempo_espera
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

Good morning,

See if this function helps something.

Select all

create or replace  
function Fn_Intervalo_Tempo (P_Dt_Inic in date, 
                             P_Dt_Fina in date) return varchar2 is 
 v_inte  number; 
 v_Hora  varchar2(200); 
 -- 
 cursor c is 
   SELECT lpad(   extract (day    from numtodsinterval(v_inte, 'day'))    ,5,' ') Qt_Dias, 
          lpad(   extract (hour   from numtodsinterval(v_inte, 'day'))    ,2,'0') Qt_Horas, 
          lpad(   extract (minute from numtodsinterval(v_inte, 'day' ))   ,2,'0') Qt_Minutos, 
          lpad(   extract (second from numtodsinterval(v_inte, 'day'))    ,2,'0') Qt_Segundos 
   FROM   dual; 
 c_r c%rowtype; 
 -- 
begin 
 -- 
 v_inte := trunc((p_dt_Fina - p_dt_Inic),6); 
 dbms_output.put_line('v_inte     '||to_char(v_inte)); 
 -- 
 open c; 
  fetch c into c_r; 
   if c%found then 
      -- 
      dbms_output.put_line('c_r.Qt_Dias     '||c_r.Qt_Dias); 
      dbms_output.put_line('c_r.Qt_Horas    '||c_r.Qt_Horas); 
      dbms_output.put_line('c_r.Qt_Minutos  '||c_r.Qt_Minutos); 
      dbms_output.put_line('c_r.Qt_Segundos '||lpad(trunc(c_r.Qt_Segundos),2,'0')); 
      -- 
      v_hora := c_r.Qt_Dias||' '||c_r.Qt_Horas||':'||c_r.Qt_Minutos||':'||lpad(trunc(c_r.Qt_Segundos),2,'0'); 
   end if; 
 close c; 
 return v_hora; 
exception 
 when others then 
  return 'erro'; 
End; 
/
To bring the total in minutes need to treat the results.
Hour * 60
days * 1440
jiopra
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 13
Joined: Mon, 29 Aug 2016 11:10 am

Personal Thanks for the tips, but nothing solved my problem, I will try to perform in another way the adjustment.

Thanks
geovani
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 20
Joined: Sat, 06 Aug 2016 9:22 am
Location: Joinville-SC
Contact:
Geovani Cristofolini
Consultor de Business intelligence
http://useweknow.com

Jiopra,

I think we can not because it was not clear exactly what you need, but I believe the past tips and functions here you will get.

Thanks!
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 15 guests