CALCULATION TIME (SECONDS) BETWEEN TABLE FIELDS

Asked

Viewed 42 times

-2

good afternoon! I need to calculate the time in seconds of results from the same table/column, only different Status.

SELECT  
e.nm_paciente,
e.ds_idade,
e.ds_sexo,
e.NR_PRONTUARIO,
e.DS_ANOS_MESES,
e.DT_NASCIMENTO,
a.nr_prescricao,
e.nr_atendimento,
e.ds_tipo_atendimento,
e.ds_convenio,
TO_CHAR(e.dt_entrada, 'dd/mm/yyyy hh24:mi') dt_entrada,
TO_CHAR(c.DT_PRESCRICAO, 'dd/mm/yyyy hh24:mi') DT_PRESCRICAO,
' '||e.nm_unidade nm_unidade,
e.ds_setor_atendimento,
m.nr_crm,
g.nm_pessoa_fisica nm_medico,
substr(lab_obter_desc_motivo_recoleta(d.nr_seq_recoleta),1,90) ds_motivo_recoleta,
decode(a.ie_exame_bloqueado,'S','Bloqueado', decode(a.ie_exame_bloqueado,'R','Repetição', decode(obter_se_recoleta_proc_etapa(a.nr_prescricao,a.nr_seq_prescricao,a.nr_sequencia),'S','Recoleta',''))) as ds_exame_bloqueado,
n.NM_EXAME,
a.ie_etapa,
substr(Obter_Valor_Dominio_Status_LIS(a.ie_etapa),1,90) ds_status,
b.ds_usuario,
to_char(a.dt_atualizacao,('dd/mm/yyyy hh24:mi:ss')) dt_atualizacao,
'00' || d.nr_prescricao || 'M' || o.nr_sequencia TUBETE
                        
FROM    
prescr_proc_etapa a,
usuario b,
prescr_medica c,
prescr_procedimento d,
atendimento_paciente_v e,
pessoa_fisica g,
medico m,
exame_laboratorio n,
material_exame_lab o
WHERE  
upper(a.nm_usuario)       = upper(b.nm_usuario(+))
and  a.nr_prescricao      in (87935)  -- 88835, 87935
and  a.nr_seq_prescricao  = 1
and a.nr_prescricao       = c.nr_prescricao
and c.nr_prescricao       = d.nr_prescricao
and e.nr_atendimento      = c.nr_atendimento
and c.cd_medico           = g.cd_pessoa_fisica
and m.cd_pessoa_fisica    = g.cd_pessoa_fisica
and d.nr_seq_exame        = n.nr_seq_exame
and o.nr_sequencia        = obter_mat_exame_lab_prescr(d.nr_prescricao, d.nr_sequencia, 1)
ORDER BY 
e.nm_paciente, a.nr_prescricao, n.NM_EXAME, a.dt_atualizacao, a.ie_etapa asc



10  Solicitação liberada    RENAN KAWAMOTO FAGUNDES 07/10/2020 16:11:22
20  Coletado    RENAN KAWAMOTO FAGUNDES 07/10/2020 16:14:24
25  Recebido Lab    RENAN KAWAMOTO FAGUNDES 07/10/2020 16:14:37
30  Digitação do resultado  RENAN KAWAMOTO FAGUNDES 07/10/2020 16:14:41
35  Aprovação do resultado  RENAN KAWAMOTO FAGUNDES 07/10/2020 16:15:41
40  Liberação do exame  RENAN KAWAMOTO FAGUNDES 07/10/2020 16:15:45

Request Released x Collected Collected x Received Lab Received Lab X Typing of the result Typing the result X Approval of the result Approval of the result X Examination clearance

sql oracle

  • The difference between two dates is a value in days or fractions , for seconds multiply by 86400 (seconds in a day) .... select 86400*(to_date('19/10/2020 13:09:00','dd/mm/yyyy hh24:mi:ss') - to_date('19/10/2020 12:34:17','dd/mm/yyyy hh24:mi:ss')) x from dual

  • The way to calculate everything well, but the problem that would need to use the same field, only different Status.

  • Your question is not clear... what it is Status diferentes for you? What result would you like to get?

  • Maybe using subselects to catch the shortest date and the longest date can help you.

  • Search for LEAD and LAG, I believe this is https://www.devmedia.com.br/funcoes-analiticas-em-oracle-dense_rank-e-lag-lead/4151

  • @Brunowarmling If you look at the image, I have several dates/times in the same table/column, the only difference is different status. 10 Request released RENAN KAWAMOTO FAGUNDES 07/10/2020 16:11:22 20 Collected RENAN KAWAMOTO FAGUNDES 07/10/2020 16:14:24 25 Received Lab RENAN KAWAMOTO FAGUNDES 07/10/2020 16:14:37 30 Input result RENAN KAWAMOTO FAGUNDES 07/10/2020 16:14:41 35 Approval of the result RENAN KAWAMOTO FAGUNDES 07/10/2020 16:15:41 40 Release of the exam RENAN KAWAMOTO FAGUNDES 07/10/2020 16:15:45

Show 1 more comment

1 answer

0

Following Image with attached results, only Different statusinserir a descrição da imagem aqui

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.