Skip to Content
avatar image
Former Member

Query For Last price for item from specific vendor

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • May 22, 2017 at 06:19 AM

    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

    Add comment
    10|10000 characters needed characters exceeded