I'm new here and I do not have much experience with SQL's, for this reason, I ask you to help me solve this question, I'm breaking your head, researching in several places of the net, but I do not think anything I can help me, or I can not fit into my scenario.
I need to generate a query that returns the difference between two TIMESTAMP fields, one contains the creation information of the call and the other contains the call closure information (or in the case of Being a call that has not yet been closed, it returns the sysdate information).
With this query, I want to measure the average monthly time (of the current month) of calls open to our IT area.
I generated the consultation below, however, I need the difference in the fields, take into account the working days and the period between 08h and 18h, dispensing the weekends and times between 18h01m and 07h59m.
I do not know if any information is missing, if you are, just ask that I will put it here.
From now on, thank you for all the attention and support of all!
SELECT DISTINCT TO_CHAR(P.STARTDATE, 'DD-MM-YYYY HH24:MI') AS DATAINICIO,
Case
when chamadoti0_.dtencerramento is null then
TO_char(sysdate, 'DD-MM-YYYY HH24:MI')
else
TO_char(chamadoti0_.dtencerramento, 'DD-MM-YYYY HH24:MI')
end AS DATAFIM,
case
when chamadoti0_.dtencerramento is null then
sysdate - P.STARTDATE
else
chamadoti0_.dtencerramento - P.STARTDATE
end as Diferença,
trunc(to_number(substr((chamadoti0_.dtencerramento -
P.STARTDATE),
1,
instr(chamadoti0_.dtencerramento -
P.STARTDATE,
' ')))) dias,
substr((chamadoti0_.dtencerramento - P.STARTDATE),
instr((chamadoti0_.dtencerramento - P.STARTDATE), ' ') + 1,
2) horas,
substr((chamadoti0_.dtencerramento - P.STARTDATE),
instr((chamadoti0_.dtencerramento - P.STARTDATE), ' ') + 4,
2) minutos,
substr((chamadoti0_.dtencerramento - P.STARTDATE),
instr((chamadoti0_.dtencerramento - P.STARTDATE), ' ') + 7,
2) segundos
FROM D_CHAMADOTI CHAMADOTI0_
LEFT OUTER JOIN D_TIPOCLASSIFICACAO TIPOCLASSI4_
ON CHAMADOTI0_.TIPODECHAMADO_NEOID = TIPOCLASSI4_.NEOID
LEFT OUTER JOIN WFPROCESS P
ON CHAMADOTI0_.WFPROCESS_NEOID = P.NEOID
WHERE P.SAVED = 1
AND (P.PROCESSSTATE = 1 OR P.PROCESSSTATE = 0)
and TIPOCLASSI4_.NOMETIPOCLASSIFICACAO = 'TI-Computadores'
and EXTRACT(YEAR FROM P.STARTDATE) = to_char(sysdate, 'YYYY')
and TO_CHAR(P.STARTDATE, 'IW') = (to_char(sysdate, 'IW'))
DATAINICIO DATAFIM DIFERENÇA DIAS HORAS MINUTOS SEGUNDOS
---------------- ---------------- ------------------- ---------- ----- ------- --------
01-04-2013 14:19 01-04-2013 15:19 +000000000 01:00:25 0 01 00 25
01-04-2013 11:40 01-04-2013 11:44 +000000000 00:03:20 0 00 03 20
01-04-2013 11:35 01-04-2013 11:37 +000000000 00:02:19 0 00 02 19
01-04-2013 11:39 01-04-2013 11:43 +000000000 00:03:35 0 00 03 35
02-04-2013 10:15 02-04-2013 10:34 +000000000 00:19:28 0 00 19 28
02-04-2013 16:22 02-04-2013 18:02 +000000000 01:39:40
01-04-2013 10:03 01-04-2013 10:32 +000000000 00:29:01 0 00 29 01
02-04-2013 09:54 02-04-2013 10:08 +000000000 00:13:24 0 00 13 24
01-04-2013 11:04 01-04-2013 11:39 +000000000 00:34:59 0 00 34 59
02-04-2013 10:49 02-04-2013 10:52 +000000000 00:03:16 0 00 03 16
02-04-2013 16:28 02-04-2013 18:02 +000000000 01:34:02
01-04-2013 09:49 01-04-2013 10:33 +000000000 00:43:27 0 00 43 27
02-04-2013 15:00 02-04-2013 15:25 +000000000 00:24:46 0 00 24 46
01-04-2013 09:44 01-04-2013 10:31 +000000000 00:47:39 0 00 47 39
02-04-2013 17:14 02-04-2013 17:38 +000000000 00:23:43 0 00 23 43
02-04-2013 11:20 02-04-2013 11:30 +000000000 00:10:17 0 00 10 17
01-04-2013 09:07 01-04-2013 09:21 +000000000 00:14:12 0 00 14 12
01-04-2013 14:52 01-04-2013 15:07 +000000000 00:15:10 0 00 15 10
02-04-2013 16:21 02-04-2013 17:39 +000000000 01:17:39 0 01 17 39
02-04-2013 16:52 02-04-2013 16:58 +000000000 00:05:38 0 00 05 38