Views: 27.929 views
Hello guys,
Good day.
In this post I will show how to perform various calculations with weekdays in SQL Server, creating a table with all the information already calculated and remaining just perform some simple SELECTs to get this information.
Prerequisite: Holiday Table
A prerequisite for this post is that you have already created the holiday table I commented on in the post. How to create a table of holidays (national, state, and mobile) in SQL Server. This table will be used to identify whether a specific date is a holiday or not.
Prerequisite: Functions for Business Day Calculation
Another prerequisite for our table, are 3 functions used to return business days, as below:
fncDia_Util_Previous
| 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 |
fncNext_Day_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 |
Creating the weekday table
After creating all the necessary prerequisites, let's create our weekday table.
| 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) |
With this, we get the following table:
Creating the business day functions
Once we create our Dia_Util table, we can use the functions below to make it easier to get the information:
fncQtde_Days_Use_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 |
fncAdd_Usday
| 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 |
fncLast_Day_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 |
Testing the functions:
That's it folks!
Until the next post!
Good morning and thank you for sharing your vast knowledge. You replied to Felipe Rodrigues that for Saturday to be considered a working day, it is only necessary to update the table after the generation or to change the case, as a simple curious that I am in SQL it will be possible for you to give me more precise indications on how to proceed these changes?
Thank you very much for your time
Dirceu, thanks for sharing I have a doubt that in the example I cannot use for what I need in the environment, I need to do between dates like
‘2020-09-26 00:00:00’ até ‘2020-10-25 23:59:59’
in the function above I could not adapt could help me?
Cool!
Thank you, Rafael.
I hope it was helpful to you
I want to mark Saturday as a business day. What would the codes look like?
Only update table after generation or change case
Very good Dirceu, that's what I needed!
It's taking too long to generate the table, how long does it take?
Gustavo, good afternoon. All right?
In environments where I implemented this table, it usually takes from 5 to 15 minutes to generate the data, depending on the hardware.