How to join multiple tables (SQL) to find the difference between an order and shipped quantities?

Question

How to join multiple tables (SQL) to find the difference between an order and shipped quantities?

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

kaminskidevelopment Mr or Mrs. 500 Points: 517 More actions · Answer 1

Yours is much more consumable - is there a reference for the shortcodes? Please forgive my haphazard posting, I sincerely appreciate your help.

Sean Lange SSC Guru Points: 286573 More actions · Answer 2

kaminskidevelopment (7/12/2013)
Yours is much more consumable - is there a reference for the shortcodes? Please forgive my haphazard posting, I sincerely appreciate your help.

Not sure what you mean by a reference to shortcodes?

You can use the IFCode Shortcuts when posting to put your code in those nice little windows. They are on the left when posting.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

kaminskidevelopment Mr or Mrs. 500 Points: 517 More actions · Answer 3

lol @ my fail of replacing the tablename with "Order" I can see how that would cause some problems :blush:

Sean Lange SSC Guru Points: 286573 More actions · Answer 4

If you can turn your last posting of data into inserts I will be happy to help you figure out your query. 🙂

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

kaminskidevelopment Mr or Mrs. 500 Points: 517 More actions · Answer 5

Thanks Sean! Deeply appreciated.

I think that's right now, I followed your lead.

Sean Lange SSC Guru Points: 286573 More actions · Answer 6

I am not sure exactly what you are looking for but I think this is close based on your original post.

select o.ID as OrderID, oi.ItemNumber as ItemOrdered, oi.QtyToShip as QuantityOrdered,

sa.ProductCode as ShippedItem, sa.Qty as QuantityShipped, oi.QtyToShip - ISNULL(sa.Qty, 0) as AmountMissing

from myorder o

join orderITEMS oi on oi.OutboundDeliveryID = o.ID

left join Shipping s on s.PO = o.OrderRef1

left join ShippingArchive sa on sa.StatusID = s.ID

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

kaminskidevelopment Mr or Mrs. 500 Points: 517 More actions · Answer 7

Thank you so much! This is an awesome basis on which to finish my query. I have to add a lot of filtering and additional logic in.

Deepest appreciation!

I need to find someone that I can pay to consult on my SQL queries, it would make my effectiveness go through the roof. Any suggestions as to how or where to find a consultant that I could call and discuss my query issues with on an as needed basis?