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.
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:
- Definir a data de início e data final.
- Contar os dias entre as datas excluindo sábados e domingos.
- 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.
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:
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.