Duplicate Rows where DISTINCT is not working

Question

I have tried several different ways to eliminate duplicates. I have submitted what my query results are and also showed what the end results should look like.

WITH CTE AS (
SELECT DISTINCT
    a.SiteID
    ,a.SiteDescription
    ,iv.VendorNumber
    ,iv.VendorName
    ,mrh.DateReceived
    ,mrh.DocumentNumber
    ,mrh.InvoiceDate
    ,mrh.InvoiceNumber
    ,i.StockNumber
    ,i.StockDescription
    ,ia.UnitsPerPack
    ,mrh.ReceivingComment
    ,ia.BrokenQuantityAdjustment
    ,ia.BasePackCost
    ,mri.BrokenQuantityAdjusted
    ,ia.fee
FROM AdmSites a, InvVendors iv, InvItems i, InvManualReceivingHeader mrh, InvAdjustments ia, InvManualReceivingItems mri, InvManualReceivingItemsAdjustments ria
WHERE a.AdmSiteID = mrh.AdmSiteId
AND a.AdmSiteID = ia.AdmSiteID
AND i.InvItemID = ia.InvItemID
AND iv.InvVendorID = ia.InvVendorID
AND mrh.DateReceived = ia.AdjustmentDate
AND ria.DateReceived = mrh.DateReceived
AND ia.InvoiceNumber = mrh.InvoiceNumber
AND mrh.InvoiceNumber = 'deleted'
AND ia.InvoiceDate BETWEEN '2017-05-01' AND '2018-08-22'
AND (ia.InvoiceNumber LIKE '%' + ia.InvoiceNumber + '%' OR ia.InvoiceNumber IS NULL)
)
SELECT  t1.*, t2.PackQty, t1.BasePackCost * t2.PackQty as TotalCost, t1.BasePackCost * t2.PackQty as TotalValue
FROM CTE t1 INNER JOIN
(SELECT  SUM(BrokenQuantityAdjustment)PackQty, BasePackCost
FROM CTE
GROUP BY BasePackCost
) t2 on t1.BasePackCost = t2.BasePackCost
ORDER BY  StockNumber, BasePackCost

My Results from the query list above:

  SiteID   SiteDescription   VendorNumber   VendorName   DateReceived   DocumentNumber   InvoiceDate   InvoiceNumber   StockNumber   StockDescription            UnitsPerPack   ReceivingComment   BrokenQuantityAdjustment   BasePackCost   BrokenQuantityAdjusted   fee   PackQty   TotalCost   TotalValue  
 -------- ----------------- -------------- ------------ -------------- ---------------- ------------- --------------- ------------- --------------------------- -------------- ------------------ -------------------------- -------------- ------------------------ ----- --------- ----------- ------------ 
  69       Madison High      4              DAIRY        05/03/18       939              06/04/18      deleted         1451          MILK WHITE 1%               1                                 -161                       0.1615         0                        0     89        14.3735     14.3735     
  69       Madison High      4              DAIRY        05/03/18       939              06/04/18      deleted         1451          MILK WHITE 1%               1                                 250                        0.1615         0                        0     89        14.3735     14.3735     
  69       Madison High      4              DAIRY        05/03/18       939              06/04/18      deleted         1451          MILK WHITE 1%               1                                 -89                        0.1617         0                        0     -89       -14.3913    -14.3913    
  69       Madison High      4              DAIRY        05/03/18       939              06/04/18      deleted         1580          MILK LACTAID FREE SKIM PF   1                                 -20                        0.6185         0                        0     0         0           0           
  69       Madison High      4              DAIRY        05/03/18       939              06/04/18      deleted         1580          MILK LACTAID FREE SKIM PF   1                                 20                         0.6185         0                        0     0         0           0   

Expected Results if I can get this query to work:

 SiteID   SiteDescription   VendorNumber   VendorName   DateReceived   DocumentNumber   InvoiceDate   InvoiceNumber   StockNumber   StockDescription            UnitsPerPack   ReceivingComment   BrokenQuantityAdjustment   BasePackCost   BrokenQuantityAdjusted   fee   PackQty   TotalCost   TotalValue  
 -------- ---------------- -------------- ------------ -------------- ---------------- ------------- --------------- ------------- --------------------------- -------------- ------------------ -------------------------- -------------- ------------------------ ----- --------- ----------- ------------ 
  69       Madison High      4              DAIRY        05/03/18       939              06/04/18      deleted         1451          MILK WHITE 1%               1                                 -161                       0.1615         0                        0     89        14.3735     14.3735     
  69       Madison High      4              DAIRY        05/03/18       939              06/04/18      deleted         1451          MILK WHITE 1%               1                                 -89                        0.1617         0                        0     -89       -14.3913    -14.3913    
  69       Madison High      4              DAIRY        05/03/18       939              06/04/18      deleted         1580          MILK LACTAID FREE SKIM PF   1                                 -20                        0.6185         0                        0     0         0           0      

Here is code to create table and insert data if this will help.

CREATE TABLE mytable(
   SiteID                   INTEGER  NOT NULL PRIMARY KEY 
  ,SiteDescription          VARCHAR(12) NOT NULL
  ,VendorNumber             INTEGER  NOT NULL
  ,VendorName               VARCHAR(5) NOT NULL
  ,DateReceived             DATE  NOT NULL
  ,DocumentNumber           INTEGER  NOT NULL
  ,InvoiceDate              DATE  NOT NULL
  ,InvoiceNumber            VARCHAR(50) NOT NULL
  ,StockNumber              INTEGER  NOT NULL
  ,StockDescription         VARCHAR(25) NOT NULL
  ,UnitsPerPack             BIT  NOT NULL
  ,ReceivingComment         VARCHAR(30)
  ,BrokenQuantityAdjustment INTEGER  NOT NULL
  ,BasePackCost             NUMERIC(6,4) NOT NULL
  ,BrokenQuantityAdjusted   BIT  NOT NULL
  ,fee                      BIT  NOT NULL
  ,PackQty                  INTEGER  NOT NULL
  ,TotalCost                NUMERIC(8,4) NOT NULL
  ,TotalValue               NUMERIC(8,4) NOT NULL
);
INSERT INTO mytable(SiteID,SiteDescription,VendorNumber,VendorName,DateReceived,DocumentNumber,InvoiceDate,InvoiceNumber,StockNumber,StockDescription,UnitsPerPack,ReceivingComment,BrokenQuantityAdjustment,BasePackCost,BrokenQuantityAdjusted,fee,PackQty,TotalCost,TotalValue) VALUES (69,'Madison High',4,'DAIRY','5/3/2018',939,'6/4/2018','deleted',1451,'MILK WHITE 1%',1,NULL,-161,0.1615,0,0,89,14.3735,14.3735);
INSERT INTO mytable(SiteID,SiteDescription,VendorNumber,VendorName,DateReceived,DocumentNumber,InvoiceDate,InvoiceNumber,StockNumber,StockDescription,UnitsPerPack,ReceivingComment,BrokenQuantityAdjustment,BasePackCost,BrokenQuantityAdjusted,fee,PackQty,TotalCost,TotalValue) VALUES (69,'Madison High',4,'DAIRY','5/3/2018',939,'6/4/2018','deleted',1451,'MILK WHITE 1%',1,NULL,250,0.1615,0,0,89,14.3735,14.3735);
INSERT INTO mytable(SiteID,SiteDescription,VendorNumber,VendorName,DateReceived,DocumentNumber,InvoiceDate,InvoiceNumber,StockNumber,StockDescription,UnitsPerPack,ReceivingComment,BrokenQuantityAdjustment,BasePackCost,BrokenQuantityAdjusted,fee,PackQty,TotalCost,TotalValue) VALUES (69,'Madison High',4,'DAIRY','5/3/2018',939,'6/4/2018','deleted',1451,'MILK WHITE 1%',1,NULL,-89,0.1617,0,0,-89,-14.3913,-14.3913);
INSERT INTO mytable(SiteID,SiteDescription,VendorNumber,VendorName,DateReceived,DocumentNumber,InvoiceDate,InvoiceNumber,StockNumber,StockDescription,UnitsPerPack,ReceivingComment,BrokenQuantityAdjustment,BasePackCost,BrokenQuantityAdjusted,fee,PackQty,TotalCost,TotalValue) VALUES (69,'Madison High',4,'DAIRY','5/3/2018',939,'6/4/2018','deleted',1580,'MILK LACTAID FREE SKIM PF',1,NULL,-20,0.6185,0,0,0,0,0);
INSERT INTO mytable(SiteID,SiteDescription,VendorNumber,VendorName,DateReceived,DocumentNumber,InvoiceDate,InvoiceNumber,StockNumber,StockDescription,UnitsPerPack,ReceivingComment,BrokenQuantityAdjustment,BasePackCost,BrokenQuantityAdjusted,fee,PackQty,TotalCost,TotalValue) VALUES (69,'Madison High',4,'DAIRY','5/3/2018',939,'6/4/2018','deleted',1580,'MILK LACTAID FREE SKIM PF',1,NULL,20,0.6185,0,0,0,0,0);

Answer 1

I'm not writing it all out for your query, but this is how you approach excluding any record with a duplicate on "Field3"

SELECT TMP.* 
  FROM (SELECT Field1,
               Field2,
               Field3,
               COUNT(1) OVER
                 ( PARTITION BY Field3
                 ) AS cntOfField3
         FROM Some_Table
       ) AS TMP
 WHERE cntOfField3 = 1;

It looks like you actually want records with duplicates on this field, you just want one of them.

SELECT TMP.* 
  FROM (SELECT Field1,
               Field2,
               Field3,
               ROW_NUMBER() OVER
                 ( PARTITION BY Field3
                       ORDER BY 1 -- How do you want to select it?
                 ) AS cntOfField3
         FROM Some_Table
       ) AS TMP
 WHERE cntOfField3 = 1

Answer 2

You're getting duplicates since you are joining your CTE with another table:

SELECT  t1.*, t2.PackQty, t1.BasePackCost * t2.PackQty as TotalCost, t1.BasePackCost * t2.PackQty as TotalValue
FROM CTE t1 INNER JOIN
(SELECT  SUM(BrokenQuantityAdjustment)PackQty, BasePackCost
FROM CTE
GROUP BY BasePackCost
) t2 on t1.BasePackCost = t2.BasePackCost
ORDER BY  StockNumber, BasePackCost

Although the rows in the CTE are distinct, unless you can guarantee a 1x1 mapping to the inner join subquery, you'll get duplicates.