I'm using this script for inventory last sold. I would like to add a column to this to include “First Receipt Date” to see how many days our inventory has been on hand.
SELECT I.ITEMNMBR Item_Number,
Q.LOCNCODE Site_ID,
I.ITEMDESC Item_Description,
Q.QTYONHND Quantity_on_Hand,
Q.QTYONORD Quantity_on_Order,
U.BASEUOFM U_of_M,
CASE I.ITEMTYPE
WHEN 1 THEN 'Sales Inventory'
WHEN 2 THEN 'Discontinued'
WHEN 3 THEN 'Kit'
WHEN 4 THEN 'Misc Charges'
WHEN 5 THEN 'Services'
WHEN 6 THEN 'Flat Fee'
END Item_Type,
I.CURRCOST Current_Cost,
I.ITMCLSCD Item_Class,
coalesce(S.LastSale,'1/1/1900') Last_Sale_Date,
coalesce(Q.LSRCPTDT,'1/1/1900') Last_Receipt_Date,
coalesce(Q.LSORDVND,'') Last_Vendor
FROM IV00101 I
INNER JOIN
IV00102 Q
ON I.ITEMNMBR = Q.ITEMNMBR
AND RCRDTYPE = 2
LEFT OUTER JOIN
(SELECT ITEMNMBR, MAX(DOCDATE) LastSale, TRXLOCTN
FROM IV30300
WHERE DOCTYPE = 6
GROUP BY ITEMNMBR, TRXLOCTN) S
ON I.ITEMNMBR = S.ITEMNMBR
AND Q.LOCNCODE = S.TRXLOCTN
INNER JOIN
IV40201 U
ON U.UOMSCHDL = I.UOMSCHDL
WHERE Q.QTYONHND <> 0
