Hi
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
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
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