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

Help needed adding a comparative date range

16 Views

Hello,

I have had a request from one of our users to see if I can modify one of our existing queries:

--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.GroupName, COUNT(CardCode) as [STORES], SUM(QTY) as [QTY], SUM(TOTAL) as [TOTAL]

FROM

(

SELECT [GroupName], CardCode, Sum([Quantity]) QTY, Sum([LineTotal]) TOTAL

FROM (

SELECT T5.[GroupName], T0.CardCode, 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'

INNER JOIN OCRD T4 ON T0.CardCode = T4.CardCode

INNER JOIN OCRG T5 ON T4.GroupCode = T5.GroupCode

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 T4.[GroupName], T0.CardCode, -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

INNER JOIN OCRD T3 ON T0.CardCode = T3.CardCode

INNER JOIN OCRG T4 ON T3.GroupCode = T4.GroupCode

WHERE T2.[U_Brand] = @BRAND AND

T0.[DocDate] BETWEEN @FRDT AND @TODT

UNION ALL

SELECT T4.[GroupName], T0.U_CardCode, 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

INNER JOIN OCRD T3 ON T0.U_CardCode = T3.CardCode

INNER JOIN OCRG T4 ON T3.GroupCode = T4.GroupCode

WHERE T2.[U_Brand] = @BRAND AND

T0.[U_Date] BETWEEN @FRDT AND @TODT

) Y

GROUP BY Y.GroupName, Y.CardCode

) X

GROUP BY X.GroupName

ORDER BY X.GroupName

What this does is asks the user to select a Brand which we have as a UDF.

Also - they need to select a date from and date to range.

This will display Customer Group, Number of business partners whom purchased the brand, Qty of products within the date range and the dollar value across the brand within the date range.

Essentially I have been asked whether it is possible to add in another 2 parameters to ask for another date range which will be used to compare the data against the first date range. As a result, we will need 3 more columns, which would be Number of BP's, Qty, Value from the second date range.

I have had a go at this - but I think it may be outside the realm of my knowledge!

Any help would be greatly appreciated!

Regards

Rick