Hi,
I have this query which prompts the user to select a brand and date range, then it displays an item summary with product code, name, qty and value. This includes the SAP table, a UDT as well as credits.
--INCLUDES PRE-SAP HISTORY, DISCOUNTS & CREDITS
Declare @BRAND Nvarchar(50) = /* SELECT FROM OITM Q0 WHERE Q0.U_Brand = */ '[%0]'
Declare @FRDT DateTime = /* SELECT FROM OINV Q1 WHERE Q1.DocDate >= */ '[%1]'
Declare @TODT DateTime = /* SELECT FROM OINV Q2 WHERE Q2.DocDate <= */ '[%2]'
SELECT X.ItemCode, X.ItemName, SUM(QTY) as [QTY], SUM(TOTAL) as [TOTAL]
FROM
(
SELECT [ItemCode], [ItemName], Sum([Quantity]) as QTY, Sum([LineTotal]) TOTAL
FROM (
SELECT T2.[ItemCode], T2.[ItemName], T1.[Quantity], (1-T1.DiscPrcnt/100) * T1.LineTotal [LineTotal]
FROM OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
LEFT JOIN RDR1 T3 ON T1.BaseEntry = T3.DocEntry AND T1.BaseLine = T3.LineNum AND T1.BaseType='17'
WHERE T2.[U_Brand] = @BRAND AND
T1.[OpenQty] <> 0 AND
T0.[DocDate] BETWEEN @FRDT AND @TODT AND
ISNULL(T3.OpenQty,0)=0
UNION ALL
SELECT T1.[ItemCode], T1.[Dscription], -1 * T1.[Quantity], -(1-T0.[DiscPrcnt]/100) * T1.[LineTotal] as [LineTotal]
FROM ORIN T0
INNER JOIN RIN1 T1 ON T0.DocEntry=T1.DocEntry
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
WHERE T2.[U_Brand] = @BRAND AND
T0.[DocDate] BETWEEN @FRDT AND @TODT
UNION ALL
SELECT T2.[ItemCode], T2.[ItemName], T1.[U_Qty], T1.[U_RowTotal]
FROM [@OINV] T0
INNER JOIN [@INV1] T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.U_ItemNo = T2.ItemCode
WHERE T2.[U_Brand] = @BRAND AND T0.[U_Date] BETWEEN @FRDT AND @TODT
) Y
GROUP BY Y.ItemName, Y.ItemCode
) X
GROUP BY X.ItemCode, X.ItemName
ORDER BY X.ItemCode, X.ItemName
This works great - except for now it is repeating things when I select a certain brand.
See below for an example:

The product name was initially HOOT PLUSH 27CM and then it was changed recently to be *ABC* HOOT PLUSH 27CM,
The first line is sales (qty) and the second line is credits (qty).
How would you have these combined so that it doesn't display both descriptions?
Regards
Rick