Modelagem - Função para calcular dias úteis em T-SQL

/*#######################################################################
ATENÇÃO: ESTE POST TODO É UM SCRIPT! SE VOCE NÃO TIVER PACIENCIA PARA LER, BASTA COPIA-LO EM SEU
MANAGEMENT STUDIO E EXECUTA-LO NA SUA BASE DE PREFERENCIAS
VOCE TERÁ UMA TABELA DATAS E A FUNÇÃO FN_DIASUTEIS PRONTA PRA USO!
#######################################################################*/

/* Olá caro leitor!

   Esses dias no fórum da MSDN um colega perguntou se era possivel criar uma função que calcula-se quantos dias uteis tivemos entre uma data e outra. Não existe nenhuma função nativa no SQL que faça esse calculo, no entanto com alguns calculos e uma tabela do tamanho de um MP3 (3Mb) conseguimos montar essa função cobrindo um período de dois séculos !

   Esse tipo de tabela também é bem comum entre o pessoal que trabalha com Data Warehouse.

  Então mãos a obra!

*/ 

/* ## Passo 1 - CONSTRUINDO E POPULANDO A TABELA ------------------------

      Nesse passo, montamos uma estrutura bem simples, que armazenará todas os dias de 1900 a 2099, incluindo alguns atributos que podem ser interessantes.

      Na sequencia com um WHILE para popularmos os dados.     

      Obs: Antes que voce questione. Existe uma razão para o campo DiaUtil ser Default 1 e tipo tinyint (explicações adiante)
 
*/

-- Tabela

CREATE TABLE Datas (
      Data datetime NOT NULL CONSTRAINT PK_Datas PRIMARY KEY,
      DiaSemana varchar(13) NOT NULL,
      DiaSemanaNum tinyint NOT NULL,
      Dia tinyint  NOT NULL,
      Mes tinyint NOT NULL,
      NomeMes varchar(9) NOT NULL,
      Ano smallint NOT NULL,
      DiaUtil tinyint NOT NULL DEFAULT (1)
      )

GO

--- Preenchimento da tabela

DECLARE @DataInicial DATETIME = '01/01/1900'
DECLARE @DataFinal DATETIME = '31/12/2099'
 
WHILE @DataInicial <= @DataFinal
      BEGIN
            INSERT INTO Datas
            SELECT
                  @DataInicial,
                  DATENAME(WEEKDAY,@DataInicial),
                  DATEPART(WEEKDAY,@DataInicial),
                  DATEPART(DAY,@DataInicial),
                  MONTH(@DataInicial), 
                  DATENAME(MONTH,@DataInicial),
                  YEAR(@DataInicial),
                  1

            SET @DataInicial = DATEADD(DAY,1,@DataInicial)
      END
      GO

/*
      Notou como a tabela ficou pequena???
    
      Execute um space used e você ficará impressionado.

*/

--- Espaço ocupado

      sp_spaceused 'Datas'
      GO

/*
rows        reserved        data               index_size         unused
----------- --------------- ------------------ ------------------ -------
73049       3344 KB         3312 KB            24 KB              8 KB
*/

/* ## Passo 2 - Marcando os dias inuteis (na visão de seu chefe)

      OK. Dados prontos, só precisamos marcar os tres tipos de dias não úteis que possuimos

      1 - Finais de Semana

      2 - Feriados Nacionais Fixos (dependendo do seu cenário, fique a vontade para incluir os feriados estaduais e municipais)

      3 - Feriados Nacionais variáveis (basicamente o Carnaval (EBA!) e o Corpus Christi (???). Pois ambos seguem o Calculo Eclesiástico quem em 325 o Concilio de Nicea assim os definiu (SQL Server também é cultura!!!)

*/


/*  Fins de Semana: Basta zerar a coluna DiaUtil onde o dia da semana numerico for igual a 7 - Sabádo e 1 – Domingo */

      UPDATE Datas SET DiaUtil = 0 WHERE DiaSemanaNum IN (7,1)


/*  Feriados Nacionais Fixos: Para cada combinação de dia e mês (o ano não importa) um update nos feriados.
  Inclua aqui dias 24/12 ou 31/12 conforme seu cenário exigir */

    

      UPDATE Datas
      SET DiaUtil = 0
      WHERE
            (Dia = 1 AND Mes = 1)   -- Dia da Confraternização Mundial
      OR
            (Dia = 21 AND Mes = 4) -- Dia de Tiradentes
      OR
            (Dia = 1 AND Mes = 5)   -- Dia do Trabalho
      OR
            (Dia = 7 AND Mes = 9)   -- Independencia do Brasil
      OR
            (Dia = 12 AND Mes = 10)   -- Dia de Nossa Senhora Aparecida
      OR
            (Dia = 2 AND Mes = 11)   -- Finados
      OR
            (Dia = 15 AND Mes = 11)   -- Proclamação da Republica
      OR
            (Dia = 25 AND Mes = 12)   -- Natal


/* ## Passo 3 - Feriados Nacionais variaveis (Carnaval e Corpus Christi).

      Aqui a coisa complica um pouco, pois temos que converter em T-SQL as fórmulas que o Concilio de Nicea decidiu a uns 1700 anos atrás (será que o
Papa da época imaginou isso um dia?)


      Minha referência veio do site http://inf.ufrgs.br/~cabral/Pascoa.html. Do professor da UFRGS - Profº Roberto Cabral de Mello Borges.

      Lá ele explica o calculo mais detalhadamente e dá exemplos. Além de uma tabela completa com datas ja calculadas.

      Basicamente aqui, farei um outro Loop com WHILE passeando por cada ano e usando a formula para identificar o Domingo de Pascoa. Através dele chegaremos na Terça de Carnaval (Domingo de Pascoa menos 47 dias) e no Corpus Christi (Domingo de Pascoa + 60 dias)
*/

-- Constantes X e Y e variaveis de calculo
DECLARE @X int = 24
DECLARE @Y int = 5
DECLARE @a int ,@b int, @c int,@d int,@e int

-- Variaveis de data
DECLARE @Ano smallint = 1900
DECLARE @Dia tinyint
DECLARE @Mes tinyint
DECLARE @DomingoPascoa datetime


WHILE @Ano <= 2099
      BEGIN
      --- Calculo de Dia e Mes
            SET @a = @Ano % 19
            SET @b = @Ano % 4
            SET @c = @Ano % 7
            SET @d = (19 * @a + @X) % 30
            SET @e = (2 * @b + 4 * @c + 6 * @d + @Y) % 7

            IF (@d + @e) > 9
                  BEGIN
                        SET @Dia = @d + @e - 9
                        SET @Mes = 4
                  END
            else
                  BEGIN
                        SET @Dia = @d + @e + 22
                        SET @Mes = 3
                  END
              

/* Correção dos casos particulares que ocorrem duas vezes por século (2049 e 2076 no século 21) */

            IF @Ano IN (2049,2076)
                  BEGIN
                        IF @Dia = 26
                             SET @Dia = 19
                        else
                             SET @Dia = 18                     
                  END  

            -- Obtém o domingo de pascoa            
            SET @DomingoPascoa = CAST(CAST(@Dia as varchar) + '/' + CAST(@Mes as varchar) + '/' + CAST(@Ano as varchar) as datetime)

            -- Marca a terça feira de carnaval (pascoa - 47 dias)
            UPDATE Datas
            SET DiaUtil = 0
            WHERE  Data = DATEADD(day,-47,@DomingoPascoa)

            -- Marca a quinta feira Corpus Christi (pascoa + 60 dias)
            UPDATE Datas
            SET DiaUtil = 0
            WHERE  Data = DATEADD(day,60,@DomingoPascoa)


            SET @Ano = @Ano + 1
      END

GO



/* ## Passo 4 - Enfim a função!!!

      Eis a razão de ter escolhido TinyInt no campo DiaUtil. Se tivesse escolhido o campo Bit, não conseguiria aplicar o SUM. 
      O campo data como PK também garante uma performance excepcional no calculo.

*/

      CREATE FUNCTION dbo.fn_DiasUteis (@DataInicial datetime,
                                        @DataFinal datetime)
      RETURNS int
      AS

            BEGIN

                  DECLARE @DiasUteis int

                  SELECT
                             @DiasUteis = SUM(DiaUtil)
                  FROM
                        Datas
                  WHERE
                        Data BETWEEN      @DataInicial AND @DataFinal


                  RETURN @DiasUteis
            END  
GO


/* ## E Pronto!

      Essa funçãozinha ficará eternamente ativa, não exigirá manutenção nos próximos 88 anos. O que é tempo suficiente para voce relaxar e descobrir e refletir sobre quantos dias uteis ocorreram desde o dia que nasceu!
   
*/
 

SELECT dbo.fn_DiasUteis ('21/04/1982','18/08/2011') 
 

/*
      Gostou da dica? Achou algum bug? Tem alguma pergunta?? Fique a vontade para me contactar e comentar abaixo!

      email: fjantunes@gmail.com


*/