Query For Last price for item from specific vendor

[Former Member]

Hi,

I need help for query that retrieve last price for items that come from specific vendor in specific period

Example, There are the following transaction in ( Goods Receipt PO , Table ( OPDN-PDN1 )) in April

Doc Date Doc Number Vendor Code Item Code Unit Price

01/4/2017 15 V01 301001 300

03/4/2017 15 V03 901001 300

15/4/2017 15 V03 301001 290

02/4/2017 15 V03 601002 50

24/4/2017 15 V03 901001 280

20/4/2017 15 V04 601002 45

Parameter Vendor Code : V03

From Date '01-04-2017' To date '30-04-2017'

Reasult: show last price for each item that come from this vendor in this period

Vendor Code : V03

Item Code Last Unit Price

301001 290

901001 280

601002 50

[Johan_H]

Hi Mohamed,

Please give this try:

SELECT r.ItemCode
       ,r.Price
       ,MAX(h.DocDate) AS MaxDocDate
FROM OPCH h
     INNER JOIN PCH1 r ON h.DocEntry = r.DocEntry
WHERE h.CardCode = '[%0]'
  AND h.DocDate BETWEEN [%1] AND [%2]
GROUP BY r.ItemCode
        ,r.Price
ORDER BY r.ItemCode

Regards,

Johan