SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Joining multiple tables to find a QTYdifference
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dkaminski
Starting Member

USA
1 Posts

Posted - 07/12/2013 :  10:30:14  Show Profile  Reply with Quote
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

547 Posts

Posted - 07/12/2013 :  11:33:47  Show Profile  Reply with Quote
Something like this:



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;
			  
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/12/2013 :  11:45:35  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000