Error using SRS report parameter in WHERE clause with CASE/WHEN statement
Posted on 2008-02-05
Greetings! I am fairly new to SRS Reporting and am just completely confused on how to resolve this issue.
We have several reports that use parameters which pull into the query such as (= @startdate or IN @organization) without no problems. All of the parameters I have used before are queried from the database except when using dates.
However, I am trying to build some non-queried parameters such as "Previous Week", "Current Month", etc. Currently, I have them set up with the label / value information of (Previous Week, 1) and (Current Month, 2). This parameter name is start.
In my SQL, I am calling this value as @start and in the WHERE clause is a date field that my query is filtering for. Generally I have been using BETWEEN @startdate and @enddate with both parameters being datetime data types.
What I would like to do is depending on the value of the @start parameter, the SQL would use a CASE/WHEN statement to determine the dates (instead of using specific dates via @startdate and @enddate). For example, if the user chose "Previous Week", the value is 1 and the case statement would use this code for my SQL date selection:
b.StartDate BETWEEN DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), - 8) AND DATEADD(ms, - 3, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) + 1, - 8))
If the selection was "Current Month" and the value 2, then it would be a different date selection in the WHERE statement.
Here is my code:
WHEN 1 Then b.StartDate BETWEEN DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), - 8) AND DATEADD(ms, - 3, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) + 1, - 8))
When 2 Then b.StartDate BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GetDate()), 0) AND DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GetDate()) + 1, 0))
The error I get is "Must declar the scalar variable @start". I have looked at it so long that I am now thoroughly confused and am uncertain whether the problem is the parameter, the SQL, or what.
If anyone has any suggestions, they are greatly appreciated.