Good afternoon!
Dear, I have the following function below that is of the days not worked that is only coming Saturday and Sunday, however, I wanted this function to include the holidays. NOTE: I already have a holidays function, however, I wanted to include this holidays function into the function of days not worked. Below is the two functions.
-> FUNÇÃO DIA NÃO TRABALHADO
USE [0800net_PRD]
GO
/****** Object: UserDefinedFunction [dbo].[FN_DIA_NAO_TRAB] Script Date: 12/09/2017 17:19:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Função para verificar se a data informada é um dia útil
SELECT dbo.FN_DIA_NAO_TRAB ('2016-01-01')
*/
ALTER FUNCTION [dbo].[FN_DIA_NAO_TRAB]
(
@data SMALLDATETIME
)
RETURNS BIT
AS
BEGIN
DECLARE @num TINYINT
DECLARE @flgDataNaoUtil BIT
IF DATENAME(dw, @data) IN ('Domingo', 'Sábado')
BEGIN
SET @num = 1
END
IF @num > 0
BEGIN
SET @flgDataNaoUtil = 1
END
ELSE
BEGIN
SET @flgDataNaoUtil = 0
END
RETURN @flgDataNaoUtil
END
-> FUNÇÃO FERIADOS
USE [0800net_PRD]
GO
/****** Object: UserDefinedFunction [dbo].[getFeriados] Script Date: 12/09/2017 17:24:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[getFeriados](@ano int = null)
RETURNS @feriado TABLE (dia DATETIME, feriado varchar(100))
AS
BEGIN
DECLARE @pascoa SMALLDATETIME,
@dia int ,@mes int , @anoPascoa int ;
IF(@ano IS NULL) BEGIN
set @ano = DATEPART(year, GETDATE());
END
SET @pascoa = dbo.getDataPascoa(@ano);
SET @dia = DATEPART(DAY, @pascoa);
SET @mes = DATEPART(MONTH, @pascoa);
SET @anoPascoa = DATEPART(YEAR, @pascoa);
INSERT INTO @feriado (dia, feriado) VALUES(@pascoa, 'Pascoa');
INSERT INTO @feriado (dia, feriado) VALUES(CAST('1-1-' + CAST(@anoPascoa AS VARCHAR) + ' 00:00:00' AS DATETIME), 'Confraternização Universal');
INSERT INTO @feriado (dia, feriado) VALUES(CAST(CAST(@anoPascoa AS VARCHAR) + '-4-21' AS DATETIME), 'Tiradentes');
INSERT INTO @feriado (dia, feriado) VALUES(CAST(CAST(@anoPascoa AS VARCHAR) + '-5-1' AS DATETIME), 'Dia do Trabalhador');
INSERT INTO @feriado (dia, feriado) VALUES(CAST(CAST(@anoPascoa AS VARCHAR) + '-7-9' AS DATETIME), 'Dia da Independência');
INSERT INTO @feriado (dia, feriado) VALUES(CAST(CAST(@anoPascoa AS VARCHAR) + '-10-12' AS DATETIME), 'N. S. Aparecida');
INSERT INTO @feriado (dia, feriado) VALUES(CAST(CAST(@anoPascoa AS VARCHAR) + '-11-2' AS DATETIME), 'Todos os santos');
INSERT INTO @feriado (dia, feriado) VALUES(CAST(CAST(@anoPascoa AS VARCHAR) + '-11-15' AS DATETIME), 'Proclamação da republica');
INSERT INTO @feriado (dia, feriado) VALUES(CAST(CAST(@anoPascoa AS VARCHAR) + '-12-25' AS DATETIME), 'Natal');
INSERT INTO @feriado (dia, feriado) VALUES(DATEADD(DAY, 60, @pascoa), 'Corpus Christi');
INSERT INTO @feriado (dia, feriado) VALUES(DATEADD(DAY, -2, @pascoa), '6º feira Santa');
INSERT INTO @feriado (dia, feriado) VALUES(DATEADD(DAY, -47, @pascoa), '3º feria Carnaval');
INSERT INTO @feriado (dia, feriado) VALUES(DATEADD(DAY, -48, @pascoa), '2º feria Carnaval');
RETURN;
END