Subtract Saturdays, Sundays and specific dates from another table

Navigation
0

Good afternoon,

I have a table: National Holidays

  • id
  • data
  • dascricao

I have a table: holidays (single and spliced)

  • id
  • data
  • description

in another table: obligation (where through a user-informed "long-term" date I would have to calculate -5 days taking Saturdays, Sundays and if I have any spare date in the holiday table, and give the dateCorrect.

Example.

termLegal = 26/01/2017

Corrected data would have to be = 1/18/2017

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

    
asked by anonymous 16.02.2017 / 20:39

1 answer

0

I took the liberty of not reading the table to check on Saturdays and Sundays, as there is a function in the 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 last PRINT is for test purposes only. In production probably this code will be in a Stored Procedure I imagine.

    
16.02.2017 / 23:30
Wildfly changes GMT How to prevent SQL injection in my PHP code?