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
SQL Assistance for Report
I have a SQL here q is the basis of a reproduction of my system related to medical care waiting time:
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?
-
- 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
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:
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:
substr(obter_min_entre_datas(a.dt_entrada, a.dt_fim_consulta,null),1,50) qt_tempo_total
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.
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.
-
- 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
Consultor de Business intelligence
http://useweknow.com
See if so you need:
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
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
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
-
- 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
Consultor de Business intelligence
http://useweknow.com
Just include the fields below, follows the code:
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
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
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
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
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,
Abração
-
- 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
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:
This function gets_min_entre_dates returns in which format? If it is numeric simply add:
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)
Thus Geovani, see if this is possible,
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
substr(obter_min_entre_datas(a.dt_entrada,a.dt_atend_medico,null),1,50) qt_tempo_espera
managed to understand me?
kkkkk
-
- 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
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:
passes the function to understand better.
Returns even a date, just multiply by 86400 to convert in seconds.
Ex:
obter_min_entre_datas(a.dt_entrada,a.dt_atend_medico,null) * 86400 qt_tempo_espera
Good morning,
See if this function helps something.
To bring the total in minutes need to treat the results.
Hour * 60
days * 1440
See if this function helps something.
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;
/
Hour * 60
days * 1440
-
- 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
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!
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!
-
- Information
-
Who is online
Users browsing this forum: No registered users and 37 guests