Duplicate rows in SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Duplicate rows in SQL

Author

Topic

neilgilbert1234
Starting Member

9 Posts

Posted - 2008-01-09 : 06:51:59

Hi All,
I have a view that i use to pull data into excel, the problem im having is there are duplicate rows.
The basics of the view are i have stock items (no duplicates) and they can belong to more than one warehouse. I want to get one row that sums it all instead of a row for each stock item per warehouse.
I hope this makes sense as im tearing my hair out how to do it.
Heres the code:

SELECT SI.Code, SI.Name, SUM(WHI.QuantityOnPOPOrder) AS QuantityOnPOPOrder, SI.StandardCost, PG.Code AS PRODUCTCODE,
PSA.SupplierAccountNumber, SI.PartNumber, SUM(DISTINCT WHI.ConfirmedQtyInStock + WHI.UnconfirmedQtyInStock) AS OverallStockLevel,
SUM(DISTINCT WHI.QuantityAllocatedBOM + WHI.QuantityAllocatedSOP + WHI.QuantityAllocatedStock) AS QuantityAllocated,
SUM(SOL.LineQuantity - SOL.DespatchReceiptQuantity - SOL.AvailableForDespatch) AS ForwardOrderQty, SI.Barcode, SI.Manufacturer
FROM dbo.StockItem SI LEFT OUTER JOIN
dbo.StockItemSupplier SS ON SI.ItemID = SS.ItemID INNER JOIN
dbo.WarehouseItem WHI ON WHI.ItemID = SI.ItemID INNER JOIN
dbo.ProductGroup PG ON PG.ProductGroupID = SI.ProductGroupID LEFT OUTER JOIN
dbo.PLSupplierAccount PSA ON PSA.PLSupplierAccountID = SS.SupplierID LEFT OUTER JOIN
dbo.SOPOrderReturnLine SOL ON SOL.ItemCode = SI.Code
WHERE (SS.Preferred = 1) OR
(SS.Preferred IS NULL)
GROUP BY SI.Code, SI.Name, WHI.QuantityOnPOPOrder, SI.StandardCost, PG.Code, PSA.SupplierAccountNumber, SI.PartNumber, SI.Barcode, SI.Manufacturer

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2008-01-09 : 06:58:05

quote:
Originally posted by neilgilbert1234

Hi All,
I have a view that i use to pull data into excel, the problem im having is there are duplicate rows.
The basics of the view are i have stock items (no duplicates) and they can belong to more than one warehouse. I want to get one row that sums it all instead of a row for each stock item per warehouse.
I hope this makes sense as im tearing my hair out how to do it.
Heres the code:

SELECT SI.Code, SI.Name, SUM(WHI.QuantityOnPOPOrder) AS QuantityOnPOPOrder, SI.StandardCost, PG.Code AS PRODUCTCODE,
PSA.SupplierAccountNumber, SI.PartNumber, SUM(DISTINCT WHI.ConfirmedQtyInStock + WHI.UnconfirmedQtyInStock) AS OverallStockLevel,
SUM(DISTINCT WHI.QuantityAllocatedBOM + WHI.QuantityAllocatedSOP + WHI.QuantityAllocatedStock) AS QuantityAllocated,
SUM(SOL.LineQuantity - SOL.DespatchReceiptQuantity - SOL.AvailableForDespatch) AS ForwardOrderQty, SI.Barcode, SI.Manufacturer
FROM dbo.StockItem SI LEFT OUTER JOIN
dbo.StockItemSupplier SS ON SI.ItemID = SS.ItemID INNER JOIN
dbo.WarehouseItem WHI ON WHI.ItemID = SI.ItemID INNER JOIN
dbo.ProductGroup PG ON PG.ProductGroupID = SI.ProductGroupID LEFT OUTER JOIN
dbo.PLSupplierAccount PSA ON PSA.PLSupplierAccountID = SS.SupplierID LEFT OUTER JOIN
dbo.SOPOrderReturnLine SOL ON SOL.ItemCode = SI.Code
WHERE (SS.Preferred = 1) OR
(SS.Preferred IS NULL)
GROUP BY SI.Code, SI.Name, WHI.QuantityOnPOPOrder, SI.StandardCost, PG.Code, PSA.SupplierAccountNumber, SI.PartNumber, SI.Barcode, SI.Manufacturer

The duplication will more than likely be happening on one of the following columns:
WHI.QuantityOnPOPOrder, SI.StandardCost, PG.Code, PSA.SupplierAccountNumber, SI.PartNumber, SI.Barcode, SI.Manufacturer

You need to have a rule as to how you want them to be handled, maybe using a Min or Max or Average or aggregate - that should solve the issue.

Order your data in the spreadsheet by the stockitem code and then you'll probably pick up that there are different cost prices or suppliers etc of these products and that is why there are duplicate rows, remember that your data will only be unique at the level specified by the columns in your group by clause

Duane.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-09 : 07:00:49

quote:
Originally posted by neilgilbert1234

Hi All,
I have a view that i use to pull data into excel, the problem im having is there are duplicate rows.
The basics of the view are i have stock items (no duplicates) and they can belong to more than one warehouse. I want to get one row that sums it all instead of a row for each stock item per warehouse.
I hope this makes sense as im tearing my hair out how to do it.
Heres the code:

SELECT SI.Code, SI.Name, SUM(WHI.QuantityOnPOPOrder) AS QuantityOnPOPOrder, SI.StandardCost, PG.Code AS PRODUCTCODE,
PSA.SupplierAccountNumber, SI.PartNumber, SUM(DISTINCT WHI.ConfirmedQtyInStock + WHI.UnconfirmedQtyInStock) AS OverallStockLevel,
SUM(DISTINCT WHI.QuantityAllocatedBOM + WHI.QuantityAllocatedSOP + WHI.QuantityAllocatedStock) AS QuantityAllocated,
SUM(SOL.LineQuantity - SOL.DespatchReceiptQuantity - SOL.AvailableForDespatch) AS ForwardOrderQty, SI.Barcode, SI.Manufacturer
FROM dbo.StockItem SI LEFT OUTER JOIN
dbo.StockItemSupplier SS ON SI.ItemID = SS.ItemID INNER JOIN
dbo.WarehouseItem WHI ON WHI.ItemID = SI.ItemID INNER JOIN
dbo.ProductGroup PG ON PG.ProductGroupID = SI.ProductGroupID LEFT OUTER JOIN
dbo.PLSupplierAccount PSA ON PSA.PLSupplierAccountID = SS.SupplierID LEFT OUTER JOIN
dbo.SOPOrderReturnLine SOL ON SOL.ItemCode = SI.Code
WHERE (SS.Preferred = 1) OR
(SS.Preferred IS NULL)
GROUP BY SI.Code, SI.Name, WHI.QuantityOnPOPOrder, SI.StandardCost, PG.Code, PSA.SupplierAccountNumber, SI.PartNumber, SI.Barcode, SI.Manufacturer

I guess what you want is one row per item. then try like this:-

SELECT SI.Code, SI.Name, Temp.QuantityOnPOPOrder , SI.StandardCost, PG.Code AS PRODUCTCODE,
PSA.SupplierAccountNumber, SI.PartNumber, SUM(DISTINCT WHI.ConfirmedQtyInStock + WHI.UnconfirmedQtyInStock) AS OverallStockLevel,
SUM(DISTINCT WHI.QuantityAllocatedBOM + WHI.QuantityAllocatedSOP + WHI.QuantityAllocatedStock) AS QuantityAllocated,
SUM(SOL.LineQuantity - SOL.DespatchReceiptQuantity - SOL.AvailableForDespatch) AS ForwardOrderQty, SI.Barcode, SI.Manufacturer
FROM dbo.StockItem SI LEFT OUTER JOIN
dbo.StockItemSupplier SS ON SI.ItemID = SS.ItemID INNER JOIN
dbo.WarehouseItem WHI ON WHI.ItemID = SI.ItemID INNER JOIN
(Select ItemID,SUM(WHI.QuantityOnPOPOrder) AS 'QuantityOnPOPOrder'
FROM dbo.WarehouseItem
GROUP BY ItemID) Temp
ON Temp.ItemID=WHI.ItemID INNER JOIN
dbo.ProductGroup PG ON PG.ProductGroupID = SI.ProductGroupID LEFT OUTER JOIN
dbo.PLSupplierAccount PSA ON PSA.PLSupplierAccountID = SS.SupplierID LEFT OUTER JOIN
dbo.SOPOrderReturnLine SOL ON SOL.ItemCode = SI.Code
WHERE (SS.Preferred = 1) OR
(SS.Preferred IS NULL)

neilgilbert1234
Starting Member

9 Posts

Posted - 2008-01-09 : 07:15:24

Thanks both for the speedy response, ill give these ago this afternoon and let you know how i get on...

neilgilbert1234
Starting Member

9 Posts

Posted - 2008-01-09 : 10:02:59

Hi Guys,
It still seems to come out on two seperate rows i've copied the output below:
As you can see the OverallStockLevel is the only different figure, Do i need to the same for this field that was done to the QuantityOnPOPOrder?

Any further help would be great....

Code,Name,OverallStockLevel,QuantityAllocated,ForwardOrderQty,QuantityOnPOPOrder,StandardCost,PRODUCTCODE,Analysis Group,SupplierAccountNumber,Barcode,PartNumber,Date Next Order Due,Manufacturer
000998096,LOWER D/S HSG ASSY WITH DECAL,0,0,0,10,2.72,25,,H142,,,,
000998096,LOWER D/S HSG ASSY WITH DECAL,1,0,0,10,2.72,25,,H142,,,,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-09 : 11:04:07

Can you show me the dbo.WarehouseItem data for this Item?Also what does the value OverallStockLevel suggest?

neilgilbert1234
Starting Member

9 Posts

Posted - 2008-01-10 : 04:29:21

The OverallStockLevel is just simply the stock available at that particular warehouse, however i need all warehouse to sum the, together. The warehouse table looks like this:
WarehouseItemID,WarehouseID,ItemID,ReorderLevel,MinimumLevel,MaximumLevel,DateOfLastSale,ConfirmedQtyInStock,UnconfirmedQtyInStock,QuantityAllocatedSOP,QuantityAllocatedStock,QuantityOnPOPOrder,HoldingValueAtBuyPrice,DateOfLastStockCount,PreReceiptAllocationQty,QuantityAllocatedBOM,QuantityReserved,DateTimeCreated
382781,6416,382778,0,0,0,,0,0,0,0,0,0,,0,0,0,30/11/2007 13:52
382783,6420,382778,0,0,0,,1,0,0,0,10,2.72,,0,0,0,30/11/2007 13:52

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 04:42:25

quote:
Originally posted by neilgilbert1234

The OverallStockLevel is just simply the stock available at that particular warehouse, however i need all warehouse to sum the, together. The warehouse table looks like this:
WarehouseItemID,WarehouseID,ItemID,ReorderLevel,MinimumLevel,MaximumLevel,DateOfLastSale,ConfirmedQtyInStock,UnconfirmedQtyInStock,QuantityAllocatedSOP,QuantityAllocatedStock,QuantityOnPOPOrder,HoldingValueAtBuyPrice,DateOfLastStockCount,PreReceiptAllocationQty,QuantityAllocatedBOM,QuantityReserved,DateTimeCreated
382781,6416,382778,0,0,0,,0,0,0,0,0,0,,0,0,0,30/11/2007 13:52
382783,6420,382778,0,0,0,,1,0,0,0,10,2.72,,0,0,0,30/11/2007 13:52

Try doing like this:-

SELECT SI.Code, SI.Name, Temp.QuantityOnPOPOrder , SI.StandardCost, PG.Code AS PRODUCTCODE,
PSA.SupplierAccountNumber, SI.PartNumber, Temp.OverallStockLevel,
Temp.QuantityAllocated, SUM(SOL.LineQuantity - SOL.DespatchReceiptQuantity - SOL.AvailableForDespatch) AS ForwardOrderQty, SI.Barcode, SI.Manufacturer
FROM dbo.StockItem SI LEFT OUTER JOIN
dbo.StockItemSupplier SS ON SI.ItemID = SS.ItemID INNER JOIN
dbo.WarehouseItem WHI ON WHI.ItemID = SI.ItemID INNER JOIN
(Select ItemID,
SUM(WHI.QuantityOnPOPOrder) AS 'QuantityOnPOPOrder',
SUM(WHI.ConfirmedQtyInStock + WHI.UnconfirmedQtyInStock) AS OverallStockLevel,
SUM(WHI.QuantityAllocatedBOM + WHI.QuantityAllocatedSOP + WHI.QuantityAllocatedStock) AS QuantityAllocated
FROM dbo.WarehouseItem
GROUP BY ItemID) Temp
ON Temp.ItemID=WHI.ItemID INNER JOIN
dbo.ProductGroup PG ON PG.ProductGroupID = SI.ProductGroupID LEFT OUTER JOIN
dbo.PLSupplierAccount PSA ON PSA.PLSupplierAccountID = SS.SupplierID LEFT OUTER JOIN
dbo.SOPOrderReturnLine SOL ON SOL.ItemCode = SI.Code
WHERE (SS.Preferred = 1) OR
(SS.Preferred IS NULL)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources