Difference in working hours (08 hours at 6pm) between two fields

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
thiago.teles
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Posts: 2
Joined: Tue, 02 Apr 2013 3:43 pm

Good afternoon people!

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 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'))
Result of the query:

Select all

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
DanielNN
Moderador
Moderador
Posts: 641
Joined: Mon, 03 Sep 2007 3:26 pm
Location: Fortaleza - CE
att,

Daniel N.N.

Dude, I'll give you only guidance.
Get, for each day, extract the amount of time (I advise useful) useful in each call.
making the real time difference less than not useful hours ..
Ex: If the call was opened at 9:47. From 12:00 to 14:00 it's not useful time. And from 6:00 p.m. the other day is not useful tb ... one must do (12:00 - 9:47) + (18:00 - 14:00). Observing the opening date and closing date to carry out the due subtractions for each day.
Then you add this information. Sum (tot_horas).

is not as difficult as it seems.
nor as easy as describing.
: p.
thiago.teles
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Posts: 2
Joined: Tue, 02 Apr 2013 3:43 pm

Good night people!

I talked to some colleagues and they suggested to create a function.

For anyone who wants, follow the code.

Noctifero, thank you for the guidelines !!

She disregards the weekends.

Select all

CREATE OR REPLACE FUNCTION DifDias(StartDate DATE, EndDate DATE) RETURN NUMBER AS 
 
--DECLARACAO VARIAVEIS 
Dtini DATE; 
Dtini_2 DATE; 
StrData VARCHAR2(27); 
 
RETORNO NUMBER; 
RETORNOAUX NUMBER; 
 
DiaSemana NUMBER; 
 
BEGIN 
 
--INICIALIZAR VARIAVEIS 
RETORNO := 0; 
 
StrData := TO_CHAR(StartDate, 'DD/MM/YYYY') || ' ' || '17:48:00'; 
Dtini := to_date(StrData,'DD/MM/YYYY HH24:MI:SS'); 
StrData := TO_CHAR(EndDate, 'DD/MM/YYYY') || ' ' || '08:00:00'; 
Dtini_2 := to_date(StrData,'DD/MM/YYYY HH24:MI:SS'); 
 
--Verificar o dia da semana tem de ser 2 a 6 
SELECT to_char(StartDate, 'D') INTO DiaSemana FROM dual; 
 
IF (to_char(StartDate,'DD/MM/YYYY') = to_char(EndDate,'DD/MM/YYYY')) THEN --Verifica se fez no mesmo dia 
   IF ((DiaSemana <> 7) AND (DiaSemana <> 1)) THEN -- não calcula sabado e domingo 
      select 24*( ((EndDate - StartDate) - 
           trunc (StartDate - EndDate)) *60 ) INTO RETORNO 
      from dual; 
   END IF; 
ELSE 
   WHILE ( to_char(Dtini,'DD/MM/YYYY') <= to_char(EndDate,'DD/MM/YYYY'))  LOOP 
      IF ((DiaSemana = 7) OR (DiaSemana = 1)) THEN -- não calcula sabado e domingo 
         SELECT Dtini + 1 INTO Dtini FROM dual; 
      ELSE 
         IF ( to_char(Dtini,'DD/MM/YYYY') = to_char(StartDate,'DD/MM/YYYY')) THEN 
             select 24*((Dtini - StartDate) *60 ) INTO RETORNOAUX from dual; 
             RETORNO:= RETORNO + RETORNOAUX; 
         ELSE 
           IF ( to_char(Dtini,'DD/MM/YYYY') = to_char(EndDate,'DD/MM/YYYY')) THEN 
               select 24*((EndDate - Dtini_2) *60 ) INTO RETORNOAUX from dual; 
               IF retornoaux < 0 THEN 
                 RETORNOAUX :=retornoaux * (-1); 
               END IF; 
               RETORNO:= RETORNO + RETORNOAUX; 
           ELSE 
               RETORNO := RETORNO + 528; --Corresponde a 8:48 
           END IF; 
         END IF; 
         SELECT Dtini + 1 INTO Dtini FROM dual; 
      END IF; 
      SELECT to_char(Dtini, 'D') INTO DiaSemana FROM dual; 
   END LOOP; 
END IF; 
 
RETURN RETORNO; 
 
END DifDias;
edutavaresjiva
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 4
Joined: Wed, 06 Sep 2017 10:47 am

Hello,

It was very useful this function, only that I have verified that it "reset" the business hours count when the initial date is in a month and the final date is in the subsequent month.
could help so that this function continue the count when in these different months cases?
Attachments
Exemplo data inicial (mês passado) e data final (mês subsequente)
Exemplo data inicial (mês passado) e data final (mês subsequente)
edutavaresjiva
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 4
Joined: Wed, 06 Sep 2017 10:47 am

A DBA friend helped me and set FUNCTION for my need. Is just below:

Select all

 
CREATE OR REPLACE FUNCTION DifDias(StartDate DATE, EndDate DATE) RETURN NUMBER AS 
 
--DECLARACAO VARIAVEIS 
Dtini DATE; 
Dtini_2 DATE; 
StrData VARCHAR2(27); 
 
RETORNO NUMBER; 
RETORNOAUX NUMBER; 
 
DiaSemana NUMBER; 
 
BEGIN 
 
--INICIALIZAR VARIAVEIS 
RETORNO := 0; 
 
StrData := TO_CHAR(StartDate, 'DD/MM/YYYY') || ' ' || '17:30:00'; 
Dtini := to_date(StrData,'DD/MM/YYYY HH24:MI:SS'); 
StrData := TO_CHAR(EndDate, 'DD/MM/YYYY') || ' ' || '07:30:00'; 
Dtini_2 := to_date(StrData,'DD/MM/YYYY HH24:MI:SS'); 
 
--Verificar o dia da semana tem de ser 2 a 6 
SELECT to_char(StartDate, 'D') INTO DiaSemana FROM dual; 
 
IF (TO_DATE(StartDate,'DD/MM/YYYY') = TO_DATE(EndDate,'DD/MM/YYYY')) THEN --Verifica se fez no mesmo dia 
   IF ((DiaSemana <> 7) AND (DiaSemana <> 1)) THEN -- não calcula sabado e domingo 
      select 24*( ((EndDate - StartDate) - 
           trunc (StartDate - EndDate)) *60 ) INTO RETORNO 
      from dual; 
   END IF; 
ELSE 
   WHILE ( TO_DATE(Dtini,'DD/MM/YYYY') <= TO_DATE(EndDate,'DD/MM/YYYY'))  LOOP 
      IF ((DiaSemana = 7) OR (DiaSemana = 1)) THEN -- não calcula sabado e domingo 
         SELECT Dtini + 1 INTO Dtini FROM dual; 
      ELSE 
         IF ( TO_DATE(Dtini,'DD/MM/YYYY') = TO_DATE(StartDate,'DD/MM/YYYY')) THEN 
             select 24*((Dtini - StartDate) *60 ) INTO RETORNOAUX from dual; 
             RETORNO:= RETORNO + RETORNOAUX; 
         ELSE 
           IF ( TO_DATE(Dtini,'DD/MM/YYYY') = TO_DATE(EndDate,'DD/MM/YYYY')) THEN 
               select 24*((EndDate - Dtini_2) *60 ) INTO RETORNOAUX from dual; 
               IF retornoaux < 0 THEN 
                 RETORNOAUX :=retornoaux * (-1); 
               END IF; 
               RETORNO:= RETORNO + RETORNOAUX; 
           ELSE 
               RETORNO := RETORNO + 528; --Corresponde a 8:48 
           END IF; 
         END IF; 
         SELECT Dtini + 1 INTO Dtini FROM dual; 
      END IF; 
      SELECT to_char(Dtini, 'D') INTO DiaSemana FROM dual; 
   END LOOP; 
END IF; 
 
RETURN RETORNO; 
 
END DifDias; 
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 19 guests