Pular para o conteúdo principal

Como calcular dias úteis com o SQL Server

No post anterior foi demonstrado como fazer o cálculo dos dias úteis entre duas datas usando a linguagem C#.

Este cálculo pode ser muito útil principalmente para rotinas comerciais e relatórios. Para não deixar limitado à linguagem C# este post demonstra como fazer o cálculo usando o banco de dados Microsoft SQL Server (MSSQL).

No exemplo a solução apresentada lança mão das funções de usuário do MSSQL. No código será demonstrado como criar uma destas funções e seus principais aspectos. A demonstração usa o banco de dados AdventureWorks.

As funções de usuário do SQL Server

O MSSQL possui um número muito grande de funções nativas (built in functions) que resolve vários problemas. Para saber quais estão disponíveis basta verificar na ferramenta SQL Server Management Studio (SSMS) o item Programmability > Functions na janela Object Explorer conforme demonstrada na figura abaixo.

image

Existem as funções específicas de cada banco e as que são nativas do MSSQL. Mesmo assim pode surgir a necessidade de se criar uma que execute alguma operação muito específica, como neste caso, o cálculo de dias úteis entre duas datas.

As funções que são criadas pelo usuário no MSSQL podem ser do tipo scalar que consiste de um valor de um tipo simples (numérico, data ou string, sempre usando os tipos suportados do MSSQL) ou ainda table-valued o que significa que o retorno será um conjunto de colunas e linhas de dados que compõem uma tabela.

Existem vários aspectos e particularidades para a utilização deste recurso sendo todos estes descritos na documentação oficial do MSSQL.

A criação de funções possui parâmetros e uma estrutura que deve ser seguida Para uma função que tenha o retorno scalar a sintaxe básica deve ter os seguintes elementos:

CREATE FUNCTION [ nome do schema. ]nome da função
( [@nome do parâmetro 1 [ AS ] tipo do parâmetro no MSSQL ], ...
[@nome do parâmetro n]
)
RETURNS tipo MSSQL do retorno
AS
BEGIN
<corpo>
RETURN <valor>
END;

CREATE FUNCTION deve ser a primeira instrução. A função não deve existir no banco o que significa que a mesma deve ser removida previamente caso já exista. O nome do schema (como dbo, por exemplo) é opcional, porém, para um correto gerenciamento é muito importante definir de qual schema a função faz parte. Toda função deve ter um nome que deve seguir (conforme a documentação oficial) as regras dos identificadores.


É obrigatório o uso dos parênteses após o nome da função, mesmo que não hajam parâmetros que se forem usados devem ter obrigatoriamente o símbolo @ como prefixo.


RETURNS é usado para indicar o tipo do retorno da função. As instruções TRANSACT SQL devem estar todas contidas dentro do bloco BEGIN ... END sendo que a última instrução deste deve ser RETURN com o valor conforme definido na declaração da função.


Uma função que retorna uma tabela tem sua estrutura um pouco diferente:

CREATE FUNCTION [ nome do schema. ] nome da função 
( [@nome do parâmetro 1 [ AS ] tipo do parâmetro no MSSQL ], ...
[@nome do parâmetro n]
)
RETURNS TABLE
[ WITH [ ,...n ] ]
[ AS ]
RETURN [ ( ] select_stmt [ ) ]

Enquanto no primeiro tipo de função o valor deve ser representado por um tipo simples, neste segundo deve ser resultado de uma consulta. Observe que o tipo de retorno é declarado como TABLE em RETURNS TABLE.


Exemplo prático


Os passos para resolver a questão de retornar o número de dias úteis entre duas datas são os seguintes:



  1. Definir a data de início e data final.
  2. Contar os dias entre as datas excluindo sábados e domingos.
  3. Retornar o valor encontrado.

Não serão considerados os feriados pois além de aumentar a complexidade são variáveis por região, estado e até mesmo cidade. O ideal, em um banco de dados, é manter-se um cadastro destes. Fica aqui a dica.


A função


O código SQL deve ser parecido com o que segue:

CREATE FUNCTION [dbo].[WORKDAYS] (@StartDate DateTime, @EndDate DateTime) returns int
as
begin
declare @days as int = 0;

if(@StartDate > @EndDate)
return null;

while(@StartDate <= @EndDate)
begin
if(DATEPART(WeekDay, @StartDate) between 2 and 6)
begin
set @days = @days+1;
end;

set @StartDate = DateAdd(Day, 1, @StartDate);
end

return @days;
end;
go

A função inicia recebendo os parâmetros @StartDate e @EndDate para armazenar as datas de início e fim respectivamente.


O retorno é sempre do tipo inteiro.


Para fazer a contagem dos dias foi definida a variável @days que será incrementada dentro de um laço.


É feito um teste com as variáveis para saber se não foram passadas na ordem errada com o if, se isto ocorrer a função retorna um valor nulo.


O laço while faz o teste usando a função nativa DATEPART para testar através da constante WeekDay qual é o dia da semana encontrado. No SQL Server e neste código os dias úteis são de segunda a sexta e estão numerados de 2 até 6. Mais informações sobre a função DATEPART pode ser conferida aqui se desejar mais informações.


A estrutura e relacionamentos das tabelas


Após definida a função basta executar com o banco de dados correto selecionado e esta será criada.


No banco AdventureWorks2012 esta função será executada para obter os dias úteis trabalhados pelos empregados desde sua data de contratação. Os dados estão localizados nas tabelas Person (que armazena dados gerais de pessoas) e Employee.


Adventure_works_diagram


O relacionamento entre as duas tabelas é feito através do campo BusinessEntityID usado também como chave primária.


Definindo a consulta e obtendo o resultado


A consulta ficou da seguinte forma:

SELECT p.FirstName + ' ' + ISNULL(p.MiddleName, '') + ' ' + ISNULL(p.LastName, '') as FullName
,CONVERT(VARCHAR(10), he.HireDate, 103) HireDate
,dbo.WORKDAYS(he.HireDate, GetDate()) as WorkDaysTotal
from Person.Person as p
join HumanResources.Employee he on he.BusinessEntityID=p.BusinessEntityID
ORDER BY 3 DESC

A função recém criada - WORKDAYS - foi usada dentro das colunas passadas na cláusula SELECT. As datas usadas forma a data de admissão HireDate e a data atual que pode ser obtida com a função GetDate().


O resultado desta consulta deve ser parecido com o da figura:

Workdays_results

Conclusão


Com este post alguns dos elementos básicos para a criação de funções de usuário podem ser conhecidos. Certamente muito mais pode ser feito dependendo das necessidades e conhecendo todos os aspectos deste recurso na documentação do SQL Server. Até a próxima.

Postagens mais visitadas deste blog

Como gerar scripts para exportar dados no SQL Server 2008

Uma das tarefas mais comuns no trabalho com desenvolvimento de software que consome dados em bancos como o SQL Server 2008 é a necessidade de em algum momento precisarmos exportar os dados de um banco para outro. Quer seja para realizar testes ou fazer simulações existem várias maneiras de se fazer isto. Neste post eu quero demonstrar um recurso do SQL Server Management Studio (SSMS) que permite realizar esta tarefa rapidamente.Para os que estão acostumados a usar esta ferramenta, já devem saber que é possível gerar scripts para o schema e também transferir os dados entre dois bancos distintos. Isto pode ser feito se o SSMS puder conectar-se com as duas bases, de origem e destino. No exemplo que vou dar, o objetivo é gerar o script apenas para uma tabela do banco de dados de exemplo da Microsoft – Northwind.1. Iniciando o assistenteO assistente deve ser iniciado clicando com o botão direito do mouse sobre o banco onde se encontra a tabela a qual iremos gerar o script. Deve se clicar n…

Pivot dinâmico com SQL Server

Passo a passo para usar pivoteamento dinâmicoOs bancos de dados bem configurados e definidos armazenam os dados de forma a otimizar o acesso, evitando duplicidade e garantindo a integridade. Porém, em muitas situações isto pode dificultar a apresentação de forma adequada sendo necessário preparar os dados usando vários recursos entre os quais, fazer o pivoteamento.Se você não precisou ainda usar ou não sabe o que é consiste em transformar cada linha de uma determinada coluna em colunas de uma nova consulta.Assim, considere uma tabela que armazene as notas bimestrais de um boletim. Uma possível estrutura para esta tabela seria algo assim:ColunaTipo de dado/TamanhoDisciplinanvarchar(50)BimestreIntNotanumeric(5,2)Uma consulta select nesta tabela com alguns dados traria um resultado parecido com o abaixo:Porém pode ser que para apresentar estes dados em um relatório seja necessário transformar cada bimestre em uma coluna e agrupar as notas nestas colunas para que fique dessa forma:Isto po…

Desabilitando o auto commit no SSMS (SQL Server Management Studio)

(Ou, como prevenir desastres e manter o emprego a salvo…)Neste post vai uma pequena mas tremendamente útil dica para desabilitar o auto commit da aplicação SQL Server Management Studio (SSMS) que é usada por dez entre dez usuários do banco de dados SQL Server para fazer consultas, alterações e executar scripts no banco de dados. (Preferências à parte, realmente muita gente usa),A primeira e mais importante notícia é que, diferentemente da ferramenta do Oracle, este editor de scripts do SQL Server vem com o recurso de auto commit ativado por padrão, assim, qualquer instrução DML (alteração dos dados com update, insert e delete) ou DDL (alteração no banco como create, drop, alter, etc.) será imediatamente enviada ao banco e persistida.Isto pode ser altamente crítico pois se estiver executando as instruções em um banco de dados de produção não haverá muitas formas de desfazer se é que haverá.Inicialmente, pode se evitar muitos acidades executando estas instruções dentro de um bloco BEGIN…