CODEWITHSUNDEEP
sqlsql-server
Sean T
Sean T

Reputation: 2504

SQL Server - Get cheapest supplier price

I have a simple ordering database with tables Order, Order_Item, Item, Supplier and Supplier_Item. An Item can have multiple suppliers which all list the item at different prices.

I'm trying to write a query that looks at the past 7 days orders, and their items and returns the cheapest supplier price for that order item.

I have tried this:

   SELECT O.Order_Id, O.Site_Id, O.Order_Date,
          OI.Item_ID, OI.Quantity, OI.Supplier,
          I.Item_Name, I.Category,
          MIN(SI.Price) AS Price , SI.Supplier_ID,
          S.Supplier_Name
   FROM  [Order_Item] OI
   INNER JOIN [Order] O ON O.Order_ID = OI.Order_ID
   INNER JOIN Item I ON OI.Item_ID = I.Item_ID
   INNER JOIN Supplier_Item SI ON SI.Item_ID = I.Item_ID
   INNER JOIN Supplier S ON S.Supplier_ID = SI.Supplier_ID
   WHERE O.Order_Date > DATEADD(DAY, - 7, GETDATE())
   GROUP BY O.Order_ID, O.Site_ID, O.Order_Date, OI.Item_ID, OI.Quantity, 
            OI.Supplier, I.Item_Name, I.Category, SI.Supplier_ID, 
            S.Supplier_Name

However the result is incorrect, it should return a row for each order item (about 100) but it returns a row for each order item AND supplier price (3000 rows).

If anyone can show me where I'm going wrong it would be a great help.

Upvotes: 1

Views: 190

Answers (2)

Ferdinand Gaspar
Ferdinand Gaspar

Reputation: 2063

Try to determine the cheapest supplier price for each item, then connect it to order item

SELECT O.Order_Id,
       O.Site_Id, 
       O.Order_Date,
       OI.Item_ID, 
       OI.Quantity, 
       OI.Supplier,
       I.Item_Name, 
       I.Category,
       SI.Price,
       SI.Supplier_ID,
       SI.Supplier_Name
  FROM [Order_Item] OI
 INNER JOIN [Order] O 
    ON O.Order_ID = OI.Order_ID
 INNER JOIN Item I 
    ON OI.Item_ID = I.Item_ID
 INNER JOIN (SELECT si_a.item_id, 
                    s.supplier_id,
                    s.supplier_name,
                    si_a.price,
                    ROW_NUMBER() OVER (PARTITION BY si_a.item_id ORDER BY si_a.price) AS cheapest
               FROM supplier_item si_a
               JOIN supplier s
                 ON si_a.supplier_id = s.supplier_id
            ) si
    ON si.item_id = i.item_id 
   AND si.cheapest = 1
 WHERE O.Order_Date > DATEADD(DAY, - 7, GETDATE())

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270493

Whatever other issues you have, your joins look incorrect. Particularly:

INNER JOIN Supplier_Item SI ON SI.Supplier_ID = I.Item_ID

If I had to guess, this should be:

ON SI.Item_ID = I.Item_ID

Upvotes: 1

Related Questions