Post Views 32,345 views
Olá Pessoal,
Bom dia.
Neste post irei mostrar como realizar diversos cálculos com dias úteis no SQL Server, criando uma tabela com todas as informações já calculadas e restando apenas realizar alguns SELECTs simples para obter essas informações.
Pré-Requisito: Tabela de feriados
Um pré-requisito para esse post, é você já ter criado a tabela de feriados que eu comentei no post How to create a table with holidays (national, state and mobile) in SQL Server. Essa tabela será utilizada para identificar se uma data específica é feriado ou não.
Pré-Requisito: Funções para cálculo de dia útil
Outro pré-requisito para a nossa tabela, são 3 funções utilizadas para retornar os dias úteis, conforme abaixo:
fncDia_Util_Anterior
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE FUNCTION [dbo].[fncDia_Util_Anterior] ( @Data_Dia DATETIME ) RETURNS DATETIME AS BEGIN WHILE (1 = 1) BEGIN SET @Data_Dia = @Data_Dia - (CASE DATEPART(WEEKDAY, @Data_Dia) WHEN 1 THEN 2 WHEN 7 THEN 1 ELSE 0 END) IF EXISTS ( SELECT TOP 1 Nr_Dia FROM dbo.Feriado WITH ( NOLOCK ) WHERE Nr_Dia = DAY(@Data_Dia) AND Nr_Mes = MONTH(@Data_Dia) AND Tp_Feriado = '1' AND ( Nr_Ano = 0 OR Nr_Ano = YEAR(@Data_Dia) ) ) SET @Data_Dia = @Data_Dia - 1 ELSE BREAK END RETURN CAST(FLOOR(CAST(@Data_Dia AS FLOAT)) AS DATETIME) END |
fncProximo_Dia_Util
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE FUNCTION [dbo].[fncProximo_Dia_Util] ( @Data_Dia DATETIME ) RETURNS DATETIME AS BEGIN WHILE (1 = 1) BEGIN SET @Data_Dia = @Data_Dia + (CASE DATEPART(WEEKDAY, @Data_Dia) WHEN 1 THEN 1 WHEN 7 THEN 2 ELSE 0 END) IF EXISTS ( SELECT TOP 1 Nr_Dia FROM dbo.Feriado WITH ( NOLOCK ) WHERE Nr_Dia = DAY(@Data_Dia) AND Nr_Mes = MONTH(@Data_Dia) AND Tp_Feriado = '1' AND ( Nr_Ano = 0 OR Nr_Ano = YEAR(@Data_Dia) ) ) SET @Data_Dia = @Data_Dia + 1 ELSE BREAK END RETURN CAST(FLOOR(CAST(@Data_Dia AS FLOAT)) AS DATETIME) END |
fncDia_Util
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
CREATE FUNCTION [dbo].[fncDia_Util] ( @Data_Dia DATETIME ) RETURNS BIT AS BEGIN DECLARE @retorno BIT IF ( DATEPART(WEEKDAY, @Data_Dia) IN ( 1, 7 ) ) SET @retorno = 0 ELSE BEGIN IF EXISTS ( SELECT TOP 1 Nr_Dia FROM dbo.Feriado WITH ( NOLOCK ) WHERE Nr_Dia = DAY(@Data_Dia) AND Nr_Mes = MONTH(@Data_Dia) AND Tp_Feriado = '1' AND ( Nr_Ano = 0 OR Nr_Ano = YEAR(@Data_Dia) ) ) SET @retorno = 0 ELSE SET @retorno = 1 END RETURN @retorno END |
Criando a tabela de dias úteis
Após criar todos os pré-requisitos necessários, vamos criar a nossa tabela de dias úteis.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 |
SET LANGUAGE 'Brazilian' IF (OBJECT_ID('dbo.Dia_Util') IS NOT NULL) DROP TABLE dbo.Dia_Util CREATE TABLE dbo.Dia_Util ( Dt_Referencia DATETIME, Nr_Dia TINYINT, Nr_Mes TINYINT, Nr_Ano INT, Dt_Dia_Util_Anterior DATETIME, Dt_Proximo_Dia_Util DATETIME, Fl_Dia_Util BIT, Fl_Dia_Util_Incluindo_Sabado BIT, Fl_Feriado BIT, Nr_Dia_Semana TINYINT, Ds_Dia_Semana VARCHAR(13), Nr_Semana INT, Nr_Semana_Mes INT, Nr_Dia_Ano INT, Qt_Dias_Uteis_Mes INT NULL, Qt_Dias_Uteis_Ano INT NULL ) DECLARE @Dt_Inicial DATETIME = '19900101', @Dt_Final DATETIME = '20991231' WHILE (@Dt_Inicial <= @Dt_Final) BEGIN INSERT INTO dbo.Dia_Util SELECT @Dt_Inicial AS Dt_Referencia, DATEPART(DAY, @Dt_Inicial) AS Nr_Dia, DATEPART(MONTH, @Dt_Inicial) AS Nr_Mes, DATEPART(YEAR, @Dt_Inicial) AS Nr_Ano, dbo.fncDia_Util_Anterior(DATEADD(DAY, -1, @Dt_Inicial)) AS Dt_Dia_Util_Anterior, dbo.fncProximo_Dia_Util(DATEADD(DAY, 1, @Dt_Inicial)) AS Dt_Proximo_Dia_Util, dbo.fncDia_Util(@Dt_Inicial) AS Fl_Dia_Util, (CASE WHEN DATEPART(WEEKDAY, @Dt_Inicial) = 1 OR EXISTS(SELECT TOP 1 Nr_Dia FROM dbo.Feriado WITH(NOLOCK) WHERE Nr_Dia = DAY(@Dt_Inicial) AND Nr_Mes = MONTH(@Dt_Inicial) AND Tp_Feriado = '1' AND (Nr_Ano = 0 OR Nr_Ano = YEAR(@Dt_Inicial))) THEN 0 ELSE 1 END) AS Fl_Dia_Util_Incluindo_Sabado, (CASE WHEN EXISTS(SELECT TOP 1 Nr_Dia FROM dbo.Feriado WITH(NOLOCK) WHERE Nr_Dia = DAY(@Dt_Inicial) AND Nr_Mes = MONTH(@Dt_Inicial) AND Tp_Feriado = '1' AND (Nr_Ano = 0 OR Nr_Ano = YEAR(@Dt_Inicial))) THEN 1 ELSE 0 END) AS Fl_Feriado, DATEPART(WEEKDAY, @Dt_Inicial) AS Nr_Dia_Semana, DATENAME(WEEKDAY, @Dt_Inicial) AS Ds_Dia_Semana, DATEPART(WEEK, @Dt_Inicial) AS Nr_Semana, DATEPART(WEEK, @Dt_Inicial) - DATEPART(WEEK, @Dt_Inicial - DATEPART(DAY, @Dt_Inicial) + 1) + 1 AS Nr_Semana_Mes, DATEPART(DAYOFYEAR, @Dt_Inicial) AS Nr_Dia_Ano, NULL AS Qt_Dias_Uteis_Mes, NULL AS Qt_Dias_Uteis_Ano SET @Dt_Inicial = DATEADD(DAY, 1, @Dt_Inicial) END -- POPULA A QUANTIDADE DE DIAS ÚTEIS ATÉ A DATA DECLARE @Qt_Dias_Uteis_Mes INT, @Qt_Dias_Uteis_Ano INT SET @Dt_Inicial = '19900101' WHILE (@Dt_Inicial <= @Dt_Final) BEGIN SET @Qt_Dias_Uteis_Mes = (SELECT COUNT(*) FROM dbo.Dia_Util WITH(NOLOCK) WHERE Fl_Dia_Util = 1 AND Nr_Ano = YEAR(@Dt_Inicial) AND Dt_Referencia <= @Dt_Inicial AND Nr_Mes = MONTH(@Dt_Inicial)) SET @Qt_Dias_Uteis_Ano = (SELECT COUNT(*) FROM dbo.Dia_Util WITH(NOLOCK) WHERE Fl_Dia_Util = 1 AND Nr_Ano = YEAR(@Dt_Inicial) AND Dt_Referencia <= @Dt_Inicial) UPDATE A SET Qt_Dias_Uteis_Mes = @Qt_Dias_Uteis_Mes, Qt_Dias_Uteis_Ano = @Qt_Dias_Uteis_Ano FROM dbo.Dia_Util A WHERE Dt_Referencia = @Dt_Inicial SET @Dt_Inicial = DATEADD(DAY, 1, @Dt_Inicial) END -- ADICIONA MAIS INFORMAÇÕES NA TABELA (ATUALIZADO EM 09/10/2019) ALTER TABLE dbo.Dia_Util ADD Fl_Ultimo_Dia_Mes BIT, Fl_Ultimo_Dia_Util_Mes BIT UPDATE dbo.Dia_Util SET Fl_Ultimo_Dia_Mes = 0, Fl_Ultimo_Dia_Util_Mes = 0 UPDATE A SET A.Fl_Ultimo_Dia_Mes = 1 FROM dbo.Dia_Util A JOIN ( SELECT Nr_Ano, Nr_Mes, MAX(Dt_Referencia) AS Dt_Referencia FROM dbo.Dia_Util GROUP BY Nr_Ano, Nr_Mes ) B ON B.Dt_Referencia = A.Dt_Referencia UPDATE A SET A.Fl_Ultimo_Dia_Util_Mes = 1 FROM dbo.Dia_Util A JOIN ( SELECT Nr_Ano, Nr_Mes, MAX(Dt_Referencia) AS Dt_Referencia FROM dbo.Dia_Util WHERE Fl_Dia_Util = 1 GROUP BY Nr_Ano, Nr_Mes ) B ON B.Dt_Referencia = A.Dt_Referencia ALTER TABLE dbo.Dia_Util ADD Nr_Bimestre TINYINT, Nr_Trimestre TINYINT, Nr_Semestre TINYINT UPDATE dbo.Dia_Util SET Nr_Bimestre = CEILING((Nr_Mes * 1.0) / 2), Nr_Trimestre = CEILING((Nr_Mes * 1.0) / 3), Nr_Semestre = CEILING((Nr_Mes * 1.0) / 6) ALTER TABLE dbo.Dia_Util ADD Nm_Mes VARCHAR(20), Nm_Mes_Ano VARCHAR(30), Nm_Mes_Ano_Abreviado VARCHAR(20), Nr_Mes_Ano INT UPDATE dbo.Dia_Util SET Nm_Mes = DATENAME(MONTH, Dt_Referencia), Nm_Mes_Ano = DATENAME(MONTH, Dt_Referencia) + ' ' + CAST(Nr_Ano AS VARCHAR(4)), Nm_Mes_Ano_Abreviado = LEFT(DATENAME(MONTH, Dt_Referencia), 3) + '/' + RIGHT(Nr_Ano, 2), Nr_Mes_Ano = CAST(CAST(Nr_Ano AS VARCHAR(4)) + RIGHT('0' + CAST(Nr_Mes AS VARCHAR(2)), 2) AS INT) ALTER TABLE dbo.Dia_Util ADD Nr_Quinzena INT, Ds_Semana VARCHAR(20), Ds_Quinzena VARCHAR(20), Ds_Bimestre VARCHAR(20), Ds_Trimestre VARCHAR(20), Ds_Semestre VARCHAR(20) UPDATE dbo.Dia_Util SET Nr_Quinzena = (CASE WHEN Nr_Dia <= 15 THEN 1 ELSE 2 END), Ds_Semana = CAST(Nr_Ano AS VARCHAR(4)) + ' - ' + CAST(Nr_Semana AS VARCHAR(2)) + 'a Semana', Ds_Quinzena = CAST(Nr_Ano AS VARCHAR(4)) + ' - ' + (CASE WHEN Nr_Dia <= 15 THEN '1a Quinzena' ELSE '2a Quinzena' END), Ds_Bimestre = CAST(Nr_Ano AS VARCHAR(4)) + ' - ' + CAST(Nr_Bimestre AS VARCHAR(2)) + 'o Bimestre', Ds_Trimestre = CAST(Nr_Ano AS VARCHAR(4)) + ' - ' + CAST(Nr_Trimestre AS VARCHAR(2)) + 'o Trimestre', Ds_Semestre = CAST(Nr_Ano AS VARCHAR(4)) + ' - ' + CAST(Nr_Semestre AS VARCHAR(2)) + 'o Semestre' CREATE CLUSTERED INDEX Idx01 ON dbo.Dia_Util(Dt_Referencia) |
Com isso, conseguimos a seguinte tabela:
Criando as funções de dia útil
Uma vez que criamos a nossa tabela de Dia_Util, podemos utilizar as funções abaixo para facilitar a obtenção das informações:
fncQtde_Dias_Uteis_Mes
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
CREATE FUNCTION dbo.fncQtde_Dias_Uteis_Mes ( @Dt_Referencia DATETIME ) RETURNS INT AS BEGIN DECLARE @Retorno INT = 0 SELECT @Retorno = COUNT(*) FROM dbo.Dia_Util WITH(NOLOCK) WHERE Dt_Referencia < = CONVERT(DATE, @Dt_Referencia) AND YEAR(Dt_Referencia) = YEAR(@Dt_Referencia) AND MONTH(Dt_Referencia) = MONTH(@Dt_Referencia) AND Fl_Dia_Util = 1 RETURN @Retorno END |
fncAdiciona_Dias_Uteis
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
CREATE FUNCTION dbo.fncAdiciona_Dias_Uteis( @Dt_Referencia [datetime], @Qt_Dias_Uteis [int] ) RETURNS datetime AS BEGIN -- DECLARE @Dt_Referencia DATETIME = '2015-05-02 09:56:57.203' DECLARE @Data_Retorno DATE, @Retorno DATETIME, @Hora TIME = @Dt_Referencia, @Ranking INT DECLARE @Ranking_Dias_Uteis TABLE ( Ranking INT, Dt_Referencia DATETIME ) INSERT INTO @Ranking_Dias_Uteis SELECT ROW_NUMBER() OVER(ORDER BY Dt_Referencia) AS Ranking, Dt_Referencia FROM dbo.Dia_Util WITH(NOLOCK) WHERE Fl_Dia_Util = 1 SELECT @Ranking = (SELECT Ranking FROM @Ranking_Dias_Uteis WHERE Dt_Referencia = CONVERT(DATE, @Dt_Referencia)) IF (@Ranking IS NULL) SET @Ranking = (SELECT MIN(Ranking) FROM @Ranking_Dias_Uteis WHERE Dt_Referencia >= CONVERT(DATE, @Dt_Referencia)) SELECT @Data_Retorno = Dt_Referencia FROM @Ranking_Dias_Uteis WHERE Ranking = @Ranking + @Qt_Dias_Uteis SET @Retorno = CONVERT(DATETIME, CONVERT(VARCHAR(10), @Data_Retorno, 112) + ' ' + CONVERT(VARCHAR(12), @Hora)) RETURN @Retorno END |
fncUltimo_Dia_Util
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
CREATE FUNCTION dbo.fncUltimo_Dia_Util( @Dt_Referencia DATETIME ) RETURNS DATETIME AS BEGIN DECLARE @Ano INT = YEAR(@Dt_Referencia), @Mes INT = MONTH(@Dt_Referencia), @Retorno DATETIME SELECT @Retorno = MAX(Dt_Referencia) FROM dbo.Dia_Util WITH(NOLOCK) WHERE Nr_Ano = @Ano AND Nr_Mes = @Mes AND Fl_Dia_Util = 1 RETURN @Retorno END |
Testando as funções:
And that's it, folks!
Até o próximo post!
Portuguese 
English
Bom dia e muito obrigado por partilhar os seus vastos conhecimentos. O Sr. respondeu ao Felipe Rodrigues que para o sábado ser considerado dia útil é Só atualizar a tabela após a geração ou mudar o case, como um simples curioso que sou em SQL será possível o Sr. dar-me indicações mais precisas sobre como proceder a essas modificações?
Muito Obrigado pelo seu tempo
Dirceu, obrigado por compartilha estou com um duvida que nao conseguir no exemplo usar para o que preciso no ambiente, estou precisando fazer entre datas tipo
‘2020-09-26 00:00:00’ até ‘2020-10-25 23:59:59’
na função acima não conseguri adaptar poderia me auxiliar?
Show de bola!
Obrigado, Rafael.
Espero que tenha sido útil pra você
Quero marcar o sábado como dia útil. Como ficaria os código ?
Só atualizar a tabela após a geração ou mudar o case
Muito bom Dirceu, era o que eu precisava!
Ta demorando muito pra gerar a tabela, quanto tempo leva ?
Gustavo, boa tarde. Tudo bem?
Nos ambientes onde implementei essa tabela, costuma demorar de 5 a 15 minutos para gerar os dados, de acordo com o hardware.