Member Avatar for Hers2keep

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')
  • 2 Contributors
  • 1 Reply
  • 151 Views
  • 1 Hour Discussion Span
  • Latest Post Latest Post by BitBlt
Member Avatar for BitBlt

Okay, so you are probably not going to like this solution, but it will work. Please study it carefully because there are nuances in it, and I don't have time at the moment to walk you through it. If, after using this, you still have questions then please post them and I'll try to respond as soon as I can.

So, given all that, use this script:

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
declare @myStmt as varchar(8000)

select @myStmt=
'DECLARE @CURRYEAR AS CHAR(4)' + CHAR(10)
+ 'DECLARE @PREVYEAR1 AS CHAR(4) ' + CHAR(10)
+ 'DECLARE @PREVYEAR2 AS CHAR(4) ' + CHAR(10)
+ 'DECLARE @PREVYEAR3 AS CHAR(4) ' + CHAR(10)
+ 'DECLARE @PREVYEAR4 AS CHAR(4)' + CHAR(10)
+ 'DECLARE @PREVYEAR5 AS CHAR(4) ' + CHAR(10)
+ 'SET @CURRYEAR = CONVERT(char, datepart(yy, GETDATE())) ' + CHAR(10)
+ 'SET @PREVYEAR1 = CONVERT(char, datepart(yy, GETDATE()))-1 ' + CHAR(10)
+ 'SET @PREVYEAR2 = CONVERT(char, datepart(yy, GETDATE()))-2 ' + CHAR(10)
+ 'SET @PREVYEAR3 = CONVERT(char, datepart(yy, GETDATE()))-3 ' + CHAR(10)
+ 'SET @PREVYEAR4 = CONVERT(char, datepart(yy, GETDATE()))-4 ' + CHAR(10)
+ 'SET @PREVYEAR5 = CONVERT(char, datepart(yy, GETDATE()))-5 ' + CHAR(10)
+ 'SELECT RFC, Color,' + CHAR(10)
+ '        CASE WHEN DatePart(year, DateEntered) = @PREVYEAR5 ' + CHAR(10)
+ '            THEN 1 ' + CHAR(10)
+ '            ELSE 0  ' + CHAR(10)
+ '        END AS [' + @PREVYEAR5 + '],  ' + CHAR(10)
+ '        CASE WHEN DatePart(year, DateEntered) = @PREVYEAR4 ' + CHAR(10)
+ '            THEN 1 ' + CHAR(10)
+ '            ELSE 0 ' + CHAR(10)
+ '        END AS [' + @PREVYEAR4 + '],  ' + CHAR(10)
+ '        CASE WHEN DatePart(year, DateEntered) = @PREVYEAR3 ' + CHAR(10)
+ '            THEN 1 ' + CHAR(10)
+ '            ELSE 0 ' + CHAR(10)
+ '        END AS [' + @PREVYEAR3 + '],  ' + CHAR(10)
+ '        CASE WHEN DatePart(year, DateEntered) = @PREVYEAR2 ' + CHAR(10)
+ '            THEN 1 ' + CHAR(10)
+ '            ELSE 0 ' + CHAR(10)
+ '        END AS [' + @PREVYEAR2 + '],  ' + CHAR(10)
+ '        CASE WHEN DatePart(year, DateEntered) = @PREVYEAR1 ' + CHAR(10)
+ '            THEN 1 ' + CHAR(10)
+ '            ELSE 0 ' + CHAR(10)
+ '        END AS [' + @PREVYEAR1 + '],  ' + CHAR(10)
+ '        CASE WHEN DatePart(year, DateEntered) = @CURRYEAR ' + CHAR(10)
+ '            THEN 1 ' + CHAR(10)
+ '            ELSE 0 ' + CHAR(10)
+ '        END AS [' + @CURRYEAR + ']  ' + CHAR(10)
+ '    FROM    CL_STATUS_DATA ' + CHAR(10)
+ '    WHERE   DatePart(year, DateEntered) BETWEEN DatePart(year, GETDATE()) AND DATEPART(YEAR,GETDATE()-5) ' + CHAR(10)
+ '    AND     CS NOT IN (''CLASS-ACTION'',''DELETED'') ' + CHAR(10)

select @myStmt

Please note that the select statement at the end will simply display the generated SQL statement, not execute it. You'll have to use "exec(@myStmt)" for it to work. Oh, and to see the statement, you'll have to set the results pane to display as text, and you'll have to set the query options (right click) to display 8000 characters.

Here's the result when I ran this on my own machine:

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 [2007],  
        CASE WHEN DatePart(year, DateEntered) = @PREVYEAR4 
            THEN 1 
            ELSE 0 
        END AS [2008],  
        CASE WHEN DatePart(year, DateEntered) = @PREVYEAR3 
            THEN 1 
            ELSE 0 
        END AS [2009],  
        CASE WHEN DatePart(year, DateEntered) = @PREVYEAR2 
            THEN 1 
            ELSE 0 
        END AS [2010],  
        CASE WHEN DatePart(year, DateEntered) = @PREVYEAR1 
            THEN 1 
            ELSE 0 
        END AS [2011],  
        CASE WHEN DatePart(year, DateEntered) = @CURRYEAR 
            THEN 1 
            ELSE 0 
        END AS [2012]  
    FROM    CL_STATUS_DATA 
    WHERE   DatePart(year, DateEntered) BETWEEN DatePart(year, GETDATE()) AND DATEPART(YEAR,GETDATE()-5) 
    AND     CS NOT IN ('CLASS-ACTION','DELETED')

Hope this works for you! Good luck!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.