SQL - Function to calculate working hours considering business hours, working days and holidays

Question

I need a function to calculate working hours considering business hours, working day and holidays, I have searched in several websites and can not find any function that contain all these functionalities within the same code.

Answer 1

I created a function with all these features that can be useful for several people, even more that it contains different examples of data processing for the conversion of dates:

-----------------------------------------------------
--FUNCAO PARA CALCULAR HORAS UTEIS 
--CONSIDERANDO HORARIO COMERCIAL, DIA UTIL E FERIADOS
-----------------------------------------------------

CREATE OR REPLACE FUNCTION diferenca_datas_completo
(var_data_inicio IN VARCHAR2, var_data_final IN VARCHAR2)
--RECEBE OS VALORES COMO VARCHAR2

RETURN VARCHAR2 IS

  --DECLARA VARIAVEL PARA CONTABILIZAR AS HORAS UTEIS
  var_resultado NUMBER;

  --DECLARA VARIAVEIS DE RANGE MINIMO E MAXIMO
  --PARA O CALCULO DE HORAS DENTRO DO HORARIO COMERCIAL
  var_data_inicio_entrada TIMESTAMP;
  var_data_final_saida TIMESTAMP;

  --DECLARA VARIAVEIS PARA AUXILIAR NO WHILE

  --DATA VARIAVEL
  var_data_loop TIMESTAMP;

  --HORARIO UTIL VARIAVEL
  var_data_loop_inicio_util TIMESTAMP;
  var_data_loop_final_util TIMESTAMP;

  --FERIADO
  var_data_loop_feriado NUMBER;

  --DIA DA SEMANA
  var_data_loop_dia_semana NUMBER;  

BEGIN

  --VALOR PADRAO DE HORAS
  var_resultado := 0;

  ---------------------------------------------------------------------
  --DEFININDO VARIAVEIS DE RANGE MINIMO E MAXIMO PARA O CALCULO DE DATA
  ---------------------------------------------------------------------

  --CONVERTENDO AS INFORMACOES PARA DATE NO PADRAO DESEJADO
  var_data_inicio_entrada := TO_DATE(TO_CHAR(TO_DATE(var_data_inicio,'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY')
                             || ' 08:00:00','DD/MM/YYYY HH24:MI:SS');
  var_data_final_saida := TO_DATE(TO_CHAR(TO_DATE(var_data_final,'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY')
                             || ' 18:00:00','DD/MM/YYYY HH24:MI:SS');

  -------------------------
  --DEFININDO DATA VARIAVEL
  -------------------------

  var_data_loop := var_data_inicio_entrada;

  ----------------------------------
  --CALCULA DIFERENÇA DE HORAS UTEIS
  ----------------------------------

  --INICIA O LOOP
  WHILE var_data_loop < var_data_final_saida LOOP

    --DEFININDO VALOR DIA DA SEMANA
    var_data_loop_dia_semana := TO_CHAR(var_data_loop,'D');

    --DEFININDO VALORES DAS HORAS UTEIS DO DIA DO LOOP
    var_data_loop_inicio_util := TO_DATE(SUBSTR(var_data_loop,0,6)||20||SUBSTR(var_data_loop,7,2)
                                 || ' 08:00:00','DD/MM/YYYY HH24:MI:SS');
    var_data_loop_final_util  := TO_DATE(SUBSTR(var_data_loop,0,6)||20||SUBSTR(var_data_loop,7,2)
                                 || ' 18:00:00','DD/MM/YYYY HH24:MI:SS');

    --DEFININDO SE A DATA E FERIADO 0 NAO / 1 SIM  
    SELECT   
    CASE 
      WHEN TO_CHAR(var_data_loop,'DD/')||TO_CHAR(var_data_loop,'MM/')||TO_CHAR(var_data_loop,'YYYY')
        IN (SELECT
            CASE
              WHEN f.nr_dia < 10 AND f.nr_mes < 10 THEN '0' || f.Nr_Dia || '/0' || f.Nr_Mes || '/' || f.nr_ano
              WHEN f.nr_dia < 10 THEN '0' || f.Nr_Dia || '/' || f.Nr_Mes || '/' || f.nr_ano
              WHEN f.nr_mes < 10 THEN f.Nr_Dia || '/0' || f.Nr_Mes || '/' || f.nr_ano
              ELSE f.Nr_Dia || '/' || f.Nr_Mes || '/' || f.nr_ano
            END DATA_CONCATENADA
            FROM FERIADO f
            WHERE f.nr_ano >= 2020)
     THEN 1 
     ELSE 0
     END
     INTO var_data_loop_feriado --VARIAVEL DO FERIADO
     FROM DUAL; 

    --SE TODAS AS CONDIÇÔES FOREM ACEITAS SOMA 1 HORA NO VALOR DO SLA (META)

    --VALIDA DIA DA SEMANA 1 DOM / 2 SEG / 3 TER / 4 QUA / 5 QUI / 6 SEX / 7 SAB
    IF var_data_loop_dia_semana NOT IN (1,7)

    --VALIDA SE A DATA É FERIADO 0 NAO / 1 SIM
    AND var_data_loop_feriado = 0

    --VALIDA HORA UTIL
    AND var_data_loop >= var_data_loop_inicio_util
    AND var_data_loop <= var_data_loop_final_util

    --VALIDA SE O PERIODO ESTA DENTRO DOS VALORES PASSADOS NA FUNCAO
    AND var_data_loop >= var_data_inicio
    AND var_data_loop <= var_data_final

    THEN

      --VALOR HORA AUMENTA 1
      var_resultado := var_resultado + 1;

    ELSE

      --RESULTADO CONTINUA O MESMO
      var_resultado := var_resultado;

    END IF;

    --ADICIONA 1 HORA NO DATA LOOP
    var_data_loop := var_data_loop + 1/24;

  END LOOP;

  ----------------------
  --RETORNANDO RESULTADO
  ----------------------

  RETURN var_resultado; 

END;
/

Running the function by passing two char values:

-------------------------------
--EXEMPLO DE EXECUCAO DA FUNCAO
-------------------------------

--EXECUTANDO FUNCAO
SELECT diferenca_datas_completo(var_data_inicio => '25/02/2020 08:00:00',
                                var_data_final => '26/02/2020 12:50:00') AS DIFERENCA_HORAS
FROM DUAL;

In case the result will be 5 hours, considering that day 25 is carnival holiday and the time of entry is 8 am.

Creating the Holiday Table:

-------------------------------
--EXEMPLO DA TABELA DE FERIADOS
-------------------------------

--CRIANDO TABELA DE FERIADO
CREATE TABLE FERIADO
(
  nr_dia     NUMBER(2) not null,
  nr_mes     NUMBER(2) not null,
  nr_ano     NUMBER(4),
  ds_feriado VARCHAR2(30) not null,
  cd_feriado NUMBER(12) not null
)

--SELECT TABELA FERIADO
SELECT * FROM FERIADO;

Other examples of code created:

---------------------------------
--EXEMPLOS DAS VARIAVEIS DE DADOS
---------------------------------

--HORA INICIAL PRIMEIRO DIA
SELECT TO_CHAR(to_date('22/04/2020 12:40:33','DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY') || ' 08:00:00' FROM DUAL;

--HORA INICIAL PRIMEIRO DIA + 1HORA (POSSIBILITA TROCAR O DIA)
SELECT TO_DATE(
       TO_CHAR(to_date('20/02/2020 12:40:33','DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY') || ' 08:00:00','DD/MM/YYYY HH24:MI:SS')
       + 1/24
       FROM DUAL;

--HORA FINAL ULTIMO DIA 
SELECT TO_CHAR(to_date('23/04/2020 12:40:33','DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY') || ' 18:00:00' FROM DUAL;

--DIA DA SEMANA 1 DOM / 2 SEG / 3 TER / 4 QUA / 5 QUI / 6 SEX / 7 SAB
SELECT TO_CHAR(to_date('23/02/2020 08:00:00','DD/MM/YYYY HH24:MI:SS'),'D')
FROM DUAL;

--FERIADOS
SELECT * FROM FERIADO;

--FERIADOS COM FORMATO PADRAO
SELECT 
CASE 
  WHEN f.nr_dia < 10 AND f.nr_mes < 10 THEN '0' || f.Nr_Dia || '/0' || f.Nr_Mes || '/' || f.nr_ano
  WHEN f.nr_dia < 10 THEN '0' || f.Nr_Dia || '/' || f.Nr_Mes || '/' || f.nr_ano
  WHEN f.nr_mes < 10 THEN f.Nr_Dia || '/0' || f.Nr_Mes || '/' || f.nr_ano  
  ELSE f.Nr_Dia || '/' || f.Nr_Mes || '/' || f.nr_ano
END DATA_CONCATENADA
FROM FERIADO f
WHERE f.nr_ano >= 2020;

I hope I’ve helped!