Skip to Content
0
May 29, 2013 at 06:44 AM

Assistance need to add another date range

17 Views

Hi,

I have a query:

--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, Max(X.ItemName) 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

ORDER BY X.ItemCode

What this does is it asks the user for a Brand (UDF) and a date from and to range.

I need help adding a second date from and to range.

The above query returns a product list, description, qty total over the date range and dollar value over the date range.

I need another two columns which would relate to the second date range.

Any help would be greatly appreciated!

Regards

Rick