Update of holidays in SQL Server table

Question

Update of holidays in SQL Server table

Navigation

#1 by (1 votes)

0

I have a SQL Server table called DM_Time. Her fields are:

DtRef
AnoRef
MesRef
DiaRef
MesExtenso
CodDiaSemana
SemanaNumero
IndFeriado

And they already have in it days marked as holidays (in this case, IndFeriado = 'YES'). But what I was wanting to do was the following, for example: if a date that was on a Friday (CodDiaSemana = 6) is not a holiday, but the day before was, I have to do an UPDATE for that Friday to be considered a holiday also (type a holiday). I tried something like this:

UPDATE DM_Tempo
SET IndFeriado = 'SIM'
WHERE AnoRef IN (2017,2018)
AND IndFeriado = 'NÃO'
AND CodDiaSemana = 6
AND IndFeriado IN (
SELECT IndFeriado FROM DM_TempoEmpresa T2 WHERE T2.DtRef = 
DATEADD(DAY,-1,T1.DtRef) AND T2.IndFeriado = 'SIM'
)
order by 2,1

But it did not work. What would you do?

sql sql-update

asked by anonymous 17.08.2018 / 17:07

1 answer

Use javascript to trigger modal ulkit css Type mismatch: can not convert from Unmarshaller to Pool.Unmarshaller

score 1 · Accepted Answer

This should solve your problem:

UPDATE tabela_tempo SET IndFeriado = 'SIM'
FROM DM_Tempo as tabela_tempo
WHERE tabela_tempo.AnoRef IN (2017, 2018)
  AND tabela_tempo.IndFeriado = 'NÃO'
  AND tabela_tempo.CodDiaSemana = 6
  AND EXISTS (SELECT 1 
                FROM DM_TempoEmpresa T2 
                WHERE T2.DtRef = DATEADD(DAY, -1, tabela_tempo.DtRef)
                  AND T2.IndFeriado = 'SIM')
order by 2,1

You did not "map" the table from the outside (which will be updated) - in fact you referred to it as T1 but without adding alias in update )