Relationship 1: N check date, difference 6 month

Question

Relationship 1: N check date, difference 6 month

Navigation

#1 by (2 votes)

2

I'm developing a SQL Server procedure, which lists two tables:

WhereaprocesshasN-statements,Ineedtoreturnthefollowingdata:allProcessNumbersthatarewiththeIrrecuperavel(int)=1columnandthe"last" date entered in the Process table for that% Process_Code% is DataAnda(date) 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 procedure returns but does not filter for the longest time.

sql sql-server c# asp.net tsql

asked by anonymous 28.11.2017 / 17:10

1 answer

Why are boolean values converted to string in camel case in C #? An unhandled exception occured. I can not understand this message, no detail comes

score 2 · Accepted Answer

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

Identify the maximum date for each of the processes

Apply the filter to get only processes where the difference is greater 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 HAVING clause (since it is not possible to use WHERE with aggregate 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