Skip to Content
Former Member
Apr 18, 2012 at 10:04 PM

Last Purchase price / date with vendor info


I have the following query to display last purchase date, last purchase price, BP code and name:

SELECT T0.ItemCode,T1.ItemName, T1.FrgnName as 'Model #', MAX(T0.DocDate) 'Last Purchase Date', (SELECT MAX(Price) FROM POR1 WHERE ItemCode=T0.ItemCode AND DocDate= MAX(T0.DocDate)) 'Last Purchase Price', T0.BaseCard, T2.CardName

FROM POR1 T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OCRD T2 ON T0.BaseCard = T2.CardCode

Group By T0.ItemCode, T1.ItemName, T1.FrgnName, T0.BaseCard, T2.CardName

ORDER BY T0.[ItemCode]

It works fine for most items. However, some items show multiple lines and I have to manually select the one with the latest date.

The query works fine without the BP information but the multiple lines appeared after adding the T0.BaseCard, T2.CardName. Any idea how do I change the code to show only items with the latest price / date? Thanks.