Extracting the most recent quote date for a part from a list of multiple parts using field formula

Hi,

I have a list of part ID (Part_ID) and for each part there are a number of quotes (Quote_Price) and a dated for each quote (Quote_Date, which is a date time value), for example:
Part_ID
      Quote_Price      Quote_Date
P1      150      28/01/2004 00:00:00
P1      160      01/08/2008 00:00:00
P1      240      28/01/2011 00:00:00
P2      20      01/07/2009 00:00:00
P2      15      24/03/2011 00:00:00

What I want to is build is a formula field that extracts the most recent quote date and the corresponding quote price for each part ID.  I have tried to create an array for all the quote dates listed for a part then tried to define the most recent date using the maximum function but this (or my coding!) hasn’t worked.  Any advice would be appreciated.
steve_yorkAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
Base your report on a query like this:



SELECT t1.Part_ID, t1.Quote_Price, t1.Quote_Date
FROM SomeTable t1 INNER JOIN
    (SELECT t2.Part_ID, MAX(t2.Quote_Date) AS Quote_Date
    FROM SomeTable t2
    GROUP BY t2.Part_ID) z ON t1.Part_ID = z.Part_ID AND t1.Quote_Date = z.Quote_Date
ORDER BY t1.Part_ID

Open in new window



Be advised that that will return multiple records for a given part if that part has >1 quote for whatever the latest date value happens to be.
0
 
mlmccCommented:
Another way is to group the report by partId and sort the groups by date.
The record you want will be in the group header if sorted descending or the footer is sorted ascending.

mlmcc
0
 
steve_yorkAuthor Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.