Skip to Content
0
Nov 02, 2012 at 06:53 AM

Assistance required - query is repeating things!

33 Views

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

Attachments

Clipboard01.jpg (4.8 kB)