Sql-server – How to join multiple tables (SQL) to find the difference between an order and shipped quantities

sql serversql-server-2005

I have a collection of orders that were submitted to an internal ERP, which were processed and partially shipped.

The table hierarchy is as follows:

  • Order table contains OrderReference(PO), OrderID
  • OrderItems table contains Product/Lot/Qty requested (OrderID is parentID that joins these tables)
  • Shipping contains OrderReference(PO), ShippingID
  • ShippingArchive contains Product/Lot/Qty that was shipped (ShippingID is parent that joins these tables)

I want to create a report that looks as follows:

OrderReference#

Order Requested / Shipped Items / Missing Pieces

PC/Lot/Qty    -   PC/Lot/Qty    - PC/Lot/Qty

I have used a temp table to get some of the data but I realize I'm taking the wrong approach. I should be able to do all of this with a single query, though it will seemingly be a bit complex.

I get held up when I try to think about how to join where pieces don't exist, and where to perform the mathematics in my sorting and filtering. Please help!

Sincere thanks 🙂

Best Answer

I think this is what you want. I had to make a few assumptions though. First that a given item is identified by product and lot. So I added a join between ShippingArchive and OrderItems based on these columns. Next that in your report you only needed to see the product and lot information once and needed the requested, shipped and missing information for qty only. I also made the assumption that missing meant requested-shipped.

Edited to handling multiple shipping for the same order.

WITH Shipped AS (SELECT Shipping.OrderReference, ShippingArchive.Product, ShippingArchive.lot, 
                        SUM(ShippingArchive.qty) AS Qty
                FROM Shipping
                JOIN ShippingArchive
                    ON Shipping.ShippingId = ShippingArchive.ShippingId
                GROUP BY Shipping.OrderReference, ShippingArchive.Product, ShippingArchive.lot
                )
SELECT  [Order].OrderReference, OrderItems.Product AS Requested_Product, OrderItems.lot AS Requested_Lot, OrderItems.qty AS Requested_Qty, 
    ISNULL(Shipped.qty,0) AS Shipped_Qty, OrderItems.qty - ISNULL(Shipped.qty,0) AS Missing_Qty
FROM [Order]
JOIN OrderItems
    ON [Order].OrderId = OrderItems.OrderId
LEFT OUTER JOIN Shipped
    ON [Order].OrderReference = Shipped.OrderReference
    AND OrderItems.Product = Shipped.Product
    AND OrderItems.Lot = Shipped.Lot
Related Question