I have quite a complex view that is bringing back duplicate lines. I believe it is because the Tally field as more than one size but could be wrong.
How do I get the SQL code correct so it only shows one unique line. In the example below it is showing 2 rows based on a code given, it should only show one
I have tried to change some of the Joins but cannot find out why it is duplicating. It looks like it is counting each tally as a line so where we have 29/3.3 and 61/3.6 it is counting this as 2. Where tallys are more than one that is where it appears to be repeating the rows (but could be wrong). Code is given below, any ideas please, many thanks
CODE --> sql
SELECT dbo.[148-PackStatus].Status, PP.DateReceived, PP.PackRef, PP.QuantityTally AS Tally, PP.TotalVolume, P.AverageCostPriceWithAdditional AS AverageCost, dbo.Per.PerCode, PP.TotalCost + PP.AdditionalCost AS Totcost, P.ProductCode, P.Description, P.AverageCostPriceWithAdditional * PP.TotalVolume AS PackValue, PD.Length, PD.Quantity, PD.Thickness, PD.Width, PP.PackID, PD.Length * PD.Quantity * PD.Thickness * PD.Width / 1000 / 1000 AS LengthVolume, PD.Length * PD.Quantity * PD.Thickness * PD.Width / 1000 / 1000 * P.AverageCostPriceWithAdditional AS LengthValue, PG.Name AS ProductGroupName, P.ProductID, B.Name AS BranchName, P.ProductGroupID, PP.BranchID, PP.BOLRef, PP.Voyage, PP.ShippingRef, PP.TotalVolume AS PackVol, PP.PackStatus, WOL.WorksOrderID, ISNULL(WOL.PackConfirmed, 0) AS PackConfirmedYN, PP.TotalCost, PP.AdditionalCost FROM dbo.PerRule INNER JOIN dbo.Product AS P INNER JOIN dbo.ProductGroup AS PG ON PG.ProductGroupID = P.ProductGroupID LEFT OUTER JOIN dbo.ProductPack AS PP ON PP.ProductID = P.ProductID LEFT OUTER JOIN dbo.ProductPackDetail AS PD ON PD.PackID = PP.PackID INNER JOIN dbo.Branch AS B ON B.BranchID = PP.BranchID INNER JOIN dbo.[148-PackStatus] ON PP.PackStatus = dbo.[148-PackStatus].PackStatus ON dbo.PerRule.PerRuleID = P.PerRuleID INNER JOIN dbo.Per ON dbo.PerRule.BasePerID = dbo.Per.PerID LEFT OUTER JOIN dbo.WorksOrderLine AS WOL ON PP.ProductID = WOL.ProductID AND PP.PackID = WOL.PackID WHERE (P.SpecialSourceID IS NULL) AND (PP.Deleted = 0) AND (PP.PackStatus IN (5, 10, 11, 12, 14)) AND (WOL.PackConfirmed IS NULL OR WOL.PackConfirmed = 0) AND (PP.PackRef = '9184475') ORDER BY PP.BranchID, P.ProductGroupID, P.ProductCode, PD.Length