Duplicate Rows 1

[Cpreston]

Hi

I created view which duplicated several rows. I added a Select Distinct and this helped in the amount of duplicates, however for each WorksOrder Number I am still getting one duplicate line for each one. My results are currently 11,835 lines but it should be half that 58175. I have looked at the Joins and tried several ways but keep getting 11,835. Any ideas how I can get rid of the duplicate lines. Code is attached.

Thanks

SELECT DISTINCT 
                         WorksOrderHeader.WorksOrderNumber, WorksOrderHeader.DateTimeProcessed, WorksOrderHeader.TotalVolumeOut, WorksOrderHeader.TotalSourceCost, WorksOrderHeader.TotalProcessCost, 
                         WorksOrderHeader.TotalAddCost, WorksOrderHeader.TotalCostPrice, dbo.ProductGroup.Name, dbo.ProductGroup.ParentID, WorksOrderHeader.Description
FROM            dbo.WorksOrderHeader AS WorksOrderHeader INNER JOIN
                         dbo.WorksOrderLine ON WorksOrderHeader.WorksOrderID = dbo.WorksOrderLine.WorksOrderID INNER JOIN
                         dbo.Product ON dbo.WorksOrderLine.ProductID = dbo.Product.ProductID INNER JOIN
                         dbo.ProductGroup ON dbo.Product.ProductGroupID = dbo.ProductGroup.ProductGroupID
WHERE        (WorksOrderHeader.DateTimeProcessed >= CONVERT(DATETIME, '2018-04-01 00:00:00', 102)) AND (WorksOrderHeader.WorksOrderNumber <> 252012) AND 
(dbo.ProductGroup.ParentID = 893) AND (NOT (WorksOrderHeader.Description LIKE '%CUT%')) AND (NOT (WorksOrderHeader.Description LIKE '%Pick%')) 
AND (NOT (WorksOrderHeader.Description LIKE '%western%)'))
Order BY WorksOrderHeader.WorksOrderNumber

 

[Andrzejek]

The DISTINCT does eliminate duplicate records, but all fields have to be the same.
When I have this issue, I grab several records of data that should be just one record and examine them. Numbers are easy, but if you have text, there could be some spaces hiding in text that make records different. Or non-printable characters, they are always a pain to spot.
You have only 10 fields to check, so it should be easy to examine them.


---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

I understand what you mean , but it looks like it is duplicating the same row once only, I cannot see any differences in the rows.

Thanks

 

[Andrzejek]

Sometimes it is hard to 'see' where the issue is.
Another approach you may try is to eliminate one field at the time and see if/when the issue is gone..
I would start with [tt]WorksOrderHeader.Description[/tt], just a hunch....


---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

Hi

I took the dbo.ProductGroup.Name field out and then it gave the correct results. Many Thanks