Subtract Saturdays, Sundays and specific dates from another table

Question

Good afternoon,

I have a table: national holidays

• id
• date
• doubt

I have a table: holidays (loose ends and amendments)

• id
• date
• description

in another table: obligation (where through a date informed by the user "prazoLegal" I would have to calculate -5 days taking Saturdays, Sundays and if I have any separate date on the holiday table, and give the dataCorrigida.

Example.

prazoLegal = 26/01/2017

dataCorrigida would have to be = 18/01/2017

ie (-8) because 25/01/2017 is a holiday in SP and this in the table of individual holidays and 21/01/2017 is Saturday and 22/01/2017 is Sunday.

Answer 1

I took the liberty of not reading the table to check Saturdays and Sundays as there is a function in sql server for this. see if it solves your problem.

DECLARE @data DATE = '2017-01-26'
DECLARE @DiasUteisRemovidos INTEGER = 0
DECLARE @DiaSemana INTEGER 
DECLARE @feriado INT
DECLARE @emenda INT

WHILE @DiasUteisRemovidos < 5
BEGIN
    --subtrai 1 dia da data final
    SET @data = DATEADD(day, -1, @data)
    --pega o dia da semana que a data cai
    SET @DiaSemana =  DATEPART(dw,@data)
    --Verifica se é feriado na tabela de feriados ou emenda na tabela de emendas
    SELECT @feriado = COUNT(*) FROM feriados WHERE data = @data
    SELECT @emenda = COUNT(*) FROM emendas WHERE data = @data
    --Se a data estiver entre domingo e sábado e não for feriado nem emenda, conta que removeu 1 dia útil
    IF @DiaSemana > 1 AND @DiaSemana < 7 AND @feriado = 0 AND @emenda = 0
    SET @DiasUteisRemovidos +=1
END

PRINT CONVERT(char(30), @data)

The latest PRINT is only for test purpose. In production probably this code will be in a Stored Procedure I imagine.