SQL Server not equal query returning equal values


Keywords:sql 


Question: 

I have a query below which is comparing two related data sets looking for lines where the sum of qty from one set does not match the other. For reference one data set is PO based (MRP) and one is SO based (ARUN) but they are linked on the PO and SO numbers as well as Material, Sales Order Item, etc. The query runs fine but the results are showing equal values where what I am aiming for is to call out where they are NOT equal. I will add that the normal output of this without using SUMreturns double the rows as each data set has a these totals split over two lines (the shipments were split up so have different container numbers). My goal was to get rid of that by using the SUMfunction and exclude that unique field thus aggregating before. Any ideas what may be happening? I have included the code as well as the results with SUM and before. so in ARUN there can be a unique container number and likewise in MRP there can be a unique Schedule Line

Code:

SELECT 
       M.STOCK_NUMBER AS [PO_NUMBER],
       M.MATERIAL, 
       M.MATERIAL_DESCRIPTION, 
       M.SIZE_LITERAL,
       A.GRID_VALUE,
       SUM(M.QUANTITY) AS [PO_QTY],
       SUM(A.QUANTITY) AS [SO_QTY],
       M.SALES_ORDER_NUMBER, 
       M.SALES_ORDER_ITEM_NUMBER, 
       M.CUSTOMER_NAME, 
       M.PLANNED_RECEIPT_DATE AS [ETA], 
       M.PLANT_CODE, 
       M.STOCK_TYPE



FROM   VW_MRP_ALLOCATION M
JOIN   VW_ARUN_NORM_NEW A   
ON     M.SALES_ORDER_NUMBER = A.SALES_ORDER_NUMBER
AND    M.SALES_ORDER_ITEM_NUMBER = A.SALES_ORDER_ITEM_NUMBER
AND    M.STOCK_NUMBER = A.SAP_PO_NUMBER
AND    M.SIZE_LITERAL = A.GRID_VALUE
AND    M.STOCK_TYPE = A.STOCK_TYPE
AND    M.MATERIAL = A.MATERIAL_NUMBER

WHERE  M.REQUIREMENT_TYPE = 'KE'
AND    M.STOCK_TYPE NOT IN ('A','C')
AND    M.STOCK_NUMBER IS NOT NULL
AND    M.QUANTITY <> A.QUANTITY

GROUP BY M.STOCK_NUMBER,
       M.MATERIAL, 
       M.MATERIAL_DESCRIPTION, 
       M.SIZE_LITERAL, 
       M.SALES_ORDER_NUMBER, 
       M.SALES_ORDER_ITEM_NUMBER, 
       M.CUSTOMER_NAME, 
       M.PLANNED_RECEIPT_DATE,
       M.PLANT_CODE, 
       M.STOCK_TYPE,
       A.GRID_VALUE

ORDER BY  M.STOCK_NUMBER,
          M.SIZE_LITERAL ASC

This code results in the below (sample of a line):

PO_NUMBER    MATERIAL           MATERIAL_DESCRIPTION                     SIZE_LITERAL GRID_VALUE PO_QTY                                  SO_QTY                                  SALES_ORDER_NUMBER SALES_ORDER_ITEM_NUMBER                 CUSTOMER_NAME                       ETA                     PLANT_CODE STOCK_TYPE
------------ ------------------ ---------------------------------------- ------------ ---------- --------------------------------------- --------------------------------------- ------------------ --------------------------------------- ----------------------------------- ----------------------- ---------- ----------
0283472626   D93889             ADI STAIRS MEN      BLACK                L            L          400                                     400                                     6194114805         10                                      ROSS STORES INC                     2018-02-28 00:00:00.000 6010       L
0283472626   D93889             ADI STAIRS MEN      BLACK                M            M          400                                     400                                     6194114805         10                                      ROSS STORES INC                     2018-02-28 00:00:00.000 6010       L
0283472626   D93889             ADI STAIRS MEN      BLACK                S            S          200                                     200                                     6194114805         10                                      ROSS STORES INC                     2018-02-28 00:00:00.000 6010       L
0283472626   D93889             ADI STAIRS MEN      BLACK                XL           XL         200                                     200                                     6194114805         10                                      ROSS STORES INC                     2018-02-28 00:00:00.000 6010       L

If I remove the SUM and add the QUANTITY to the group by I get this:

PO_NUMBER    MATERIAL           MATERIAL_DESCRIPTION                     SIZE_LITERAL GRID_VALUE PO_QTY                                  SO_QTY                                  SALES_ORDER_NUMBER SALES_ORDER_ITEM_NUMBER                 CUSTOMER_NAME                       ETA                     PLANT_CODE STOCK_TYPE
------------ ------------------ ---------------------------------------- ------------ ---------- --------------------------------------- --------------------------------------- ------------------ --------------------------------------- ----------------------------------- ----------------------- ---------- ----------
0283472626   D93889             ADI STAIRS MEN      BLACK                L            L          28                                      372                                     6194114805         10                                      ROSS STORES INC                     2018-02-28 00:00:00.000 6010       L
0283472626   D93889             ADI STAIRS MEN      BLACK                L            L          372                                     28                                      6194114805         10                                      ROSS STORES INC                     2018-02-28 00:00:00.000 6010       L
0283472626   D93889             ADI STAIRS MEN      BLACK                M            M          28                                      372                                     6194114805         10                                      ROSS STORES INC                     2018-02-28 00:00:00.000 6010       L
0283472626   D93889             ADI STAIRS MEN      BLACK                M            M          372                                     28                                      6194114805         10                                      ROSS STORES INC                     2018-02-28 00:00:00.000 6010       L
0283472626   D93889             ADI STAIRS MEN      BLACK                S            S          14                                      186                                     6194114805         10                                      ROSS STORES INC                     2018-02-28 00:00:00.000 6010       L
0283472626   D93889             ADI STAIRS MEN      BLACK                S            S          186                                     14                                      6194114805         10                                      ROSS STORES INC                     2018-02-28 00:00:00.000 6010       L
0283472626   D93889             ADI STAIRS MEN      BLACK                XL           XL         14                                      186                                     6194114805         10                                      ROSS STORES INC                     2018-02-28 00:00:00.000 6010       L
0283472626   D93889             ADI STAIRS MEN      BLACK                XL           XL         186                                     14                                      6194114805         10                                      ROSS STORES INC                     2018-02-28 00:00:00.000 6010       L

1 Answer: 

If I understand your logic correctly, then you should be comparing the two sums in a HAVING clause:

SELECT
    M.STOCK_NUMBER AS [PO_NUMBER],
    M.MATERIAL, 
    M.MATERIAL_DESCRIPTION, 
    M.SIZE_LITERAL,
    A.GRID_VALUE,
    SUM(M.QUANTITY) AS [PO_QTY],
    SUM(A.QUANTITY) AS [SO_QTY],
    M.SALES_ORDER_NUMBER, 
    M.SALES_ORDER_ITEM_NUMBER, 
    M.CUSTOMER_NAME, 
    M.PLANNED_RECEIPT_DATE AS [ETA], 
    M.PLANT_CODE, 
    M.STOCK_TYPE
FROM VW_MRP_ALLOCATION M
INNER JOIN VW_ARUN_NORM_NEW A   
    ON M.SALES_ORDER_NUMBER = A.SALES_ORDER_NUMBER AND
       M.SALES_ORDER_ITEM_NUMBER = A.SALES_ORDER_ITEM_NUMBER AND
       M.STOCK_NUMBER = A.SAP_PO_NUMBER AND
       M.SIZE_LITERAL = A.GRID_VALUE AND
       M.STOCK_TYPE = A.STOCK_TYPE AND
       M.MATERIAL = A.MATERIAL_NUMBER
WHERE
    M.REQUIREMENT_TYPE = 'KE' AND
    M.STOCK_TYPE NOT IN ('A','C') AND
    M.STOCK_NUMBER IS NOT NULL AND
    M.QUANTITY <> A.QUANTITY
GROUP BY
    M.STOCK_NUMBER,
    M.MATERIAL, 
    M.MATERIAL_DESCRIPTION, 
    M.SIZE_LITERAL, 
    M.SALES_ORDER_NUMBER, 
    M.SALES_ORDER_ITEM_NUMBER, 
    M.CUSTOMER_NAME, 
    M.PLANNED_RECEIPT_DATE,
    M.PLANT_CODE, 
    M.STOCK_TYPE,
    A.GRID_VALUE
HAVING
    SUM(M.QUANTITY) <> SUM(A.QUANTITY)
ORDER BY
    M.STOCK_NUMBER,
    M.SIZE_LITERAL;