I have a sproc that pulls six years of records for a report. The person who set it up years ago set it up using statically defined years which ended in 2009. I am rewriting this sproc to pull the current year and five years previous.
In the process of rewriting this query, I am trying to set the names of the "year" columns dynamically based upon the current year. I can't get the Name of the column set using the variable. I've tried everything I can think of and it seems like this wouldn't be hard to do, but it's got me stumped. I want the columns to look like this: RFC, Color, 2007, 2008, 2009, 2010, 2011, 2012 (dynamically depending upon what the current year is). Following is the subquery that is part of the stored procedure. I get this error when running this query:
Msg 170, Level 15, State 1, Line 19
Line 19: Incorrect syntax near '@PREVYEAR5'.
DECLARE @CURRYEAR AS CHAR(4)
DECLARE @PREVYEAR1 AS CHAR(4)
DECLARE @PREVYEAR2 AS CHAR(4)
DECLARE @PREVYEAR3 AS CHAR(4)
DECLARE @PREVYEAR4 AS CHAR(4)
DECLARE @PREVYEAR5 AS CHAR(4)
SET @CURRYEAR = CONVERT(char, datepart(yy, GETDATE()))
SET @PREVYEAR1 = CONVERT(char, datepart(yy, GETDATE()))-1
SET @PREVYEAR2 = CONVERT(char, datepart(yy, GETDATE()))-2
SET @PREVYEAR3 = CONVERT(char, datepart(yy, GETDATE()))-3
SET @PREVYEAR4 = CONVERT(char, datepart(yy, GETDATE()))-4
SET @PREVYEAR5 = CONVERT(char, datepart(yy, GETDATE()))-5
SELECT RFC, Color,
CASE WHEN DatePart(year, DateEntered) = @PREVYEAR5
THEN 1
ELSE 0
END AS @PREVYEAR5, <<ERROR IS ON THIS LINE
CASE WHEN DatePart(year, DateEntered) = @PREVYEAR4
THEN 1
ELSE 0
END AS @PREVYEAR4,
CASE WHEN DatePart(year, DateEntered) = @PREVYEAR3
THEN 1
ELSE 0
END AS @PREVYEAR3,
CASE WHEN DatePart(year, DateEntered) = @PREVYEAR2
THEN 1
ELSE 0
END AS @PREVYEAR2,
CASE WHEN DatePart(year, DateEntered) = @PREVYEAR1
THEN 1
ELSE 0
END AS @PREVYEAR1,
CASE WHEN DatePart(year, DateEntered) = @CURRYEAR
THEN 1
ELSE 0
END AS @CURRYEAR
FROM CL_STATUS_DATA
WHERE DatePart(year, DateEntered) BETWEEN DatePart(year, GETDATE()) AND DATEPART(YEAR,GETDATE()-5)
AND CS NOT IN ('CLASS-ACTION','DELETED')