Aprenda PL/SQL

Diferença em horas uteis (08h as 18h) entre dois campos

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

Mensagemem Ter, 02 Abr 2013 6:03 pm

Boa tarde Pessoal!

Sou novo por aqui e não tenho muita experiência com sql´s, por este motivo, peço que me ajudem a resolver esta questãozinha, estou o dia todo quebrando a cabeça, pesquisando em diversos lugares da net, mas não acho nada que possa me ajudar, ou eu não estou conseguindo encaixar no meu cenário.

Preciso gerar uma consulta que retorne a diferença entre dois campos do tipo “TimeStamp”, um contém a informação da criação do chamado e o outro contém a informação do encerramento do chamado (ou no caso de ser um chamado que ainda não foi encerrado, ele retorna as informações do sysdate).

Com essa consulta, quero mensurar o tempo médio mensal (do mês corrente) de atendimento dos chamados abertos para a nossa área de TI.

Gerei a consulta abaixo, entretanto, preciso que a diferença dos campos, leve em consideração os dias uteis e as o período entre 08h e 18h, dispensando os finais de semanas e os horários entre 18h01m e 07h59m.

Não sei se está faltando alguma informação, caso esteja, é só pedir que vou colocando aqui.

Desde já, agradeço toda atenção e apoio de todos!

Código: Selecionar todos
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'))


Resultado da consulta:

Código: Selecionar todos
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

Mensagemem Qua, 03 Abr 2013 8:26 am

Cara, lhe darei apenas orientação.
Consiga, para cada dia, extrair a quantidade de tempo(aconselho em hora) úteis em cada chamado.
Fazendo a diferença do HORAS REAL menos as HORAS não ÚTEIS..
EX: Se o chamado foi aberto as 9:47. de 12:00 as 14:00 não é hora útil. e das 18:00 as 8:00 do outro dia não for útil também... deve-se fazer (12:00 - 9:47) + (18:00 - 14:00). Observando a data de abertura e data de encerramento para realizar as devidas subtrações POR CADA DIA.
Depois você SOMA essa informação. SUM(TOT_HORAS).

Não é tão difícil quanto parece.
Nem tão fácil quanto descrevo.
:P.
DanielNN
Localização: Fortaleza - CE

att,

Daniel N.N.

Mensagemem Sex, 26 Abr 2013 6:31 pm

Boa noite pessoal!

Conversei com alguns colegas e eles me sugeriram criar uma function.

Para quem quiser, segue o código.

Noctifero, agradeço as orientações!!

Ela desconsidera os finais de semana.

Código: Selecionar todos
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

Mensagemem Qua, 06 Set 2017 10:57 am

thiago.teles escreveu:Boa noite pessoal!

Conversei com alguns colegas e eles me sugeriram criar uma function.

Para quem quiser, segue o código.

Noctifero, agradeço as orientações!!

Ela desconsidera os finais de semana.

Código: Selecionar todos
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;



------------------------------------------------------------------------

Olá,

Foi muito útil essa sua function, só que verifiquei que ela "reseta" a contagem de horas úteis quando a data inicial é em um mês e a data final é no mês subsequente.
Poderiam ajudar para que essa function continuasse a contagem quando nesses casos de meses diferentes?
Anexos
difdia.png
Exemplo data inicial (mês passado) e data final (mês subsequente)
edutavaresjiva

Mensagemem Qua, 06 Set 2017 1:58 pm

edutavaresjiva escreveu:
thiago.teles escreveu:Boa noite pessoal!

Conversei com alguns colegas e eles me sugeriram criar uma function.

Para quem quiser, segue o código.

Noctifero, agradeço as orientações!!

Ela desconsidera os finais de semana.

Código: Selecionar todos
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;



------------------------------------------------------------------------

Olá,

Foi muito útil essa sua function, só que verifiquei que ela "reseta" a contagem de horas úteis quando a data inicial é em um mês e a data final é no mês subsequente.
Poderiam ajudar para que essa function continuasse a contagem quando nesses casos de meses diferentes?


-------------------------------------------------------------------------

Um amigo DBA me ajudou e ajustou a function para minha necessidade. Está logo abaixo:

Código: Selecionar todos
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


  • Veja também
    Respostas
    ExibiÇões
    Última mensagem


Voltar para SQL

Quem está online

Usuários navegando neste fórum: Nenhum usuário registrado e 4 visitantes