pulling max(date) not correct

PennyLayne · #1 (**permalink**) 12-21-12, 15:10

Code:

SELECT P.ID, P.QTY, T_DATE 

FROM PARTS P

INNER JOIN INVENTORY I ON P.ID = I.PART_ID 

WHERE P.QTY > 0 AND I.CODE = ‘R’ 

GROUP BY P.ID, P.QTY, I.T_DATE 

HAVING MAX(T_DATE) !> DATEADD(MONTH,-12,GETDATE())

Hi,
I am trying to make the sql only pull records where the highest date stored is older than a year ago.

so, based on today's date 12/21/2012:
if the highest (max) T_DATE is 3/12/2012 => don't pull that record
if the highest (max) T_DATE is 11/30/2011 => pull that record into the recordset

the statement is off, it is pulling records that should not be included.
TIA

PennyLayne · #2 (**permalink**) 12-21-12, 15:26

PS:

It is adding in earlier values. How do I get it to only evaluate the oldest/latest date?

Quote:

part qty date
123 3 evaluate the latest (max) date
234 9 evaluate the latest (max) date

It shouldn't bring in the older date values

Pat Phelan · #3 (**permalink**) 12-21-12, 18:42

As long as you don't mind ties, try:

Code:

SELECT P.ID, P.QTY, T_DATE 
   FROM PARTS P
   INNER JOIN INVENTORY I
      ON P.ID = I.PART_ID 
   WHERE P.QTY > 0
      AND I.CODE = ‘R’ 
      AND I.T_DATE = (SELECT Max(z.T_DATE)
         FROM INVENTORY AS z
         WHERE  z.PART_ID = p.ID
            AND z.T_DATE < DateAdd(month, -12, GetDate())

There is a way to get only one row returned, but it uses more advanced concepts that are harder to understand and explain for your users.

-PatP

PennyLayne · #4 (**permalink**) 12-22-12, 20:35

Made this re-write as suggested:

Code:

SELECT P.ID, P.QTY, T_DATE 
     FROM PARTS P
     INNER JOIN INVENTORY I 
          ON P.ID = I.PART_ID 
     WHERE P.QTY > 0 
          AND I.CODE = ‘R’ 
          AND I.T_DATE = (SELECT MAX(Z.T_DATE)
               FROM INVENTORY Z
               WHERE Z.PART_ID = P.ID
                    AND Z.T_DATE < DATEADD(MONTH,-12,GETDATE()))
     ORDER BY P.ID

The statement is still off.
My data has this:

part qty date
AB1 7 12/22/2011 (has earlier dates, but significant is today's date 1 year ago)
AB1 7 12/22/2011
AB1 7 12/22/2011
AB1 7 12/22/2011
AB1 7 3/19/2012
AB1 7 3/19/2012
AB1 7 4/9/2012
AB1 7 4/9/2012 ...
AB1 7 9/17/2012 (latest/Max date)

Part AB1 is being returned when it should not.
Funny thing is that some of the other parts that previously were being incorrectly pulled into the recordset, are now not.
I think that because the record contained today's date - 2011, it somehow got pulled in.

As a test, I can see that MAX(T_DATE) is being returned, but this piece, "AND Z.T_DATE < DATEADD(MONTH,-12,GETDATE()))", is where it goes wrong.

PS: also tried not greater than: z.t_date !> dateadd etc....

PennyLayne · #5 (**permalink**) 12-22-12, 22:03

Thanks for your help Pat. I got it to work by doing this:

Code:

SELECT P.ID, P.QTY, T_DATE 
     FROM PARTS P
     INNER JOIN INVENTORY I 
          ON P.ID = I.PART_ID 
     WHERE P.QTY > 0 
          AND I.CODE = ‘R’ 
          AND I.T_DATE = (SELECT MAX(Z.T_DATE)
               FROM INVENTORY Z
               WHERE Z.PART_ID = P.ID)
          AND I.T_DATE !> DATEADD(MONTH,-12,GETDATE()))
     ORDER BY P.ID

Internet Services

Web Development

Digital Marketing

Consumer Tech