Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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

549 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
52326 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  
 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.07 seconds. Powered By: Snitz Forums 2000