SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26716 Visits: 17557
kaminskidevelopment (7/12/2013)
The naming conventions are poor.

The order connects to the shipment via the PO - PO in shipping is the OrderRef1 in the ORDER table


So in your sample data neither of the orders have been shipped? It would probably be a better dataset to have one that has been shipped and one that hasn't.

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
kaminskidevelopment
kaminskidevelopment
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 8
Thanks for all the help! I feel like I could write this query with just a push in the right direction, but for some reason I'm hitting a brick wall pretty early on.

I figure I need to:

Get the quantity of each product from the OrderItems per order
Get the quantity shipped for each correlating PO in the shipping table
subtract the two quantities and display the result, per item.

I was doing so with temp tables - inserting all orders, removing all orderIDs from the temp table that had a full match on qty ordered + qty shipped, and trying to report on the order status of the remaining records. That's not helping my SQL knowledge grow in any way, though.
kaminskidevelopment
kaminskidevelopment
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 8
Let me get some better IDs for you - I just used some top IDs
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26716 Visits: 17557
kaminskidevelopment (7/12/2013)
Let me get some better IDs for you - I just used some top IDs


I put together an example of how you should post this stuff. It needs to be readily consumable so we can just hit f5 and start on the issue.

I changed your Order table to MyOrder so I don't have to continuously wrap it with []. w00t



CREATE TABLE [dbo].[MyORDER](
[ID] [int] NOT NULL,
[OrderRef1] [varchar](10) NULL,
[OrderRef2] [varchar](10) NULL,
[OrderDate] [varchar](10) NULL,
[Shipped] [bit] NOT NULL,
[ReceiptSent] [bit] NOT NULL,
)

CREATE TABLE [dbo].[orderITEMS](
[ID] [int] NOT NULL,
[OutboundDeliveryID] [int] NOT NULL,
[OrderLineNr] [int] NULL,
[ItemNumber] [varchar](18) NULL,
[BatchNr] [varchar](10) NULL,
[QtyToShip] [varchar](13) NULL,
)

CREATE TABLE [dbo].[Shipping](
[ID] [int] NOT NULL,
[Client] [int] NULL,
[PO] [nvarchar](255) NULL,
[Date] [datetime] NULL,
)


CREATE TABLE [dbo].[ShippingArchive](
[ID] [int] NOT NULL,
[OrigID] [int] NULL,
[Client] [int] NULL,
[ProductCode] [nvarchar](25) NULL,
[LotID] [int] NULL,
[Lot] [nvarchar](25) NULL,
[Qty] [int] NULL,
[StatusID] [int] NULL,
[StatusDate] [datetime] NULL,
)

insert myORDER
select 1525, 1181115159, null, '12/01/2013', 0, 0 union all
select 1524, 1181115158, null, '10/01/2013', 0, 0

insert orderITEMS
select 1663, 1525, 901, 71565555, '2Y637N', 20 union all
select 1662, 1524, 20, 122000600, NULL, 1

insert shipping
select 177620, 400, '0080014959', '2013-07-11 00:00:00.000' union all
select 177618, 400, '0080014957', '2013-07-11 00:00:00.000'

insert shippingArchive
select 4262720, 0, 400, 71034600, 2838356, '13EM10643', 1, 177618, '2013-07-11 00:00:00.000' union all
select 4262722, 0, 400, 71034600, 2838356, '13EM10643', 1, 177620, '2013-07-11 00:00:00.000'


select * from myorder
select * from orderITEMS
select * from Shipping
select * from ShippingArchive



Notice how the sample data is inserts, that is what I mean by consumable.

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
kaminskidevelopment
kaminskidevelopment
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 8


insert myOrder
select 1511,'0080014959',NULL,'11/07/2013',1,1 union all
select 1510,'0080014957',NULL,'11/07/2013',1,1

insert orderITEMS
select 1646,1511,900001,'71034600','13EM10643',1 union all
select 1645,1510,10,'71034600','13EM10643',1

insert shipping
select 177620,400,'0080014959','2013-07-11 00:00:00.000' union all
select 177618,400,'0080014957','2013-07-11 00:00:00.000'

insert shippingArchive
select 4262720,0,400,'71034600',2838356,'13EM10643',1,177618,'2013-07-11 00:00:00.000' union all
select 4262722,0,400,'71034600',2838356,'13EM10643',1,177620,'2013-07-11 00:00:00.000'


kaminskidevelopment
kaminskidevelopment
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 8
Yours is much more consumable - is there a reference for the shortcodes? Please forgive my haphazard posting, I sincerely appreciate your help.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26716 Visits: 17557
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
kaminskidevelopment
kaminskidevelopment
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 8
lol @ my fail of replacing the tablename with "Order" I can see how that would cause some problems Blush
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26716 Visits: 17557
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
kaminskidevelopment
kaminskidevelopment
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 8
Thanks Sean! Deeply appreciated.

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search