SQL Server Function - Holidays

Question

Good afternoon!

Dear friends, I have the following function below that is the days not worked that are coming only Saturday and Sunday, however, I wanted to include in this function the holidays. NOTE: I already have a function of holidays, however, I wanted to include this function of holidays within the function of days not worked. Below follows 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

Answer 1

Example using the existing Function.

ALTER FUNCTION [dbo].[FN_DIA_NAO_TRAB] ( @data SMALLDATETIME ) RETURNS BIT AS BEGIN

    DECLARE @diaNaoTrab TINYINT DECLARE @flgDataNaoUtil BIT
    SET @diaNaoTrab= 0 

    IF DATENAME(dw, @data) IN ('Domingo', 'Sábado') 
    BEGIN 
        SET @diaNaoTrab = 1 
    END

    IF EXISTS( SELECT NULL FROM getFeriados(year(@data)) WHERE dia=@data )
    BEGIN 
        SET @diaNaoTrab = 1 
    END


    RETURN @diaNaoTrab

END

NOTE: It is not a good practice to write methods or functions with a negative in the name, because it makes a little more confusing your understanding.

Answer 2

Your holiday function is not very cool for a number of reasons, but I will focus on your primary function. First I will change the name by removing the nao that can cause confusion.

ALTER FUNCTION dbo.fn_dia_trabalhado(@data DATE)
RETURNS BIT
AS
BEGIN
  DECLARE @resultado BIT;

  IF DATEPART(WEEKDAY, @data) IN (7, 1) OR EXISTS(SELECT 1
                                                    FROM getferiados(DATEPART(YEAR, @data)) f
                                                   WHERE CAST(f.dia AS DATE) = @data)
  BEGIN
    SET @resultado = 0;
  END
  ELSE
  BEGIN
    SET @resultado = 1;
  END;

  RETURN @resultado;
END;
GO

After all the changes we would have the function above that will return 1 if the day is worked and 0 if it is not.