Date Working Hours - SQL Server

Question

Guys, I have a question. In the query below, in the tarexpiration field, he is counting the hours normally, however, I want to count only the working hours.

NOTE: Already has a useful hours function called horas_uteis_worked NOTE: This @Time variable is picking the hours from another table.

Ex: Let’s assume that @Hora is 6 Hours, so today the result is coming out that way. DataAberturaSistema Tarvencimento 2017-11-06 14:00:00.000 2017-11-06 20:00:000

Note that he is adding two more hours after 6:00 pm, however, the working time that is in the function is 8:00 to 6:00 pm, and I want him to add only the useful hours that in this case was to leave that way. DataAberturaSistema Tarvencimento 2017-11-06 14:00:00.000 2017-11-07 10:00:000

CONSULTATION

DECLARE @HORA INT

SET @HORA = (select max(C01) from FRM_50 where TarefaID = 170768)

select DataAberturaSistema,
DATEADD(HOUR,@HORA,tarvencimento)
from Tarefa where TarID = 170768

OFFICE HOURS WORKED

USE [0800net_PRD]
GO
/****** Object:  UserDefinedFunction [dbo].[horas_uteis_trabalhadas]    Script Date: 06/11/2017 17:19:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION  [dbo].[horas_uteis_trabalhadas]  (@DATA_INICIAL DATETIME, @DATA_FINAL DATETIME)
RETURNS VARCHAR(20)
AS

 BEGIN
 IF(@DATA_FINAL IS NULL)
 BEGIN
 RETURN NULL
 END

    DECLARE @CONTDIAS INT 
    DECLARE @CONTHORAS INT
    DECLARE @CONTMINUTOS INT
    DECLARE @CONTROLADORH DATE
    DECLARE @CONTROLADORM DATETIME

    SET @DATA_INICIAL = CAST(@DATA_INICIAL AS datetime)
    SET @DATA_FINAL   = CAST(@DATA_FINAL   AS datetime)

    SET @CONTDIAS = (dbo.dias_uteis(@DATA_INICIAL, @DATA_FINAL))
    SET @CONTHORAS = 0
    SET @CONTMINUTOS = 0

    IF(CAST(@DATA_INICIAL AS DATE) = CAST(@DATA_FINAL AS DATE))
    BEGIN
    SET @CONTDIAS = (dbo.dias_uteis(@DATA_INICIAL, @DATA_FINAL)/19)
    END

    IF(DATEDIFF(HOUR, (CONVERT(DATE, @DATA_INICIAL)), @DATA_INICIAL) BETWEEN 12 AND 13)
    BEGIN
    SET @DATA_INICIAL = CAST(CAST(@DATA_INICIAL AS DATE) AS DATETIME) +' 13:00:00' 
    END

    IF(DATEDIFF(HOUR, (CONVERT(DATE, @DATA_INICIAL)), @DATA_INICIAL) > 18)
    BEGIN
    SET @DATA_INICIAL = CAST(CAST(@DATA_INICIAL AS DATE) AS DATETIME) +' 18:00:00'
    END

    IF(DATEDIFF(HOUR, (CONVERT(DATE, @DATA_FINAL)), @DATA_FINAL) BETWEEN 12 AND 13)
    BEGIN
    SET @DATA_FINAL = CAST(CAST(@DATA_FINAL AS DATE) AS DATETIME) +' 12:00:00'
    END

    IF(DATEDIFF(HOUR, (CONVERT(DATE, @DATA_FINAL)), @DATA_FINAL) > 18)
    BEGIN
    SET @DATA_FINAL = CAST(CAST(@DATA_FINAL AS DATE) AS DATETIME) +' 18:00:00' 
    END

--CONTADOR DE DIAS------------------------------------------------------------------------------------      
          BEGIN
            IF @CONTDIAS > 1 
             BEGIN
              SET @CONTDIAS = @CONTDIAS - 2
             END
            ELSE
             BEGIN
              SET @CONTDIAS = @CONTDIAS - 1
             END
           END


--CONTADOR DE MINUTOS-----------------------------------------------------------------------------------
         BEGIN
            IF DATEDIFF(MINUTE, (CONVERT(DATE, @DATA_INICIAL)), @DATA_INICIAL) >= 780
             BEGIN
              SET @CONTMINUTOS = 1080 - (DATEDIFF(MINUTE, (CONVERT(DATE, @DATA_INICIAL)), @DATA_INICIAL))
             END 
            IF DATEDIFF(MINUTE, (CONVERT(DATE, @DATA_INICIAL)), @DATA_INICIAL) < 780
             BEGIN
              SET @CONTMINUTOS = 1020 - (DATEDIFF(MINUTE, (CONVERT(DATE, @DATA_INICIAL)), @DATA_INICIAL))
             END
         END

         BEGIN
            IF DATEDIFF(MINUTE, (CONVERT(DATE, @DATA_FINAL)), @DATA_FINAL) > 780
             BEGIN
              SET @CONTMINUTOS = @CONTMINUTOS + ((DATEDIFF(MINUTE, (CONVERT(DATE, @DATA_FINAL)), @DATA_FINAL)-540))
             END 
            IF DATEDIFF(MINUTE, (CONVERT(DATE, @DATA_FINAL)), @DATA_FINAL) < 780
             BEGIN
              SET @CONTMINUTOS = @CONTMINUTOS + ((DATEDIFF(MINUTE, (CONVERT(DATE, @DATA_FINAL)), @DATA_FINAL)-480))
             END
         END
--------------------------------------------------------------------------------------------------------------------------------------------------
    SET @CONTHORAS = @CONTMINUTOS
        BEGIN
          IF @CONTMINUTOS > 60
           BEGIN
            SET @CONTMINUTOS = @CONTMINUTOS%60
           END
        END 


       BEGIN 
        IF @CONTHORAS > 60
           BEGIN
            SET @CONTHORAS = @CONTHORAS /60 
           END
        END 

--------------------------------------------------------------------------------------------------------------
RETURN CONCAT(CAST(@CONTHORAS + (@CONTDIAS * 9) AS VARCHAR),':',CASE WHEN @CONTMINUTOS < 10 THEN CONCAT('0',CAST(@CONTMINUTOS AS VARCHAR)) ELSE CAST(@CONTMINUTOS  AS VARCHAR(5))END)
END

Answer 1

You can create a function that increments by checking if the time is within the range:

IF OBJECT_ID('dbo.adicionar_horas_uteis', 'FN') IS NULL
BEGIN
  EXEC('CREATE FUNCTION dbo.adicionar_horas_uteis() RETURNS DATETIME AS BEGIN RETURN GETDATE(); END;')
END;
GO

ALTER FUNCTION dbo.adicionar_horas_uteis(@data_base  DATETIME,
                                         @quantidade INT)
RETURNS DATETIME
AS
BEGIN
  DECLARE @data_final DATETIME,
          @hora       INT,
          @contador   INT;

  SET @contador = 0;
  SET @data_final = @data_base;

  -- Para quando o contador estiver dentro do satisfeito pelo parâmetro
  WHILE @contador < @quantidade
  BEGIN
    SET @data_final = DATEADD(HOUR, 1, @data_final); -- Adiciona uma hora ao referencial
    SET @hora = DATEPART(HOUR, @data_final);

    -- Só incrementa o contador se a hora estiver entre os intervalos determinados, no caso deve começar com 9 porquê o horário "8" não significa incremento
    IF @hora BETWEEN 9 AND 18
    BEGIN
      SET @contador = @contador + 1;
    END;
  END;

  RETURN @data_final;
END;
GO

Replace in your query as follows:

...

SELECT DataAberturaSistema,
       adicionar_horas_uteis(t.tarvencimento, @hora)
 FROM Tarefa t
WHERE t.TarID = 170768

Answer 2

If we consider working hours worked encompassing hours, minutes and seconds, the routine becomes a little more complex.

To answer your question, I have adapted a routine that returns the working hours, minutes and seconds worked within a time interval (@HoraEntrada and @HoraSaida).

Before posting the response routine, I will post my routine to facilitate understanding.

Worked Working Hours (returns working hours, minutes and seconds)

DECLARE @HoraEntrada time = '08:23'
DECLARE @HoraSaida time = '14:30'

----------------------------------------------
DECLARE @TempoUtilTrab int = 0

--Tabela para guardar os intervalos de horas úteis efetivamente trabalhados 
DECLARE @PeriodoUtilTrab TABLE (InicioPeriodoUtilTrab time, FimPeriodoUtilTrab time)

--Tabela para guardar os intervalos de horas úteis
DECLARE @JanelaHoraUtil TABLE (InicioPeriodoUtil time, FimPeriodoUtil time)

--Incluir os intevalos de horas úteis
INSERT @JanelaHoraUtil VALUES ('08:00','12:00')
INSERT @JanelaHoraUtil VALUES ('13:00','18:00')

--Incluir na tabela de períodos efetivamente trabalhados os intervalos 
--de horas que interseccionam com o período definido por @HoraEntrada e @HoraSaida
INSERT @PeriodoUtilTrab
    SELECT InicioInterseccao 
               = CASE WHEN @HoraEntrada < InicioPeriodoUtil 
                      THEN InicioPeriodoUtil 
                      ELSE @HoraEntrada END
          ,FimInterseccao 
               = CASE WHEN @HoraSaida > FimPeriodoUtil 
                      THEN FimPeriodoUtil 
                      ELSE @HoraSaida END
      FROM @JanelaHoraUtil
     WHERE @HoraEntrada <= FimPeriodoUtil
       AND @HoraSaida >= InicioPeriodoUtil

--Totalizar (em segundos) o tempo trabalhado de acordo com os períodos
--efetivamente trabalhados 
SELECT @TempoUtilTrab = @TempoUtilTrab 
             + DATEDIFF(second,InicioPeriodoUtilTrab,FimPeriodoUtilTrab)
  FROM @PeriodoUtilTrab

--Períodos efetivamente trabalhados
SELECT * FROM @PeriodoUtilTrab

--Qtde de horas uteis efetivamente trabalhadas (convertendo de segundos para time)
SELECT HoraUtilTrab = CAST(DATEADD(SECOND,@TempoUtilTrab,0) AS time)

Answer to the Question - Adapted Routine

DECLARE @DataAberturaSistema datetime = '07/11/2017 14:21'
DECLARE @Hora int = 6

DECLARE @DataInicio datetime = @DataAberturaSistema
DECLARE @DataFim datetime = DATEADD(HOUR,@Hora,@DataAberturaSistema)

DECLARE @TempoCont int = DATEDIFF(second,@DataInicio,@DataFim)
DECLARE @DiaCont Datetime = CAST(@DataInicio as date)
------------------------------------------------

DECLARE @HoraEntrada time = CAST(@DataAberturaSistema as time)
DECLARE @HoraSaida time = CAST(@DataFim as time)

----------------------------------------------
DECLARE @HoraFim as time
DECLARE @TempoUtilTrab int 
DECLARE @InicioExpediente time
DECLARE @TerminoExpediente time

--Tabela para guardar os intervalos de horas úteis efetivamente trabalhados 
DECLARE @PeriodoUtilTrab TABLE (InicioPeriodoUtilTrab time, FimPeriodoUtilTrab time)

--Tabela para guardar os intervalos de horas úteis
DECLARE @JanelaHoraUtil TABLE (InicioPeriodoUtil time, FimPeriodoUtil time)

--Incluir os intevalos de horas úteis
INSERT @JanelaHoraUtil VALUES ('08:00','12:00')
INSERT @JanelaHoraUtil VALUES ('13:00','18:00')

--Buscar a hora de início do expediente
SELECT @InicioExpediente = MIN(InicioPeriodoUtil)
    FROM @JanelaHoraUtil

--Buscar a hora de término do expediente
SELECT @TerminoExpediente = Max(FimPeriodoUtil)
    FROM @JanelaHoraUtil

WHILE @TempoCont > 0
BEGIN

    IF CAST(@DataFim as date) > CAST(@DataInicio as date)
        SET @HoraSaida = @TerminoExpediente

    SET @TempoUtilTrab = 0

    DELETE @PeriodoUtilTrab

    --Incluir na tabela de períodos efetivamente trabalhados os intevalos de horas que
    --interseccionam com o período definido por @HoraEntrada e @HoraSaida
    INSERT @PeriodoUtilTrab
        SELECT InicioInterseccao 
                    = CASE WHEN @HoraEntrada < InicioPeriodoUtil 
                           THEN InicioPeriodoUtil 
                           ELSE @HoraEntrada END
              ,FimInterseccao 
                    = CASE WHEN @HoraSaida > FimPeriodoUtil 
                           THEN FimPeriodoUtil 
                           ELSE @HoraSaida END
          FROM @JanelaHoraUtil
         WHERE @HoraEntrada <= FimPeriodoUtil
           AND @HoraSaida >= InicioPeriodoUtil

    --Totalizar (em segundos) o tempo trabalhado de acordo com os períodos efetivamente trabalhados 
    SELECT @TempoUtilTrab = @TempoUtilTrab 
                 + DATEDIFF(second,InicioPeriodoUtilTrab,FimPeriodoUtilTrab)
      FROM @PeriodoUtilTrab

    --Reduzir as horas uteis trabalhadas no dia do contador de horas
    SET @TempoCont = @TempoCont - @TempoUtilTrab

    --Se ainda houver hora no contador, passar para o próximo dia
    IF @TempoCont > 0
    BEGIN
        SET @DiaCont = DATEADD(day,1,@DiaCont)
        SET @HoraEntrada = @InicioExpediente
        SET @HoraSaida = DATEADD(second,@TempoCont,@HoraEntrada)
    END
END

--A hora fim corresponde ao fim do último período util trabalhado
SELECT @HoraFim = MAX(FimPeriodoUtilTrab)
  FROM @PeriodoUtilTrab

--Somar a DiaCont com a HoraFim
SELECT Tarvencimento = DATEADD(second,DATEDIFF(second,0,@HoraFim),@DiaCont)