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