Group By field adds duplicate rows

[former_member239716]

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

[former_member185682 · Accepted solution]

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

[former_member239716]

Hi Diego - Brilliant - thanks, got it 🙂

Roy

[former_member239716]

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]

[former_member185682]

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

[former_member239716]

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

That one is going in the knoweldge bank for future queries 🙂

Warmest Regards

Roy