Latest Purchase for Each Supplier for a Product

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Latest Purchase for Each Supplier for a Product

Author

Topic

terbs
Starting Member

29 Posts

Posted - 2008-09-09 : 22:34:09

I need to peform a Select statement which retrieves the most recent Buy price of a certain product from each supplier it has came from.

All I can seem to manage so far is a list of the suppliers and the most recent Date.

SELECT Max(TBLPurchaseOrder.Date) AS MaxOfDate, TBLPurchaseOrder.SupplierID
FROM TBLPurchaseOrderItem INNER JOIN TBLPurchaseOrder ON TBLPurchaseOrderItem.PONumber = TBLPurchaseOrder.PONumber
GROUP BY TBLPurchaseOrderItem.PARTID, TBLPurchaseOrder.SupplierID
HAVING (((TBLPurchaseOrderItem.PARTID)=@PartID))
ORDER BY Max(TBLPurchaseOrder.Date) DESC

What I need to add to this is SupplierCode, BuyPrice and SupplierName.

SupplierCode and BuyPrice are fields belonging to TBLPurchaseOrderItem.

SupplierName is a field belonging to TBLSupplier which joins on TBLPurchaseOrder.SupplierID.

Any help would be great, cheers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-09 : 22:58:30

[code]SELECT P.PARTID, P.SupplierID, P.SupplierName, P.SupplierCode, P.[Date], P.BuyPrice
FROM
(
SELECT i.PARTID, O.SupplierID, S.SupplierName, S.SupplierCode, O.[Date], I.BuyPrice,
row_no = row_number() OVER (PARTITION BY i.PARTID, O.SupplierID
ORDER BY O.Date DESC)
FROM TBLPurchaseOrderItem I
INNER JOIN TBLPurchaseOrder O
ON I.PONumber = O.PONumber
INNER JOIN TblSupplier S
ON O.SupplierID = S.SupplierID
WHERE I.PARTID = @PartID
) P
WHERE P.row_no = 1[/code]

KH
[spoiler]Time is always against us[/spoiler]