Put parameter date variables as columns

[jbrotto]

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.

/* 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

[rasmuswulff_jensen · Accepted solution]

You need to specify your declared variables Type

So

DECLARE @DocDateFrom

Should be

DECLARE  @DocDateFrom DATETIME

Before it works: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/variables-transact-sql?view=sql-server-...

[jbrotto · Accepted solution]

I tried tweaking the code but get this error.

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



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

[jbrotto]

Error in running the query.

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



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

[jbrotto]

Solved my issue it seemed to be an issue with the aliases for the dummy table.

/* SELECT FROM [ORDR] T1 */
DECLARE @DocDateFrom AS DATETIME
/* WHERE */
SET @DocDateFrom = /* T1.DocDate */ '[%1]' 



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