Insert records that only exist in the source table with MERGE

Question

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
);

Answer 1

I think you have to remove the conditions from the initial query and put their conditions as columns. So you will make MATCH whenever there is registration (regardless of the other conditions) but so you will be able to put these criteria at the time of MATCH. Something like this:

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,
    CASE WHEN B.designacao IS NOT NULL AND 
                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)) THEN 1 ELSE 0 END as CanUpdate,
    CASE WHEN B.designacao IS NOT NULL AND DATEDIFF(DAY, b.data_fim, GETDATE()) > 45) as CanInsert
FROM tb_calcula_hist_log A
LEFT JOIN teste_tb_planos_acoes B ON A.designacao = B.designacao) AS U
     ON U.designacao = T.designacao AND U.designacao_B IS NOT NULL
WHEN MATCHED AND U.CanUpdate = 1 THEN
    UPDATE SET status_fim = 'aberto', prazo = GETDATE() + 2, qtde_log = U.qtde_log, reaberto = 'sim'
WHEN MATCHED AND U.CanInsert = 1 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
    );
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
);

Answer 2

Thank you ! A colleague in another forum managed to help me.

   MERGE teste_tb_planos_acoes AS T
 USING
(
   SELECT distinct
    A.designacao, 
    A.Cliente,
    desc_uds,
    A.qtde_log,
    A.rec_ano,
    O.data_fim,
    O.dif_dias
FROM tb_calcula_hist_log A
OUTER APPLY
(
    SELECT TOP(1)
        B.data_fim,
        B.status_fim,
        DATEDIFF(DAY, B.data_fim, GETDATE()) as dif_dias
    FROM teste_tb_planos_acoes B 
    WHERE 
        B.designacao = A.designacao
    ORDER BY
        B.data_fim 
   ) O    
   WHERE
    (O.status_fim IS NULL) OR
    ((O.status_fim = 'encerrado') and
     (O.dif_dias between 10 and 30 or O.dif_dias > 45) and
     (not exists
         (select 1 from teste_tb_planos_acoes E
          where
              E.designacao = A.designacao and
              E.status_fim = 'aberto')))
         ) AS U
           ON 
             U.designacao = T.designacao AND
             U.data_fim = T.data_fim AND
             U.dif_dias between 10 and 30
          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
);