-- Consulta para ver los que estan fallando

SELECT A.CMD_Id, AA.DocApiID,A.EmpID,A.CMD_DniDocId
FROM TSSGESDOC.dbo.GesDoc AA
         INNER JOIN
     (SELECT A.CMD_Id, MAX(B.DocID) AS MaxDocId,A.EmpID,A.CMD_DniDocId
      FROM TESISSEG.dbo.AS_CAMBIOMEDIACION A
               INNER JOIN TSSGESDOC.dbo.GesDoc B ON A.EmpID = B.EmpID AND B.DocID LIKE CONCAT ('MED100045N', RIGHT(REPLICATE('0',8) + CAST(A.CMD_Id as varchar ) ,8),'%')
          AND B.ApaId = 'CAMBIOMEDIACION'
          AND B.DocApiID != 0
      GROUP BY A.CMD_Id,A.EmpID,A.CMD_DniDocId) A ON AA.EmpID = A.EmpID AND AA.DocID = A.MaxDocId AND AA.ApaId = 'CAMBIOMEDIACION'
WHERE AA.ApaId = 'CAMBIOMEDIACION' AND A.CMD_DniDocId IS NULL OR (A.CMD_DniDocId) = ''

-- Consulta para actualizar los que estan fallando , aqui actualiza todos
-- != y <> no funcionan correctamente y no hemos podido ponerle ese filtro par actualizar solo los que faltaban


UPDATE AA
SET AA.CMD_CartaDocId  = D.DocApiID
    FROM
TESISSEG.dbo.AS_CAMBIOMEDIACION AA
LEFT JOIN
(SELECT A.CMD_Id, AA.DocApiID,A.EmpID
FROM TSSGESDOC.dbo.GesDoc AA
INNER JOIN
(SELECT A.CMD_Id, MAX(B.DocID) AS MaxDocId,A.EmpID
FROM TESISSEG.dbo.AS_CAMBIOMEDIACION A
INNER JOIN TSSGESDOC.dbo.GesDoc B ON A.EmpID = B.EmpID AND B.DocID LIKE CONCAT ('MED100109N', RIGHT(REPLICATE('0',8) + CAST(A.CMD_Id as varchar ) ,8),'%')
AND B.ApaId = 'CAMBIOMEDIACION'
AND B.DocApiID != 0
GROUP BY A.CMD_Id,A.EmpID) A ON AA.EmpID = A.EmpID AND AA.DocID = A.MaxDocId AND AA.ApaId = 'CAMBIOMEDIACION'
WHERE AA.ApaId = 'CAMBIOMEDIACION') D ON AA.EmpID = D.EmpId AND AA.CMD_Id = D.CMD_Id