I am hoping you can help me understand my options for resolving a dilemma. I have developed a SQL query that runs fine and the desired output is achieved in ManagementStudio.
A parameter to the query(used to simulate User selection from an SSRS report) is a comma separated list. Using that comma separated list, I retrieve values from a table in my database and then build a string. Here is the output of that string for the 4 comma separated values:
(incidentDate >= '2017-08-17' and incidentDate <= '2017-10-12') OR
(incidentDate >= '2017-10-16' and incidentDate <= '2017-12-19') OR
(incidentDate >= '2018-01-03' and incidentDate <= '2018-03-16') OR
(incidentDate >= '2018-03-19' and incidentDate <= '2018-05-31')
I set this to a variable to use in my query string which is then set to @cmdString. I then exec(@cmdString). Works great no issues.
The issue I am having is when I try to use this in my SSRS report. The editor does not recognize the fields to display on the report since they are in the string I am executing.
Then when I try to create that query the "regular" way, I run into trouble because of trying to use a variable in the Where clause. I am not sure what to do.
Please ask questions as I probably did not explain well,