Crie um site como este com o WordPress.com
Comece agora

Operações com dias úteis no SQL Server

pexels-photo-760710.jpeg
Foto por Bich Tran (Pexels)

Nos últimos dias presenciei alguns casos de dúvidas relacionadas ao cálculo de dias úteis. Neste artigo destaco situações de primeiro e último dia útil do mês, próximo dia útil, dia útil anterior, quantidade de dias úteis entre duas datas etc. etc. Inclusive como criar tabelas de feriados e de calendário.

1. Introdução

No início de 2020 publiquei extenso guia em que o assunto data & hora foi tratado com profundidade, com mais de 60 páginas no tamanho A4; um e-book sobre o assunto. Mas como o assunto “dias úteis” sempre retorna, resolvi então escrever este artigo utilizando partes selecionadas do artigo “Dominando datas e horas no SQL Server” e de outros artigos anteriormente publicados no Porto SQL.

Antes de prosseguir, uma observação. Desenvolver códigos T-SQL exige tempo e dedicação para estudos e testes, afora conhecimento técnico. Sempre que utilizo código T-SQL de outra pessoa como base eu informo o nome do autor e/ou o endereço web da página onde foi publicado, de modo a respeitar o esforço de programação de quem o desenvolveu. Do contrário tem-se o plágio, que além de ser uma violação dos direitos autorais é também uma demonstração de falta de ética profissional. Nesse ponto faço minhas as palavras de Edvaldo Castro e de Erickson Ricci presentes no artigo “PLÁGIO – Sério mesmo?”.

2. Criação das tabelas FERIADO e CALENDÁRIO

Para trabalhar com dias úteis além de saber quando é sábado e domingo também é necessário dispor de tabela de feriados. E aqui não somente os feriados nacionais, mas também os estaduais e, dependendo do contexto do banco de dados, os municipais. Outra questão é se o sábado será considerado dia útil ou não. Assim, o primeiro passo é construir a tabela de feriados e, opcionalmente, a tabela com o calendário contendo informação sobre todos os dias.

2.1. Tabela FERIADO

Os feriados oficiais são definidos por leis, podendo ser nacionais, estaduais ou municipais. A Lei 9.093/1995, junto com a Lei 10.607/2002, dispõe sobre feriados nacionais, citando que os feriados podem ser de origem civil ou religiosa.

A estrutura da tabela de feriados depende do contexto do banco de dados. Por exemplo, ele pode ser de uma empresa que está instalada em um único município e que somente é necessário conhecer quando é feriado no município, além dos feriados federais e estaduais e conforme as regras da atividade principal da empresa. Nesse caso podemos ter algo como

-- código #2.1
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

CREATE TABLE dbo.FERIADO (
     Data date not null,
     Abrangencia char(1) not null 
        check (Abrangencia in ('M', 'E', 'N')),
     constraint PK_FERIADO primary key (Data)
);

Para cada dia que seja feriado somente pode existir uma única linha, com a indicação da abrangência do feriado: municipal (M), estadual (E) ou nacional (N). Se em uma mesma data há mais de um feriado, com abrangências diferentes, deve-se cadastrar o de mais alta abrangência.

É uma tabela simples mas que contém pouca informação. A primeira informação que falta é sobre o motivo do feriado. Outra informação que pode ser útil é se é um feriado religioso ou oficial, além de registrar se é um feriado fixo ou móvel.

Podemos então ampliar a tabela de feriados para a seguinte estrutura:

-- código #2.2
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

CREATE TABLE dbo.FERIADO (
     Data date not null,
     Abrangencia char(1) not null 
         check (Abrangencia in ('M', 'E', 'N')), 
     Origem char(1) not null 
         check (Origem in ('R','C')), -- religioso ou civil
     Tipo char(1) not null 
         check (Tipo in ('F', 'M')), -- fixo ou móvel
     Descricao varchar(80),
     constraint PK_FERIADO primary key (Data, Abrangencia)
);

Pelo fato de ter sido acrescentada a descrição do feriado, a chave primária da tabela passou a ser composta, pois em uma mesma data podem existir um feriado nacional e um municipal, por exemplo, o que significa que existirão duas linhas com a mesma data na tabela.

Observe que, para fins didáticos, a tabela possui uma estrutura simples. Entretanto, dependendo da atividade da empresa, se ela possui instalações em localidades diversas e em diferentes estados, o registro de feriados é mais complexo podendo inclusive envolver mais de uma tabela.

Para fins de desenvolvimento deste artigo o código T-SQL da carga inicial da tabela com os feriados nacionais de 2020 é o seguinte:

-- código #2.3
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

set dateformat dmy;

-- feriados nacionais de 2020
INSERT into dbo.FERIADO 
            (Data, Abrangencia, Origem, Tipo, Descricao)
  values 
    ('1/1/2020', 'N', 'C', 'F', 'Confraternização universal'),
    ('10/4/2020', 'N', 'R', 'M', 'Paixão de Cristo'),
    ('21/4/2020', 'N', 'C', 'F', 'Tiradentes'),
    ('1/5/2020', 'N', 'C', 'F', 'Dia mundial do Trabalho'),
    ('7/9/2020', 'N', 'C', 'F', 'Independência do Brasil'),
    ('12/10/2020', 'N', 'R', 'F', 'Nossa Senhora Aparecida'),
    ('2/11/2020', 'N', 'C', 'F', 'Finados'),
    ('15/11/2020', 'N', 'C', 'F', 'Proclamação da República'),
    ('25/12/2020', 'N', 'R', 'F', 'Natal');

Dos feriados relacionados acima somente um é móvel, Paixão de Cristo (ou sexta-feira santa), sendo que ele ocorre sempre na sexta-feira que antecede o domingo de Páscoa. Esta, a Páscoa, é uma comemoração religiosa em data móvel, sendo definida como o “primeiro domingo depois da lua cheia após o início do equinócio vernal. Do ponto de vista eclesiástico, o equinócio vernal acontece em 21 de março (embora ocorra no dia 20 de março na maioria dos anos do ponto de vista astronômico) e a lua cheia não ocorre necessariamente na data correta astronômica. Por isso, a data da Páscoa varia entre 22 de março e 25 de abril (inclusive)”, conforme trecho do verbete Páscoa da wikipedia. Ou seja, primeiro temos que encontrar a data da Páscoa no ano para somente após obtermos a data do feriado religioso “Paixão de Cristo”. Complicado…

Para facilitar o trabalho há alguns métodos que envolvem ou o uso de tabela auxiliar ou então de algoritmos, sendo que neste artigo será utilizado o algoritmo de Meeus/Jones/Butcher:

a = ANO mod 19
b = ANO div 100
c = ANO mod 100
d = b div 4
e = b mod 4
f = (b + 8) div 25
g = (b - f + 1) div 3
h = (19 × a + b - d - g + 15) mod 30
i = c div 4
k = c mod 4
L = (32 + 2 × e + 2 × i - h - k) mod 7
m = (a + 11 × h + 22 × L) div 451

MÊS = (h + L - 7 × m + 114) div 31
DIA = 1+ (h + L - 7 × m + 114) mod 31

onde div é o operador para obter a divisão inteira e mod é o operador para obter o resto de divisão inteira. Por exemplo, 

7 div 3 = 2
7 mod 3 = 1

Para carregar tabela de feriados por 30 anos, e utilizando o algoritmo Meeus/Jones/Butcher, temos

-- código #2.4
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

set dateformat dmy;
set nocount on;

-- feriados fixos de 2020
INSERT into dbo.FERIADO
(Data, Abrangencia, Origem, Tipo, Descricao)
values
('1/1/2020', 'N', 'C', 'F', 'Confraternização universal'),
('21/4/2020', 'N', 'C', 'F', 'Tiradentes'),
('1/5/2020', 'N', 'C', 'F', 'Dia mundial do Trabalho'),
('7/9/2020', 'N', 'C', 'F', 'Independência do Brasil'),
('12/10/2020', 'N', 'R', 'F', 'Nossa Senhora Aparecida'),
('2/11/2020', 'N', 'C', 'F', 'Finados'),
('15/11/2020', 'N', 'C', 'F', 'Proclamação da República'),
('25/12/2020', 'N', 'R', 'F', 'Natal');

-- complementa feriados fixos por 30 anos
with
GetNums (n) as (
SELECT row_number() over (order by (SELECT NULL))
from (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as A(n)
cross join (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as B(n)
)
INSERT into dbo.FERIADO
(Data, Abrangencia, Origem, Tipo, Descricao)
SELECT dateadd (year, +N.n, F.Data),
F.Abrangencia, F.Origem, F.Tipo, F.Descricao
from dbo.FERIADO as F
cross join GetNums (29) as N
where N.n <= 29;

-- acrescenta os feriados móveis
declare @a integer, @b integer, @c integer, @d integer,
@e integer, @f integer, @g integer, @h integer,
@i integer, @k integer, @L integer, @m integer,
@Ano integer, @Mes integer, @Dia integer;

set @Ano= 2020;
while @Ano < 2050
begin
set @a = @Ano % 19;
set @b = @Ano / 100;
set @c = @Ano % 100;
set @d = @b / 4;
set @e = @b % 4;
set @f = (@b + 8) / 25;
set @g = (@b - @f + 1) / 3;
set @h = (19 * @a + @b - @d - @g + 15) % 30;
set @i = @c / 4;
set @k = @c % 4;
set @L = (32 + 2 * @e + 2 * @i - @h - @k) % 7;
set @m = (@a + 11 * @h + 22 * @L) / 451;

set @Mes = (@h + @L - 7 * @m + 114) / 31;
set @Dia = 1+ (@h + @L - 7 * @m + 114) % 31;

--
INSERT into dbo.FERIADO
(Data, Abrangencia, Origem, Tipo, Descricao)
SELECT dateadd (day, -2, datefromparts (@Ano, @Mes, @Dia)),
'N', 'R', 'M', 'Paixão de Cristo';

-- próximo ano
set @Ano+= 1;
end;

O código sql para a inclusão do feriado da “Paixão de Cristo” é bem simples, processando linha a linha. Optei por programá-lo assim pois poucas linhas são geradas e facilita a compreensão e manutenção.

Para cada ano a descrição de cada feriado se repete, o que pode ser eliminado ao criar uma tabela adicional somente com as descrições, reduzindo assim o espaço físico ocupado pela tabela FERIADO. Entretanto, como é uma tabela pequena, me parece que não compensa separar o conteúdo da tabela FERIADO em duas tabelas.

2.2. Tabela CALENDÁRIO

A tabela com o calendário desenvolvida neste artigo também considera somente uma localidade, de modo a facilitar a demonstração das soluções. O sábado não foi considerado como dia útil.

-- código #2.5
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

CREATE TABLE dbo.CALENDARIO (
     Data date not null,
     Dia as cast (datepart (day, Data) as tinyint) persisted,
     Mes as cast (datepart (month, Data) as tinyint) persisted,
     Ano as cast (datepart (year, Data) as smallint) persisted,
     DiaSemana varchar(20) not null,
     SemanaAno tinyint not null,
     DiaUtil bit not null,
     Feriado bit not null,
     constraint PK_CALENDARIO primary key (Ano, Mes, Dia)
);
CREATE unique nonclustered INDEX I2_CALEND_Data 
     on dbo.CALENDARIO (Data);

A carga da tabela CALENDARIO por 30 anos, a partir de 2020, pode ser feita utilizando o seguinte código T-SQL:

-- código #2.6
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

declare @DataInicial date, @DataFinal date;
set @DataInicial= convert (date, '1/1/2020', 103);
set @DataFinal= convert (date, '31/12/2049', 103);

--
declare @Dias integer;
set @Dias= datediff (day, @DataInicial, @DataFinal) +1;

set datefirst 7; -- semana começa no domingo
set language brazilian; -- para datename retornar em português
set nocount on;

with
GetNums (n) as (
SELECT row_number() over (order by (SELECT NULL))
from (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as A(n)
cross join (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as B(n)
cross join (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as C(n)
cross join (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as D(n)
cross join (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as E(n)
), Periodo as ( SELECT dateadd (day, (T.n -1), @DataInicial) as Dia from GetNums as T where T.n <= @Dias ) INSERT into dbo.CALENDARIO (Data, DiaSemana, SemanaAno, DiaUtil, Feriado) SELECT P.Dia, datename (weekday, P.Dia), datepart (week, P.Dia), case when datepart (weekday, P.Dia) in (1, 7) or exists (SELECT * from dbo.FERIADO as F where F.Data = P.Dia) then 0 else 1 end, case when exists (SELECT * from dbo.FERIADO as F where F.Data = P.Dia) then 1 else 0 end from Periodo as P;

Para gerar as datas foi utilizada tally table, com capacidade de até 100.000 dias. Se for necessário gerar período maior, basta alterar a tally table.

3. Seleção de casos frequentes

No capítulo anterior foi tratado de como criar as tabelas de feriados e do calendário e agora vamos utilizá-las na resolução de vários casos.

3.1. Primeiro dia útil do mês

O algoritmo para obter o primeiro dia útil do mês depende da existência ou não da tabela CALENDARIO. Se existir, é algo bem simples:

-- código #3.1
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

declare @Mes tinyint, @Ano smallint;
set @Mes= 4;
set @Ano= 2020;

SELECT top (1) convert (char(10), Data, 103) as Data
  from dbo.CALENDARIO
  where DiaUtil = 1
        and Mes = @Mes and Ano = @Ano
  order by Ano, Mes, Dia;

cujo resultado é

p061_codigo #3.1

Se não existir a tabela CALENDARIO, a função datepart (weekday) e a tabela FERIADO podem ser utilizados para pular finais de semana e feriados.

-- código #3.2
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

declare @Mes tinyint, @Ano smallint;
set @Mes= 4;
set @Ano= 2020;

declare @FimMes date;
set @FimMes= eomonth (datefromparts (@Ano, @Mes, 1));

set datefirst 1; -- semana inicia na segunda-feira

with PrimeiroDiaUtil as (
SELECT datefromparts (@Ano, @Mes, 1) as Data
union all
SELECT dateadd (day, +1, P.Data)
  from PrimeiroDiaUtil as P
  where P.Data < @FimMes
        and (datepart (weekday, P.Data) >= 6
             or exists (SELECT * from Feriado as F where F.Data = P.Data))
)
SELECT convert (char(10), max (Data), 103) as Data
  from PrimeiroDiaUtil;

O código T-SQL anterior considera que o primeiro dia útil é o primeiro dia do mês e então verifica se esse dia ocorre no final de semana. Se for o caso, então soma 1 ou 2 ao dia, dependendo se é domingo ou sábado. Bem simples.

Os códigos T-SQL deste item podem ser transformados cada um em função de usuário.

3.2. Próximo dia útil

É o caso de, a partir de uma data, obter qual é o próximo dia útil. O algoritmo para obter o próximo dia útil depende da existência ou não da tabela CALENDARIO. Se existir, é algo bem simples:

-- código #3.3
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

declare @DataRef date;
set @DataRef= convert (date, '9/4/2020', 103);

SELECT top (1) convert (char(10), C.Data, 103) as [Data]
  from dbo.CALENDARIO as C
  where C.Data > @DataRef
        and C.DiaUtil = 1
  order by C.Data;

cujo resultado é

p061_codigo #3.3

A data foi escolhida propositalmente pois o 9 de abril de 2020 é quinta-feira sendo que o dia seguinte, dia 10, é feriado. Ou seja, somente a segunda-feira seguinte é o próximo dia útil. Um algoritmo errado, com dados incompletos, forneceria dia 10/4/2020 como dia útil.

Caso não existia a tabela CALENDARIO, a função datepart (weekday) e a tabela FERIADO podem ser utilizados para pular finais de semana e feriados.

-- código #3.4
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

declare @DataRef date;
set @DataRef= convert (date, '9/4/2020', 103);

set datefirst 1; -- semana inicia na segunda-feira

with ProximoDiaUtil as (
SELECT dateadd (day, +1, @DataRef) as Data
union all
SELECT dateadd (day, +1, P.Data)
  from ProximoDiaUtil as P
  where (datepart (weekday, P.Data) >= 6
        or exists (SELECT * from Feriado as F where F.Data = P.Data))
)
SELECT convert (char(10), max (Data), 103) as Data
  from ProximoDiaUtil;

Os códigos T-SQL anteriores podem ser transformados cada um em função de usuário.

3.3. Dia útil anterior

Obter o dia útil imediatamente anterior é bem semelhante ao item anterior, com algumas adaptações. Novamente, o algoritmo para obter o dia útil anterior depende da existência ou não da tabela CALENDARIO. Se existir, é algo bem simples:

-- código #3.5
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

declare @DataRef date;
set @DataRef= convert (date, '13/4/2020', 103);

SELECT top (1) convert (char(10), C.Data, 103) as [Data]
  from dbo.CALENDARIO as C
  where C.Data < @DataRef
        and C.DiaUtil = 1
  order by C.Data desc;

cujo resultado é

p061_codigo #3.5

Observe na cláusula ORDER BY que a ordem é decrescente, ou seja, inversa.

A data foi escolhida propositalmente pois o 13 de abril de 2020 é segunda-feira sendo que o dia de semana anterior, dia 10, é feriado. Ou seja, somente a quinta-feira anterior é o dia útil anterior. Um algoritmo errado, com dados incompletos, forneceria dia 10/4/2020 como dia útil.

Se não existir a tabela CALENDARIO, a função datepart (weekday) e a tabela FERIADO podem ser utilizados para pular finais de semana e feriados.

-- código #3.6
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

declare @DataRef date;
set @DataRef= convert (date, '13/4/2020', 103);

set datefirst 1; -- semana inicia na segunda-feira

with DiaUtilAnterior as (
SELECT dateadd (day, -1, @DataRef) as Data
union all
SELECT dateadd (day, -1, P.Data)
  from DiaUtilAnterior as P
  where (datepart (weekday, P.Data) >= 6
        or exists (SELECT * from Feriado as F where F.Data = P.Data))
)
SELECT convert (char(10), min (Data), 103) as Data
  from DiaUtilAnterior;

Os códigos T-SQL anteriores podem ser transformados cada um em função de usuário.

3.4. Último dia útil do mês

O algoritmo para obter o último dia útil do mês depende da existência ou não da tabela CALENDARIO. Se existir, é algo bem simples:

-- código #3.7
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

declare @Mes tinyint, @Ano smallint;
set @Mes= 5;
set @Ano= 2020;

SELECT top (1) convert (char(10), Data, 103) as Data
  from dbo.CALENDARIO
  where DiaUtil = 1
        and Mes = @Mes and Ano = @Ano
  order by Ano desc, Mes desc, Dia desc;

cujo resultado é

p061_codigo #3.7

Se não existir a tabela CALENDARIO, a função datepart (weekday) e a tabela FERIADO podem ser utilizadas para pular finais de semana e feriados.

-- código #3.8
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

declare @Mes tinyint, @Ano smallint;
set @Mes= 5;
set @Ano= 2020;

declare @InicioMes date;
set @InicioMes= datefromparts (@Ano, @Mes, 1);

set datefirst 1; -- semana inicia na segunda-feira

with UltimoDiaUtil as (
SELECT eomonth (datefromparts (@Ano, @Mes, 1)) as Data
union all
SELECT dateadd (day, -1, P.Data)
  from UltimoDiaUtil as P
  where P.Data > @InicioMes
        and (datepart (weekday, P.Data) >= 6
             or exists (SELECT * from Feriado as F where F.Data = P.Data))
)
SELECT convert (char(10), min (Data), 103) as Data
  from UltimoDiaUtil;

O código T-SQL anterior considera que o último dia útil é o último dia do mês e então verifica se esse dia ocorre no final de semana. Se for o caso, subtrai de um em um, saltando finais de semana e feriados contíguos, se houver. Bem simples.

Os códigos T-SQL deste item podem ser transformados cada um em função de usuário.

3.5. Adicionar n dias úteis a uma data

Esta também é uma solicitação que ocorre em tópicos de fóruns: qual é a data daqui a 5 dias úteis? O algoritmo para obter a data após n dias úteis depende da existência ou não da tabela CALENDARIO. Se existir, é algo bem simples:

-- código #3.9
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

declare @DataRef date, @DiasUteis smallint;
set @DataRef= convert (date, '9/4/2020', 103);
set @DiasUteis= 17;

with DiasUteis as (
SELECT top (@DiasUteis) Data
  from dbo.CALENDARIO
  where Data > @DataRef
        and DiaUtil = 1
  order by Data
)
SELECT convert (char(10), @DataRef, 103) as [Data base],
       @DiasUteis as [Dias úteis],
       convert (char(10), max (Data), 103) as N_DiasUteis
  from DiasUteis;

cujo resultado é o seguinte:

p061_codigo #3.9

Na CTE DiasUteis são listadas n linhas da tabela CALENDARIO onde n é o número de dias úteis a avançar no calendário. Após, basta selecionar a data da última linha listada, que é a que possui a data mais recente.

Tudo fica bem mais simples quando se tem tabela com calendário. Se não existir a tabela CALENDARIO, a função datepart (weekday) e a tabela FERIADO podem ser utilizados para pular finais de semana e feriados.

-- código #3.10
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

declare @DataRef date, @DiasUteis smallint;
set @DataRef= convert (date, '9/4/2020', 103);
set @DiasUteis= 17;

set datefirst 1; -- segunda-feira
with DiasUteis as (
SELECT @DataRef as Data, 0 as seq
union all
SELECT dateadd (day, +1, D.Data),
       case when datepart (weekday, dateadd (day, +1, D.Data)) < 6
                 and not exists (SELECT * from Feriado as F
                                 where F.Data = dateadd (day, +1, D.Data))
                 then (D.seq +1) else D.seq end
  from DiasUteis as D 
  where D.seq < @DiasUteis
) 
SELECT convert (char(10), @DataRef, 103) as [Data base],
       @DiasUteis as [Dias úteis],
       convert (char(10), max (Data), 103) as N_DiasUteis
  from DiasUteis;

Foi utilizada CTE recursiva para gerar sequencialmente as datas até chegar ao enésimo dia útil. Após, basta selecionar a data da última linha listada, que é a que possui a data mais recente. Caso o valor de dias úteis a somar seja superior a 100 dias corridos, deve-se acrescentar a opção MAXRECURSION ao final do código T-SQL.

Os códigos SQL anteriores podem ser transformados cada um em função de usuário.

3.6. Tempo decorrido em dias úteis

O algoritmo para calcular o tempo decorrido em dias úteis entre duas datas depende da existência ou não da tabela CALENDARIO. Se existir, é algo bem simples:

-- código #3.11
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

declare @D1 date, @D2 date;
set @D1= datefromparts (2020, 4, 9);
set @D2= datefromparts (2020, 4, 22);

SELECT convert (char(10), @D1, 103) as [Data inicial],
       convert (char(10), @D2, 103) as [Data final],
       count(*) as [Dias úteis]
  from dbo.CALENDARIO as C
  where C.Data between @D1 and @D2
        and C.DiaUtil = 1;

cujo resultado é

p061_codigo #3.11

Moleza!

O período escolhido foi proposital, pois nele há 2 feriados.

O código #3.11 pode ser transformado facilmente em função de usuário do tipo inline table-valued, conforme código T-SQL a seguir:

-- código #3.12
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

CREATE or alter FUNCTION dbo.intervalo_D (
     @D1 date, @D2 date
)
returns table
return
SELECT count(*) as diasUteis
  from dbo.CALENDARIO as C
  where C.Data between @D1 and @D2
        and C.DiaUtil = 1;
go

Se não existir a tabela CALENDARIO, a função datepart (weekday) e a tabela FERIADO podem ser utilizadas para calcular o tempo decorrido em dias úteis entre duas datas, pulando dias úteis e feriados.

-- código #3.13

declare @D1 date, @D2 date;
set @D1= datefromparts (2020, 4, 9);
set @D2= datefromparts (2020, 4, 22);

set datefirst 7; -- semana inicia no domingo (padrão de DATEDIFF)
SELECT convert (char(10), @D1, 103) as [Data inicial],
       convert (char(10), @D2, 103) as [Data final],
       (datediff (day, @D1, @D2) + 1)
        - (datediff (week, @D1, @D2) * 2)
        - (case when datepart (weekday, @D1) = 1 then 1 else 0 end)
        - (case when datepart (weekday, @D2) = 7 then 1 else 0 end)
        - (SELECT count(*) from dbo.FERIADO
             where Data between @D1 and @D2
                   and datepart (weekday, Data) not in (1, 7)) as [Dias úteis];

O código T-SQL anterior foi baseado no artigo Calculating Work Days, de Jeff Moden, sendo acrescentado o tratamento da tabela de feriados.

3.7. Tempo decorrido em horas úteis

O algoritmo para calcular o tempo decorrido em horas (e minutos) úteis entre duas datas depende da existência ou não da tabela CALENDARIO. Além disso, é necessário ter a informação de qual é o horário de serviço, em turnos ou corrido (sem intervalo).

Para desenvolvimento inicial vamos considerar que é necessário calcular quantas horas úteis determinado colaborador dedicou a uma atividade. O colaborador trabalha 30 horas semanais, de segunda-feira a sexta-feira e em turno único de 6 horas, das 13h às 19h.

-- código #3.14
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

declare @D1 datetime2(0), @D2 datetime2(0);
set @D1= datetime2fromparts (2020, 4, 9, 15, 55, 0, 0, 0);
set @D2= datetime2fromparts (2020, 4, 22, 15, 54, 0, 0, 0);

declare @Horario_inicio time(0), @Horario_fim time(0), 
        @Minutos_dia int;
set @Horario_inicio= '13:00';
set @Horario_fim= '19:00';
set @Minutos_dia= datediff (minute, @Horario_inicio, @Horario_fim);

with
calcDiasUteis as (
SELECT (@Minutos_dia * count(*)) as MinDias
  from dbo.CALENDARIO as C
  where C.Data > cast (@D1 as date)
        and C.Data < cast (@D2 as date)
        and C.DiaUtil = 1
),
calcMinutosUteis as (
SELECT (MinDias
        + case when cast (@D1 as date) = cast (@D2 as date)
                    then datediff (minute, @D1, @D2)
               when @D2 > @D1
                    then datediff (minute, cast (@D1 as time(0)), @Horario_fim)
                         + datediff (minute, @Horario_inicio, cast (@D2 as time(0)))
               else 0 end) as MinutosUteis
  from calcDiasUteis
)
SELECT format (@D1, 'dd/MM/yyyy HH:mm:ss') as D1,
       format (@D2, 'dd/MM/yyyy HH:mm:ss') as D2,
       MinutosUteis,
       (MinutosUteis / @Minutos_dia) as Dia,
       (MinutosUteis % @Minutos_dia /60) as Hora,
       (MinutosUteis % 60) as Minuto
  from calcMinutosUteis;

cujo resultado é

p061_codigo #3.14

A primeira CTE, calcDiasUteis, calcula quantos dias úteis há entre o dia seguinte ao início e o dia anterior ao final, multiplicando esse valor por 360, que é o número de minutos úteis de um dia de trabalho do colaborador.

A segunda CTE, calcMinutosUteis, calcula a quantidade de minutos que o colaborador trabalhou na data inicial e na data final e os soma ao valor calculado na CTE anterior. cabe destacar que esta CTE considera que as datas informadas para início e término são dias úteis.

Ao final, a quantidade de minutos calculada é exibida como minutos totais e também como dias, horas e minutos.

O código T-SQL anterior pode ser ampliado para outras condições, como mais de um turno ao dia, por exemplo. Também pode ser parametrizado e transformado em procedimento armazenado ou mesmo em função de usuário do tipo inline table-valued.


Este artigo contém trechos do capítulo 4 do artigo “Dominando datas e horas no SQL Server”, que é um guia sobre como utilizar datas e horas em T-SQL, contendo mais de 60 páginas em tamanho A4.


Publicidade

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

Este site utiliza o Akismet para reduzir spam. Saiba como seus dados em comentários são processados.

%d blogueiros gostam disto: