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)
 Joining multiple tables to find a QTYdifference

Author  Topic 

dkaminski
Starting Member

1 Post

Posted - 2013-07-12 : 10:30:14
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 :)

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-12 : 11:33:47
Something like this:
[CODE]


SELECT O.PO as PO,
O.OrderID as OrderID, I.Product as ORProduct, I.Lot as ORLot, I.Qty as ORQty, -- Order Requests
A.ShippingID as ShippingID, A.Product as SIProduct, A.Lot as SILot, A.Qty as SIQty, -- Shipped Items
I.Product as MPProduct, I.Lot as MPLot, I.Qty - COALESCE(A.Qty, 0) as MPQty -- Missing Pieces
from OrdersTable O LEFT JOIN ShippingTable S ON O.PO = S.PO LEFT JOIN ItemsTable I ON O.OrderID = I.OrderID
LEFT JOIN ArchiveTable A ON S.ShippingID = A.ShippingID and I.Product = A.Product and I.Lot = A.Lot
WHERE (I.Qty - COALESCE(A.Qty, 0)) > 0;

[/CODE]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-12 : 11:45:35
the format you want can be very easily obtained in tools like SQL Reporting Services with grouping applied on reference no.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -