September 6, 2016 at 10:49 pm
I am trying to set up a query that will report on quantities ordered, shipped and backordered (those that didn't ship).
The issue is that I am ordering from one location and may be able to ship from that location, sometimes from another location and sometime the original location and another.
The part that is difficult is when I order from an original location and ship part of the order from another. Even though I shipped only from one location I need to show both locations.
If I have the following schema and data:
IF OBJECT_ID('tempdb..#Order') IS NOT NULL
DROP TABLE #Order
IF OBJECT_ID('tempdb..#Product') IS NOT NULL
DROP TABLE #Product
CREATE TABLE #Order
(
OrderNumber VARCHAR(10) ,
Location VARCHAR(20) ,
Total INT
)
CREATE TABLE #Product
(
OrderNumber VARCHAR(10) ,
Product VARCHAR(20) ,
Location VARCHAR(20) ,
Quantity INT
)
INSERT #Order
VALUES ( '001', 'Dallas', 10 )
INSERT #Product
VALUES ( '001', 'WidgetA', 'Dallas', 3 )
INSERT #Order
VALUES ( '002', 'Dallas', 20 )
INSERT #Product
VALUES ( '002', 'WidgetB', 'Hampton', 8 )
INSERT #Order
VALUES ( '003', 'Dallas', 15 )
INSERT #Product
VALUES ( '003', 'WidgetC', 'Dallas', 3 )
INSERT #Product
VALUES ( '003', 'WidgetC', 'Hampton', 7 )
I want to end up with the following result:
Order#ProductLocationQty OrderedQty ShippedQty Backordered
001 WidgetADallas10 3 7
002 WidgetBDallas20 0 12
002 WidgetBHampton8 8 0
003 WidgetCDallas15 3 5
003 WidgetCHampton7 7 0
So for 001, we have ordered for Dallas and shipped 3 from Dallas, so they all show on one line.
For 002, we have ordered for Dallas, but shipped from Hampton, so we have 2 lines (one for each location).
For 003, we have ordered 15 for Dallas and shipped 3 from Dallas and 7 from Hampton. So we again have two rows.
How would I set up the query to handle this.
The problem is that joins don't seem to work by themselves
Thanks,
Tom
September 7, 2016 at 2:59 am
The question is, what are your primary keys and your foreign keys. From the layout it could be either order number or order number and location or just location. Your data seems to support stuff being added to either side, order or product and then you want a combined result. That's not possible without some more clarity. You could write this query a number of different ways:
SELECT *
FROM #Product AS p
LEFT JOIN #Order AS o
ON p.Location = o.Location
AND o.OrderNumber = p.OrderNumber;
SELECT *
FROM #Order AS o
LEFT JOIN #Product AS p
ON p.Location = o.Location
AND p.OrderNumber = o.OrderNumber;
SELECT *
FROM #Order AS o
LEFT JOIN #Product AS p
ON p.OrderNumber = o.OrderNumber;
SELECT *
FROM #Product AS p
LEFT JOIN #Order AS o
ON o.OrderNumber = p.OrderNumber;
SELECT *
FROM #Order AS o
LEFT JOIN #Product AS p
ON p.Location = o.Location;
SELECT *
FROM #Product AS p
LEFT JOIN #Order AS o
ON o.Location = p.Location;
None of them is returning the data set you want, although the closest is the third:
OrderNumber Product Location Quantity OrderNumber Location Total
----------- -------------------- -------------------- ----------- ----------- -------------------- -----------
001 WidgetA Dallas 3 001 Dallas 10
002 WidgetB Hampton 8 NULL NULL NULL
003 WidgetC Dallas 3 003 Dallas 15
003 WidgetC Hampton 7 NULL NULL NULL
(4 row(s) affected)
OrderNumber Location Total OrderNumber Product Location Quantity
----------- -------------------- ----------- ----------- -------------------- -------------------- -----------
001 Dallas 10 001 WidgetA Dallas 3
002 Dallas 20 NULL NULL NULL NULL
003 Dallas 15 003 WidgetC Dallas 3
(3 row(s) affected)
OrderNumber Location Total OrderNumber Product Location Quantity
----------- -------------------- ----------- ----------- -------------------- -------------------- -----------
001 Dallas 10 001 WidgetA Dallas 3
002 Dallas 20 002 WidgetB Hampton 8
003 Dallas 15 003 WidgetC Dallas 3
003 Dallas 15 003 WidgetC Hampton 7
(4 row(s) affected)
OrderNumber Product Location Quantity OrderNumber Location Total
----------- -------------------- -------------------- ----------- ----------- -------------------- -----------
001 WidgetA Dallas 3 001 Dallas 10
002 WidgetB Hampton 8 002 Dallas 20
003 WidgetC Dallas 3 003 Dallas 15
003 WidgetC Hampton 7 003 Dallas 15
(4 row(s) affected)
OrderNumber Location Total OrderNumber Product Location Quantity
----------- -------------------- ----------- ----------- -------------------- -------------------- -----------
001 Dallas 10 001 WidgetA Dallas 3
001 Dallas 10 003 WidgetC Dallas 3
002 Dallas 20 001 WidgetA Dallas 3
002 Dallas 20 003 WidgetC Dallas 3
003 Dallas 15 001 WidgetA Dallas 3
003 Dallas 15 003 WidgetC Dallas 3
(6 row(s) affected)
OrderNumber Product Location Quantity OrderNumber Location Total
----------- -------------------- -------------------- ----------- ----------- -------------------- -----------
001 WidgetA Dallas 3 001 Dallas 10
001 WidgetA Dallas 3 002 Dallas 20
001 WidgetA Dallas 3 003 Dallas 15
002 WidgetB Hampton 8 NULL NULL NULL
003 WidgetC Dallas 3 001 Dallas 10
003 WidgetC Dallas 3 002 Dallas 20
003 WidgetC Dallas 3 003 Dallas 15
003 WidgetC Hampton 7 NULL NULL NULL
(8 row(s) affected)
The problem isn't a join, the problem is that the data doesn't support returning two values for the 002 OrderNumber as you have it currently defined.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 7, 2016 at 7:52 am
This is the closes I could get to your expected results. I have to agree with Grant, the design is incorrect.
WITH CTE AS(
SELECT OrderNumber, Location, Total AS Quantity, x.Product, 'Ordered' AS type
FROM #Order o
CROSS APPLY ( SELECT TOP 1 Product FROM #Product p WHERE o.OrderNumber = p.OrderNumber) x
UNION ALL
SELECT OrderNumber, Location, Quantity, Product, 'Shipped' AS type
FROM #Product
)
SELECT OrderNumber,
Product,
Location,
ISNULL( SUM( CASE WHEN type = 'Ordered' THEN Quantity END), SUM(Quantity)),
SUM( CASE WHEN type = 'Shipped' THEN Quantity ELSE 0 END),
ISNULL( SUM( CASE WHEN type = 'Ordered' THEN Quantity END), SUM(Quantity))
- SUM( CASE WHEN type = 'Shipped' THEN Quantity ELSE 0 END)
FROM CTE
GROUP BY OrderNumber,
Product,
Location;
September 7, 2016 at 8:02 am
Nice work Luis.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 7, 2016 at 10:33 am
That looks pretty much what I am looking for.
As has been said the design is incorrect. The actual tables are correct. I was just trying to get a simple representation of what I was looking for.
I could have mentioned that the OrderNumber is the primary key. And I assumed that the FK would be able to be discernable.
In our tables, we were getting single rows when there should have been two (when the location was different in the products from the order).
You're solution was what I was looking for.
Thanks,
Tom
September 8, 2016 at 2:46 am
To get the best help, making it so we don't have to guess at stuff sure would help. And no, since there are multiple matching columns between the tables, the keys are not at all clear.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply