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.
| 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 PiecesPC/Lot/Qty - PC/Lot/Qty - PC/Lot/QtyI 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] |
 |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|
|
Joining multiple tables to find a QTYdifference