cancel
Showing results for 
Search instead for 
Did you mean: 

Last Purchase price / date with vendor info

Former Member
0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Ken,

I have tested your query on my database it working fine.

The multiple lines come when the items purchase from multiple vendor.

is your query returns following records

1) I001 ITEM1  27/02/12  1000 V001 Vendor1

2) I001 ITEM1  27/02/12  1000 V001 Vendor1

Former Member
0 Kudos

Yes. You are correct. That makes sense.

Thanks for your help.

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Ken,

Why are you not taking last purchase date from OITM itself...column LastPurDat

Former Member
0 Kudos

Hi Ken,

Have you check with distinct keyword after select keyword.

if not please add the distinct keyword after select keyword. still u will find any problem

instead of using BaseCard field use CardCode field of OPOR Table.

Regards

Sachin

Former Member
0 Kudos

Hi Sachin,

I tried both select distinct and used OPOR.cardcode instead. However, it still doesn't work.

It'd be too cumbersome to go thru the exported spreadsheet to sort out multi line items with the latest dates. Any idea?