If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > Database Server Software > Microsoft SQL Server > pulling max(date) not correct

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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?

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
Reply With Quote
  #3 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,794
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.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On