Hello,
I am trying to get the current inventory of part numbers that meet certain criteria. What i am getting is the inventory information at the time that each transaction was posted. So I have to use excel formulas to get the row with the highest Post Order to return what the current inventory level is.
Is there a way to only return the current inventory in SQL, so that every row returned is the current inventory?
This is what i have:
SELECT LineItem.ItemID, InventoryCosts.Quantity, InventoryCosts.PostOrderNumber, InventoryCosts.TransDate,
FROM InventoryCosts InventoryCosts, LineItem LineItem
WHERE InventoryCosts.ItemRecNumber = LineItem.ItemRecordNumber AND ((InventoryCosts.RecordType In (30,50)) AND (LineItem.ItemIsInactive=0)
ORDER BY LineItem.ItemID
This is what it gives me:
| ItemId | Quantity | PostOrder | TransDate |
| 1025-040-16B-E1 | 37 | 11392 | 6/24/2016 |
| 1025-040-16B-F1 | 150 | 11393 | 6/24/2016 |
| 1025-040-16B-F1 | 135 | 26478 | 12/13/2018 |
| 1255-063-17A-E1 | 22 | 34492 | 11/14/2019 |
| 1255-063-17A-E1 | 9 | 35268 | 12/20/2019 |
| 1255-063-17A-E1 | 8 | 35438 | 1/2/2020 |
| 1255-063-17A-E1 | 7 | 35583 | 1/3/2020 |
| 1385-040-18A-F1 | 56 | 20719 | 3/16/2018 |
| 1385-040-18A-F1 | 54 | 20776 | 3/23/2018 |
| 1385-040-18A-F1 | 50 | 22113 | 6/7/2018 |
| 1385-040-18A-F1 | 47 | 34919 | 12/10/2019 |
