CODEWITHSUNDEEP
sql-serverdatereporting-servicesgraphssrs-2008
krynil
krynil

Reputation: 243

SSRS graph: Retrieve values from different dates in SQL query

I am using the following query to retrieve snapshot values from 4 different dates: -1 day (latest), -2 days, -3 days and -40 days (not yet implemented).

SELECT [SnapshotDate]
      ,[SnapshotKey]
      ,[info1]
      ,[info2]
      ,[info3]
FROM [Database].[dbo].[Values]
WHERE [SnapshotDate] >= DATEADD(day,-3, GETDATE())
AND [SnapshotKey] = 'Some text here'

This results in the following graph:

enter image description here

The query is not quite right firstly since it is showing 4 values and should only be showing 3 at this point. Secondly I would like to show the last snapshot from 40 days ago as shown in the graph.

I have tried a few different queries but have not managed to figure out how to do this properly. 

[SnapshotKey] = SELECT DATEADD(day,-40,getdate())

The above query gives me the correct answer in theory. However, when I use this in my query there is no result. I believe this might be due to not having a date conversion or the fact that I'm using "day" in my query. I'm not sure.

Any suggestions?

EDIT:

I also tried using the following with no luck (no result):

CONVERT(date, [SnapshotDate]) = CONVERT(date, DATEADD(day,-40, GETDATE()))

Upvotes: 0

Views: 21

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21703

I'm not sure what your date values are but I'm guessing this report was run on 2nd May. If this is correct then you need to change the range to exclude where the difference in dates is zero. Personally I use DATEDIFF in situations like this as it's easier to visualise for me.

Try changing the where clause to something like this.

WHERE (DATEDIFF(day, getdate(),[SnapshotDate]) BETWEEN -3 AND -1
    OR DATEDIFF(day, getdate(), [SnapshotDate]) = -40)
    AND [SnapshotKey] = 'Some text here'

Upvotes: 1

Related Questions