Lowell (3/14/2013)
With TheCheapest
AS
(
SELECT MIN(Price) as Cheapest, ProductID, SupplierID, BaseSupplierID
FROM test1
GROUP By ProductID, SupplierID, BaseSupplierID
) ,
BaseSupplierPrices AS
(
SELECT MIN(Price) as BasePrice, ProductID, SupplierID, BaseSupplierID
FROM test1
WHERE SupplierID= BaseSupplierID
GROUP By ProductID, SupplierID, BaseSupplierID
)
SELECT *
FROM TheCheapest
LEFT OUTER JOIN BaseSupplierPrices
ON TheCheapest.ProductID = BaseSupplierPrices.ProductID
WHERE TheCheapest.Cheapest < BaseSupplierPrices.BasePrice
That's the way I probably would have done it because it shows all of the suppliers with a lower price than the base. It will give the "decision maker" the option to pick a supplier and that's important because a supplier with the lowest price might be on some black list or in another country were lead time might become a problem.
It also shows what the current price is so that if both less expensive suppliers are in another country or there are other mitigating circumstances (like shipping costs), the decision maker can do the comparison and decide if it's actually worth it.
--Jeff Moden
Change is inevitable... Change for the better is not.