Skip to Content

Group By field adds duplicate rows

Hi community - I have a query below that works great - but I also wanted to add the field ON HAND to show what is in stock next to what the customer has taken over a date range and when that happens it shows duplicates. What it appears to do is spread the volume taken out over the amount of invoices created for the product.

Working Code:

SELECT T2.[ItemCode]
, T2.[ItemName]
, SUM (T1.[Quantity]) AS 'Total Qty Taken'

FROM ORDR T0
INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

WHERE T0.[CardCode] BETWEEN [%0] AND [%1] AND T0.[DocDate] BETWEEN [%2] AND [%3] AND T1.[ItemCode] NOT Like '%%Carriage%%' AND T1.[ItemCode] NOT Like '%%SPOOKY%%' AND T1.[ItemCode] NOT Like '%%XMAS%%'

GROUP BY T2.[ItemCode], T2.[ItemName]

ORDER BY T2.[ItemCode]

Adding the following (in bold) breaks it:

SELECT T2.[ItemCode]
, T2.[ItemName]
, SUM (T1.[Quantity]) AS 'Total Qty Taken'
, T3.[OnHand]

FROM ORDR T0
INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
INNER JOIN OITW T3 ON T3.ItemCode = T2.ItemCode

WHERE T0.[CardCode] BETWEEN [%0] AND [%1] AND T0.[DocDate] BETWEEN [%2] AND [%3] AND T1.[ItemCode] NOT Like '%%Carriage%%' AND T1.[ItemCode] NOT Like '%%SPOOKY%%' AND T1.[ItemCode] NOT Like '%%XMAS%%'

GROUP BY T2.[ItemCode], T2.[ItemName], T3.[OnHand]

ORDER BY T2.[ItemCode]

I am sure it is to do with my GROUP BY clause, somehow but cannot figure out what.

Any help that can be provided will be greatfully received.

Regards

Roy

dupes.jpg (259.6 kB)
dupes2.jpg (259.0 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    Dec 07, 2016 at 04:54 PM

    Hi Roy,

    This happens because you probably have more then one warehouse, so, in your clause "INNER JOIN OITW T3 ON T3.ItemCode = T2.ItemCode" this will multiple your register by the number of warehouses.

    Try the following query only looking the warehouse that is defined on the line of the marketing document.

    SELECT 
    	T2.[ItemCode]
    	, T2.[ItemName]
    	, SUM (T1.[Quantity]) AS 'Total Qty Taken'
    	, T3.[OnHand]
    FROM 
    	ORDR T0 
    	INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry 
    	INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
    	INNER JOIN OITW T3 ON T3.ItemCode = T2.ItemCode AND T3.WhsCode = T1.WhsCode
    WHERE 
    	T0.[CardCode] BETWEEN [%0] AND [%1] 
    	AND T0.[DocDate] BETWEEN [%2] AND [%3] 
    	AND T1.[ItemCode] NOT Like '%%Carriage%%' 
    	AND T1.[ItemCode] NOT Like '%%SPOOKY%%' 
    	AND T1.[ItemCode] NOT Like '%%XMAS%%'
    GROUP BY 
    	T2.[ItemCode], 
    	T2.[ItemName], 
    	T3.[OnHand]
    ORDER BY 
    	T2.[ItemCode]
    

    Kind Regards,

    Diego Lother

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 08, 2016 at 09:18 AM

    Brilliant, Diego - great spot and thank you so much.

    That one is going in the knoweldge bank for future queries :)

    Warmest Regards

    Roy

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 08, 2016 at 03:20 PM

    Diego - I have added a couple of columns to this, on request.

    Is there a way to:

    1. Have the negative values return as ZERO?

    2. Round up the part numbers to a whole?

    SELECT
    T2.[ItemCode]
    , T2.[ItemName]
    , SUM (T1.[Quantity]) AS 'Total Qty Taken'
    , T3.[OnHand] AS 'Currently In Stock'
    , T3.[OnOrder] AS 'Currently On Order'
    , SUM (T1.[Quantity]) / 3*4 - (T3.[OnOrder]) AS 'Recommended to Order'

    FROM
    ORDR T0
    INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
    INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
    INNER JOIN OITW T3 ON T3.ItemCode = T2.ItemCode AND T3.WhsCode = T1.WhsCode

    WHERE
    T0.[CardCode] BETWEEN [%0] AND [%1]
    AND T0.[DocDate] BETWEEN [%2] AND [%3]
    AND T1.[ItemCode] NOT Like '%%Carriage%%'
    AND T1.[ItemCode] NOT Like '%%SPOOKY%%'
    AND T1.[ItemCode] NOT Like '%%XMAS%%'

    GROUP BY
    T2.[ItemCode],
    T2.[ItemName],
    T3.[OnOrder],
    T3.[OnHand]

    ORDER BY
    T2.[ItemCode]

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Roy,

      For the negative values you can apply a case statement in your query, like the sample:

      CASE WHEN T3.[OnHand] < 0 THEN 0 ELSE T3.[OnHand] END AS 'Currently In Stock'

      About round the values there are some function on sql server like:

      Round, Floor and Ceiling. A sample:

      CEILING((SUM (T1.[Quantity]) / 3*4 - (T3.[OnOrder]))) AS 'Recommended to Order'  --This force round up the value
      FLOOR((SUM (T1.[Quantity]) / 3*4 - (T3.[OnOrder]))) AS 'Recommended to Order'  --This force round down the value
      ROUND((SUM (T1.[Quantity]) / 3*4 - (T3.[OnOrder])), 2) AS 'Recommended to Order'  --This will round the value correctly based on the number of decimals places that you provide in the second parameter
      

      Hope it helps.

      Kind Regards,

      Diego Lother

  • Dec 08, 2016 at 04:26 PM

    Hi Diego - Brilliant - thanks, got it :)

    Roy

    Add comment
    10|10000 characters needed characters exceeded