Hello,
I've created a query in which I've pulled all the data that I'm looking for, now I'd like to get the most current date for each part number (om.Part_Number)
SELECT DISTINCT
om.Part_Number AS Part_Number,
vq.PO_Date AS PO_Date,
vq.Unit_Cost AS Unit_Cost
FROM dbo.Order_Material om
LEFT OUTER JOIN dbo.vQV_Part_Purchasing vq ON om.PO_Number = vq.PO_Number
WHERE om.Part_Number IN ( '6051005' , '6051013' ) AND om.PO_Number IS NOT NULL AND
vq.Unit_Cost IS NOT NULL
ORDER BY om.Part_Number ASC, vq.PO_Date DESC
That returns data like this:
Part_Number | PO_Date | Unit_Cost
12345 | 02/25/2018 | 2.35
12346 | 02/25/2018 | 2.40
12346 | 03/02/2019 | 2.45
12346 | 03/01/2019 | 2.50
The results I'm looking for should be:
Part_Number | PO_Date | Unit_Cost
12345 | 02/25/2018 | 2.35
12346 | 03/02/2019 | 2.45
I've tried a couple of attempts at a subquery but I don't get the results I'm looking for.
This returns no records:
SELECT DISTINCT
om.Part_Number AS Part_Number,
vq.PO_Date AS PO_Date,
vq.Unit_Cost AS Unit_Cost
FROM dbo.Order_Material om
LEFT OUTER JOIN dbo.vQV_Part_Purchasing vq ON om.PO_Number = vq.PO_Number
WHERE om.Part_Number IN ( '6051005' , '6051013' ) AND om.PO_Number IS NOT NULL AND
vq.Unit_Cost IS NOT NULL AND vq.PO_Date = (SELECT MAX(vq.PO_Date)
FROM dbo.Order_Material om
LEFT OUTER JOIN dbo.vQV_Part_Purchasing vq ON om.PO_Number = vq.PO_Number)
Would a HAVING clause work better than a sub-query. I tried to write one but kept getting errors.
Any help would be appreciated.