Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

How to join multiple tables (SQL) to find the difference between an order and shipped quantities? Expand / Collapse
Author
Message
Posted Friday, July 12, 2013 9:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 13,226, Visits: 12,703
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 Moden's 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)
Post #1473121
Posted Friday, July 12, 2013 9:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 7:41 AM
Points: 13, 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.
Post #1473124
Posted Friday, July 12, 2013 9:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 7:41 AM
Points: 13, Visits: 8
Let me get some better IDs for you - I just used some top IDs
Post #1473125
Posted Friday, July 12, 2013 9:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 13,226, Visits: 12,703
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 [].


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 Moden's 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)
Post #1473126
Posted Friday, July 12, 2013 10:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 7:41 AM
Points: 13, 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'

Post #1473127
Posted Friday, July 12, 2013 10:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 7:41 AM
Points: 13, Visits: 8
Yours is much more consumable - is there a reference for the shortcodes? Please forgive my haphazard posting, I sincerely appreciate your help.
Post #1473130
Posted Friday, July 12, 2013 10:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 13,226, Visits: 12,703
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 Moden's 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)
Post #1473132
Posted Friday, July 12, 2013 10:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 7:41 AM
Points: 13, Visits: 8
lol @ my fail of replacing the tablename with "Order" I can see how that would cause some problems
Post #1473135
Posted Friday, July 12, 2013 10:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 13,226, Visits: 12,703
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 Moden's 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)
Post #1473141
Posted Friday, July 12, 2013 10:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 7:41 AM
Points: 13, Visits: 8
Thanks Sean! Deeply appreciated.

I think that's right now, I followed your lead.
Post #1473145
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse