How to calculate business days in SQL Server (dCalendario Table)

Hey guys,
Good morning.

In this post I will show how to perform various calculations with working days in SQL Server, creating a table with all the information already calculated and all that remains is to perform some simple SELECTs to obtain this information.

Prerequisite: Holiday Table

A prerequisite for this post is that you have already created the holiday table that I mentioned in the post How to create a table with 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 working day calculation

Another prerequisite for our table are 3 functions used to return business days, as follows:

fncDia_Util_Previous

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

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

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 working days table

After creating all the necessary prerequisites, let's create our working days table.

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 obtained the following table:

SQL Server - Dias Úteis e Feriados — SQL Server - Business Days and Holidays

Creating the workday roles

Once we have created our Day_Util table, we can use the functions below to facilitate obtaining the information:

fncQty_Days_Working_Months

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

fncAdds_WorkingDays

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

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:

SQL Server - Como calcular dias úteis e feriados função tabela — SQL Server - How to calculate working days and holidays table function

That's it, folks!
Until the next post!

Databases Data Formatting and Validation SQL Server date calculations date day of month business day business days sql sql server

How to calculate business days in SQL Server (dCalendario Table)

Prerequisite: Holiday Table

Prerequisite: Functions for working day calculation

Creating the working days table

Creating the workday roles

Dirceu Resende

Comments (0)

Prerequisite: Holiday Table

Prerequisite: Functions for working day calculation

Creating the working days table

Creating the workday roles

Dirceu Resende

Related posts

Comments (0)