Ratio 1:N check date, difference 6 month

Question

I am developing a SQL Server Database, which lists two tables:

Where a process has N movements, I need to return the following data: all Process that have the column Irrecuperavel(int) = 1 and that the "last" date entered in the Progress table for that Process DataAnda(date) be it >= 6 months.

SELECT  
       A.NumeroProcesso, MAX(A.DataAnda) 

FROM   Andamento AS A LEFT OUTER JOIN Processo AS P

ON     A.NumeroProcesso = P.NumeroProcesso
WHERE
       (P.Irrecuperavel = 1) AND 
       (DATEDIFF(MONTH, A.DataAnda, GETDATE()) >= 6)

GROUP BY A.NumeroProcesso 

My past returns but does not filter for the longest date.

Answer 1

What is missing from your implementation is filtering after getting the maximum date for each process. I could do this in two parts:

1. Identify the maximum date for each process
2. Apply the filter to obtain only those processes where the difference is more than 6 months:

SELECT  *
  FROM 
(
   SELECT NumeroProcesso, MAX(A.DataAnda) MaxDataAnda
     FROM Processo P
    INNER JOIN Andamentos A
       ON A.NumeroProcesso = P.NumeroProcesso
    WHERE P.Irrecuperavel = 1
) iP
WHERE DATEDIFF(MONTH, iP.MaxDataAnda, GETDATE())) >= 6

A more concise alternative is possible using the clause HAVING (since it is not possible to use the WHERE with aggregation functions)

SELECT A.NumeroProcesso
  FROM Andamento AS A 
 INNER JOIN Processo AS P
    ON A.NumeroProcesso = P.NumeroProcesso
 WHERE P.Irrecuperavel = 1
GROUP BY A.NumeroProcesso 
HAVING MIN(DATEDIFF(MONTH, A.DataAnda, GETDATE())) >= 6