E agora DBA?: agosto 2011

/*#######################################################################
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)
      )

--- 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

/* ## 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!