Skip to Content
1
Jun 22 at 08:13 PM

Put parameter date variables as columns

69 Views

Trying to make a report that will show the from and to date on the far left column. But get an error for the SET keyword.

image.png

/* SELECT FROM [ORDR] T2 */
DECLARE @DocDateFrom
/* WHERE */
SET @DocDateFrom = /* T2.DocDate */ '[%0]'



Select @DocDateFrom AS 'Date1',
T2.SLPName, T4.ItemCode, T4.ItemName,
SUM(CASE WHEN YEAR(T1.DocDate) = YEAR(GetDAte())-1 THEN ISNULL(T0.LineTotal,0) ELSE 0 END) as 'Last Year',
SUM(CASE WHEN YEAR(T1.DocDate) <> YEAR(GetDAte())-1 THEN ISNULL(T0.LineTotal,0) ELSE 0 END) as 'This Year'

FROM RDR1 T0
INNER JOIN ORDR T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OSLP T2 ON T1.SlpCode = T2.SlpCode
INNER JOIN OCRD T3 ON T1.CardCode = T3.CardCode
INNER JOIN OITM T4 ON T4.Itemcode = T0.itemCode
WHERE T2.SlpName = [%0] AND ((T1.DocDate >= [%1] and T1.DocDate <= [%2]) OR
(T1.DocDate >= DateAdd(YEAR, -1, [%1]) and T1.DocDate <= DateAdd(YEAR, -1, [%2]))) 
AND T1.DocType = 'I' AND T1.Canceled = 'N'
GROUP BY T2.SLPName, T4.ItemCode, T4.ItemName

Attachments

image.png (6.3 kB)