July 12, 2013 at 8:49 am
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 🙂
July 12, 2013 at 8:59 am
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/
July 12, 2013 at 9:02 am
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!
July 12, 2013 at 9:10 am
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/
July 12, 2013 at 9:23 am
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,
)
July 12, 2013 at 9:31 am
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
July 12, 2013 at 9:36 am
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/
July 12, 2013 at 9:40 am
What do you mean by expected results?
July 12, 2013 at 9:40 am
The naming conventions are poor.
The order connects to the shipment via the PO - PO in shipping is the OrderRef1 in the ORDER table
July 12, 2013 at 9:47 am
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/
July 12, 2013 at 9:49 am
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/
July 12, 2013 at 9:56 am
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.
July 12, 2013 at 9:56 am
Let me get some better IDs for you - I just used some top IDs
July 12, 2013 at 9:59 am
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/
July 12, 2013 at 10:01 am
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