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

  • 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 🙂

  • Hi and welcome to the forums. In order to help we will need a few things:

    1. Sample DDL in the form of CREATE TABLE statements

    2. Sample data in the form of INSERT INTO statements

    3. Expected results based on the sample data

    Please take a few minutes and read the first article in my signature for best practices when posting questions.

    _______________________________________________________________

    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/

  • Thanks for getting back to me so quickly!

    Is there any quick/easy way to pull these out of SQL Management Stuido, or do I have to hand write these statements for you?

    Thanks!

  • kaminskidevelopment (7/12/2013)


    Thanks for getting back to me so quickly!

    Is there any quick/easy way to pull these out of SQL Management Stuido, or do I have to hand write these statements for you?

    Thanks!

    The create table stuff is pretty simple. Right the table(s), the select "script table as -> create to -> ...". For the data you will probably want to hand write it. This does NOT need to be a complete data dump. Just a few rows in each table that will represent your situation. Of course, make sure you obfuscate the data as we don't want to see anything that might be private type of information.

    You can use SSMS to script data for you but the setup is somewhat tedious and by the time you are done with that you could have just done it manually.

    _______________________________________________________________

    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/

  • CREATE TABLE [dbo].[ORDER](

    [ID] [int] IDENTITY(1,1) 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] IDENTITY(1,1) 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] IDENTITY(1,1) NOT NULL,

    [Client] [int] NULL,

    [PO] [nvarchar](255) NULL,

    [Date] [datetime] NULL,

    )

    CREATE TABLE [dbo].[ShippingArchive](

    [ID] [int] IDENTITY(1,1) 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,

    )

  • ORDER

    IDOrderRef1OrderDateShippedReceiptSent

    1525118111515912/01/201300

    1524118111515810/01/201300

    orderITEMS

    IDOutboundDeliveryIDOrderLineNrItemNumberBatchNrQtyToShip

    16631525901715655552Y637N20

    1662152420122000600NULL1

    shipping

    idclientpodate

    1776204000080014959 2013-07-11 00:00:00.000

    1776184000080014957 2013-07-11 00:00:00.000

    shippingArchive

    IDOrigIDClientProductCodeLotIDLotQtyStatusIDStatusDate

    4262720040071034600283835613EM1064311776182013-07-11 00:00:00.000

    4262722040071034600283835613EM1064311776202013-07-11 00:00:00.000

  • How do you know which order a shipment is for? I don't see anything that connects those together.

    _______________________________________________________________

    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/

  • What do you mean by expected results?

  • The naming conventions are poor.

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

  • kaminskidevelopment (7/12/2013)


    What do you mean by expected results?

    That means that based on your sample data what should the desired output be. Yours is reasonably easy enough to figure that out so don't waste any time putting that together.

    _______________________________________________________________

    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 (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 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/

  • 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.

  • Let me get some better IDs for you - I just used some top IDs

  • 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 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/

  • 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'

Viewing 15 posts - 1 through 15 (of 21 total)

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