SQL - Retrieving the most recent receipt info for an item

It's been quite a while since I have posted a SQL script, and it's funny, because this is what I had in mind when I started out my blog. Shaun Childers posted a question on the Microsoft Dynamics GP public newsgroup, as follows:

"We are trying to create a script that will pull together our most recent
purchasing information only. The results should give all inventory items
with a qty on hand > 0, the current cost (we are average perpetual), the most
recent receipt number, the unit cost for that receipted item, the receipt date,
and the vendor name. I have tried to put this together, but have been
unsuccessful."

At first, this query may not seem to complex, but when you start to analyze the information being requested, it becomes apparent that using a standard set based query is not going to be as simple. Luckily enough, we can take advatage of the latest T-SQL enhancements to use ranks and partitions on sets to deliver the requested query, as follows:

-- Created by: Mariano Gomez, MVP
SELECT A.ITEMNMBR
 , B.ITEMDESC
 , B.CURRCOST
 , ISNULL(R.receiptdate, '01/01/1900') AS LastReceiptDate
 , ISNULL(R.UNITCOST, 0.00) AS UNITCOST
 , ISNULL(R.VENDNAME, '') AS VENDNAME
FROM IV00102 A
 LEFT OUTER JOIN IV00101 B ON (A.ITEMNMBR = B.ITEMNMBR)
 LEFT OUTER JOIN (
  SELECT ITEMNMBR, UNITCOST, receiptdate, VENDNAME FROM (
   SELECT C.ITEMNMBR, C.UNITCOST, D.receiptdate, D.VENDNAME, RANK() OVER
    (PARTITION BY C.ITEMNMBR ORDER BY D.receiptdate DESC) AS RECEIPT_RANK
   FROM POP30310 C
    LEFT OUTER JOIN POP30300 D ON (C.POPRCTNM = D.POPRCTNM)
  ) Receipts WHERE RECEIPT_RANK = 1
 ) R ON (A.ITEMNMBR = R.ITEMNMBR)
WHERE (A.RCRDTYPE = 1) AND (A.QTYONHND > 0)

In Microsoft Dynamics GP v10, the POP Receipt history tables also hold In-Transit Transfer transactions. If you are looking to retrieve strictly vendor receipts, you may change the query as follows:

-- Created by: Mariano Gomez, MVP
SELECT A.ITEMNMBR
  , B.ITEMDESC
  , B.CURRCOST
  , ISNULL(R.receiptdate, '01/01/1900') AS LastReceiptDate
  , ISNULL(R.UNITCOST, 0.00) AS UNITCOST
  , ISNULL(R.VENDNAME, '') AS VENDNAME
FROM IV00102 A
 LEFT OUTER JOIN IV00101 B ON (A.ITEMNMBR = B.ITEMNMBR)
 LEFT OUTER JOIN (
  SELECT ITEMNMBR, UNITCOST, receiptdate, VENDNAME FROM (
   SELECT C.ITEMNMBR, C.UNITCOST, D.receiptdate, D.VENDNAME, RANK() OVER
     (PARTITION BY C.ITEMNMBR ORDER BY D.receiptdate DESC) AS RECEIPT_RANK
    FROM POP30310 C
     LEFT OUTER JOIN POP30300 D ON (C.POPRCTNM = D.POPRCTNM)
    WHERE D.POPTYPE <> 8
   ) Receipts WHERE RECEIPT_RANK = 1
  ) R ON (A.ITEMNMBR = R.ITEMNMBR)
WHERE (A.RCRDTYPE = 1) AND (A.QTYONHND > 0)

Hope you find this query useful.

Until next post,

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/