Everyone, I have a question. In the query below, in the field titling, he counts the hours normally, however, I want you to count only the working hours.
NOTE: You already have a working hours function called working_hours NOTE: This variable @Hora is picking up the times from another table.
Ex: Let's assume that @Hora is 6 Hours, so today the result is coming out that way. DataWalkingTrendsystem 2017-11-06 14:00 00:00 2017-11-06 20:00 00:00
Note that it is adding two more hours after 18:00, but the useful time that is in the function is from 08:00 to 18:00, and I want it to add only the useful hours that in this case it was to come out that way. DataWalkingTrendsystem 2017-11-06 14:00 00:00 2017-11-07 10:00:00 AM
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
WORKING HOURS FUNCTION
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