Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2012
    Posts
    23

    pulling max(date) not correct

    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

  2. #2
    Join Date
    Dec 2012
    Posts
    23
    PS:

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

    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

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,910
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Dec 2012
    Posts
    23
    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....
    Last edited by PennyLayne; 12-22-12 at 20:49.

  5. #5
    Join Date
    Dec 2012
    Posts
    23
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •