SSRS and ignore the "have" clause
I am trying to get a list of data whose last updated date is greater than the current date minus x month.
An example of a table is shown as an example:
| DataGUID | UpdateDate |
|------------|---------------|
| AAA | 12-05-2017 |
| BBB | 22-06-2017 |
| AAA | 14-02-2017 |
| BBB | 16-05-2017 |
I currently have a SQL query that looks something like this:
SELECT
DataGUID,
MAX(UpdateDate)
FROM
Table
GROUP BY
DataGUID
HAVING
MAX(UpdateDate) <= DATEADD(mm, CAST('-'+@LastUpdatedXMonthAgo AS INT), GETDATE())
ORDER BY
MAX(UpdateDate) DESC;
Expected Output: (@LastUpdatedXMonthAgo = 1 and current date 13-07-2017):
| DataGUID | UpdateDate |
|------------|---------------|
| AAA | 12-05-2017 |
It works on SSMS, but SSRS seems to ignore the "having" clause and gives me this result:
| DataGUID | UpdateDate |
|------------|---------------|
| BBB | 22-06-2017 |
| AAA | 12-05-2017 |
It looks like SSRS is just ignoring the "having" clause, is there a way to make it work without using SSRS filters?
source to share
The problem is when you are trying to add hyphen concatenation to a session variable which is a number. This causes the minus sign to be ignored / dropped. Hence your suggestion HAVING
works, but it compares each maximum date to one month in the future, not one month.
The following query will show you one way to fix this problem:
DECLARE @LastUpdatedXMonthAgo INT;
SET @LastUpdatedXMonthAgo = 1;
SELECT
DATEADD(mm, CAST('-'+CAST(@LastUpdatedXMonthAgo AS VARCHAR(55)) AS INT), GETDATE());
This approach is to inject the @LastUpdatedXMonthAgo
text before trying to "nullify" the string. Another approach would be to just render the text @LastUpdatedXMonthAgo
, but it might be awkward for the rest of your script.
Update:
In SSMS, we could simplify this even further:
DATEADD(mm, -@LastUpdatedXMonthAgo, GETDATE());
Demo here:
Rextester
source to share