0
I created the following script that only inserts records that do not exist in the table teste_tb_planos_acoes
, put some conditions:
If the column value designacao
exists in the table teste_tb_planos_acoes
and the column status_fim = encerrado
, check if the column data_fim
is greater than >=30
days and update the record by updating the columns:
status_fim = aberto
Prazo = getdate()+2
But I need to put one more criterion, which I’m not getting:
When there is no MATCHED
, even if the value exists in the table teste_tb_planos_acoes
check whether DATEDIFF(DAY, b.data_fim, GETDATE()) > 45)
, if it is larger, allow entering the record, otherwise do not enter.
Follows the script:
MERGE teste_tb_planos_acoes AS T
USING
(
SELECT distinct
A.designacao,
A.Cliente,
desc_uds,
A.qtde_log,
A.rec_ano,
B.designacao AS designacao_B
FROM tb_calcula_hist_log A
LEFT JOIN teste_tb_planos_acoes B ON A.designacao = B.designacao
WHERE
(B.designacao IS NULL) OR
(B.status_fim = 'encerrado' and b.data_fim between dateadd(day, -45, cast( current_timestamp as date)) and dateadd(day, -10, cast( current_timestamp as date)))) AS U
ON U.designacao = T.designacao AND U.designacao_B IS NOT NULL
WHEN MATCHED THEN
UPDATE SET status_fim = 'aberto', prazo = GETDATE() + 2, qtde_log = U.qtde_log, reaberto = 'sim'
WHEN NOT MATCHED THEN
INSERT
(
designacao,
cliente,
area,
qtde_log,
data_cad,
data_email_env,
login_cadastro,
rec_num,
status_fim,
prazo
)
VALUES
(
U.designacao,
U.Cliente,
U.desc_uds,
U.qtde_log,
GETDATE(),
GETDATE(),
'w7spo082851\GEO',
U.rec_ano,
'aberto',
GETDATE() + 2
);
@Evaldo, your problem was to insert or update the table depending on the criteria. I’m not seeing this here in your post. Anyway, the most important thing is you have solved your problem.
– Eduardo Silva
I had only posted a portion of the code, updated the response.
– Edvaldo Lucena