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.