April 19, 2012 at 2:12 pm
I have a query where it is duplicating data. Below is the sample code.
In the results for product 12345 the inventory for warehouse is displaying as 240 but it must be 120 as inventory for each product in warehouse remains same though sales will be in different city.
CREATE TABLE #Sample( Product varchar(10), City varchar(50), Warehouse int,Sales decimal(12,2), Inventory int)
INSERT INTO #Sample VALUES('12345','NewYork',1,'12.00','120')
INSERT INTO #Sample VALUES('12345','NewHaven',1,'8.00','120')
INSERT INTO #Sample VALUES('54321','NewYork',3,'15.00','10')
SELECT
Product,
SUM(CASE
WHEN Warehouse = 1 THEN Sales
ELSE 0
END) AS SalesofWarehouse1,
SUM(CASE
WHEN Warehouse = 3 THEN Sales
ELSE 0
END) AS SalesofWarehouse3,
SUM(CASE
WHEN Warehouse = 1 THEN Inventory
ELSE 0
END) AS InventoryofWarehouse1,
SUM(CASE
WHEN Warehouse = 3 THEN Inventory
ELSE 0
END) AS InventoryofWarehouse3
FROM #Sample
GROUP BY Product
DROP TABLE #Sample
I want results to be
Product SalesofWarehouse1 SalesofWarehouse3 InventoryofWarehouse1 InventoryofWarehouse3
12345 20.00 0.00 120 0
54321 0.00 15.00 0 10
April 19, 2012 at 2:32 pm
It's certainly not the most elegant solution but this will work:
SELECT
Product,
SUM(CASE
WHEN Warehouse = 1 THEN Sales
ELSE 0
END) AS SalesofWarehouse1,
SUM(CASE
WHEN Warehouse = 3 THEN Sales
ELSE 0
END) AS SalesofWarehouse3,
isnull((select top 1 Inventory from #Sample s2 where s1.Product = s2.Product and Warehouse = 1),0) AS InventoryofWarehouse1,
isnull((select top 1 Inventory from #Sample s2 where s1.Product = s2.Product and Warehouse = 3),0) AS InventoryofWarehouse3
FROM #Sample s1
GROUP BY Product
April 19, 2012 at 2:32 pm
Will the Inventory at each Warehouse ever be different? Could you have a record of:
INSERT INTO #Sample VALUES('12345','NewHaven',1,'8.00','20')
If so, how do those values impact the Inventory totals for the product? If not, instead of SUM for the Inventory values, just use MAX or MIN.
SELECT
Product,
SUM(CASE
WHEN Warehouse = 1 THEN Sales
ELSE 0
END) AS SalesofWarehouse1,
SUM(CASE
WHEN Warehouse = 3 THEN Sales
ELSE 0
END) AS SalesofWarehouse3,
MAX(CASE
WHEN Warehouse = 1 THEN Inventory
ELSE 0
END) AS InventoryofWarehouse1,
MAX(CASE
WHEN Warehouse = 3 THEN Inventory
ELSE 0
END) AS InventoryofWarehouse3
FROM #Sample
GROUP BY Product
April 19, 2012 at 2:32 pm
Hmm... So you don't want to sum that do you? why not place it as the second column and include that in the grouping.
SELECT
Product,CASE
WHEN Warehouse = 1 THEN Inventory
ELSE 0
END AS InventoryofWarehouse1,
CASE
WHEN Warehouse = 3 THEN Inventory
ELSE 0
END AS InventoryofWarehouse3,
SUM(CASE
WHEN Warehouse = 1 THEN Sales
ELSE 0
END) AS SalesofWarehouse1,
SUM(CASE
WHEN Warehouse = 3 THEN Sales
ELSE 0
END) AS SalesofWarehouse3
FROM #Sample
GROUP BY Product, CASE
WHEN Warehouse = 1 THEN Inventory
ELSE 0,
CASE
WHEN Warehouse = 3 THEN Inventory
ELSE 0
Or you could get those values first into a temp table or CTE and then join to it.
Jared
CE - Microsoft
April 19, 2012 at 2:50 pm
I need to use sum on inventory as I was grouping on UPC. To avoid confusion I did not use UPC field.
Now when you run the query below you will see for UPC 00112345 has inventory 320 but it must be 200 (120 + 80) only. UPC can be same for multiple products.
CREATE TABLE #Sample( Product varchar(10),UPC varchar(10), City varchar(50), Warehouse int,Sales decimal(12,2), Inventory int)
INSERT INTO #Sample VALUES('12345','00112345','NewYork',1,'12.00','120')
INSERT INTO #Sample VALUES('12345','00112345','NewHaven',1,'8.00','120')
INSERT INTO #Sample VALUES('23451','00112345','NewYork',1,'45.00','80')
INSERT INTO #Sample VALUES('54321','00256895','NewYork',3,'15.00','10')
SELECT
UPC,
SUM(CASE
WHEN Warehouse = 1 THEN Sales
ELSE 0
END) AS SalesofWarehouse1,
SUM(CASE
WHEN Warehouse = 3 THEN Sales
ELSE 0
END) AS SalesofWarehouse3,
SUM(CASE
WHEN Warehouse = 1 THEN Inventory
ELSE 0
END) AS InventoryofWarehouse1,
SUM(CASE
WHEN Warehouse = 3 THEN Inventory
ELSE 0
END) AS InventoryofWarehouse3
FROM #Sample
GROUP BY UPC
DROP TABLE #Sample
April 19, 2012 at 2:54 pm
Shree-903371 (4/19/2012)
I need to use sum on inventory as I was grouping on UPC. To avoid confusion I did not use UPC field.Now when you run the query below you will see for UPC 00112345 has inventory 320 but it must be 200 (120 + 80) only. UPC can be same for multiple products.
CREATE TABLE #Sample( Product varchar(10),UPC varchar(10), City varchar(50), Warehouse int,Sales decimal(12,2), Inventory int)
INSERT INTO #Sample VALUES('12345','00112345','NewYork',1,'12.00','120')
INSERT INTO #Sample VALUES('12345','00112345','NewHaven',1,'8.00','120')
INSERT INTO #Sample VALUES('23451','00112345','NewYork',1,'45.00','80')
INSERT INTO #Sample VALUES('54321','00256895','NewYork',3,'15.00','10')
SELECT
UPC,
SUM(CASE
WHEN Warehouse = 1 THEN Sales
ELSE 0
END) AS SalesofWarehouse1,
SUM(CASE
WHEN Warehouse = 3 THEN Sales
ELSE 0
END) AS SalesofWarehouse3,
SUM(CASE
WHEN Warehouse = 1 THEN Inventory
ELSE 0
END) AS InventoryofWarehouse1,
SUM(CASE
WHEN Warehouse = 3 THEN Inventory
ELSE 0
END) AS InventoryofWarehouse3
FROM #Sample
GROUP BY UPC
DROP TABLE #Sample
I'm sorry, to me this makes not sense statistically. I am looking at a product and its total sales and multiple products inventories in the same warehouse?
Jared
CE - Microsoft
April 19, 2012 at 2:57 pm
Sorry for the confusion. Thats why i did not add UPC before.
UPC is universal product code. Few products may have same UPC. This is a business thing.
So if they want to see by product then what you all suggested works.
But as they want to see by UPC, sales and inventory has to be summed.
April 19, 2012 at 2:59 pm
Ok, so change what I did to UPC instead of product.
Jared
CE - Microsoft
April 19, 2012 at 3:00 pm
This does require a bit of a redesign, but why not keep the warehouse inventory levels in a different table? It probably shouldn't be in the same table as sales info by city but rather be in it's own table by product/warehouse.
April 19, 2012 at 3:03 pm
cfradenburg (4/19/2012)
This does require a bit of a redesign, but why not keep the warehouse inventory levels in a different table? It probably shouldn't be in the same table as sales info by city but rather be in it's own table by product/warehouse.
+1 The biggest problem here is design, and just flat out bad data in that you are repeating a value that should not be repeated.
Jared
CE - Microsoft
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply