Difference in hours useful (08:00 the 6:0 pm) between two fields

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Tue, 02 Apr 2013 6:03 pm

Good afternoon everyone!

I'm new here and don't have much experience with sql ´ s, for this reason, I ask you to help me solve this questãozinha, I'm racking my brains all day, searching in several places on the net, but I can't find anything that can help me, or I'm not getting fit in my scenario.

I need to generate a query that returns the difference between two fields of type \"TimeStamp\", one contains the information the creation of named and the other contains the information of the closure of the so-called (or in the case of a call that has not yet been closed, it returns the information of the sysdate).

With this query, want to measure the average monthly time (current month) availability of so-called open for our area.

I generated the query below, however, the difference of the fields, take into consideration the business days and the period between 08:00 and 6:0 pm, dispensing the weekends and times between 06:01 pm and 07:59

I don't know if you're missing any information, if you are, just ask that I'm putting here.

At the outset, I would like to thank all the attention and support of all!

Code: 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'))
the query result:
Code: 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
thiago.teles

Poston Wed, 03 Apr 2013 8:26 am

Dude, give you guidance only.
Can, for each day, extract the amount of time (I advise in time) useful in each call.
Making the difference of HOURS less time USEFUL NOT REAL ...
EX: If the call was opened the 9:47. of 12:00 the 14:00 is not useful time. and the 18:00 the 08:00 the other day is not helpful também ... you must do (12:00-9:47) + (18:00-14:00). Watching the opening date and closing date for performing the appropriate subtractions for EVERY DAY after you add that information. SUM (TOT_HORAS).

It's not as hard as it sounds.
Not as easy as I describe.
:P.
DanielNN
Location: Fortaleza - CE

att,

Daniel N.N.

Poston Fri, 26 Apr 2013 6:31 pm

Good night guys!

I spoke with some colleagues and they suggested me creating a function.

For those who want to follow the code.

Noctifero, thanks for the guidance!

She disregards the weekends.

Code: 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;
thiago.teles

Poston Wed, 06 Sep 2017 10:57 am

thiago. teles wrote: Good night guys!

I spoke with some colleagues and they suggested me creating a function.

For those who want to follow the code.

Noctifero, thank you for the guidance!

She disregards the weekends.

Code: 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;
------------------------------------------------------------------------Hello, it was very useful that your function, just checked she " " resets the count of working hours when the start date is in a month and the end date is in the subsequent month.
Could help to this function to continue the count when in such cases of different months.
Attachments
difdia.png
Exemplo data inicial (mês passado) e data final (mês subsequente)
edutavaresjiva

Poston Wed, 06 Sep 2017 1:58 pm

edutavaresjiva wrote:
thiago. teles wrote: Good night guys!

I spoke with some colleagues and they suggested me creating a function.

For those who want to follow the code.

Noctifero, thank you for the guidance!

She disregards the weekends.

Code: 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;
------------------------------------------------------------------------Hello, it was very useful that your function, just checked she " " resets the count of working hours when the start date is in a month and the end date is in the subsequent month.
Could help to this function to continue the count when in such cases of different months.
-------------------------------------------------------------------------A friend helped me DBA and set the function to my need. It's right below:
Code: 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;
edutavaresjiva


  • See also
    Replies
    Views
    Last Post


Return to SQL

Who is online

Users browsing this forum: No registered users and 3 guests