This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Change SQL Query for "last month" to "last 6 months" or "Last year"

ktearns over 4 years ago

This pre-fab report queries outage durations for the last month, and it works really well. How can I alter this query to show data for the last 6 months, or the last year?

I've tried making changes to the 'between dateadd' string in the where statement. I've tried changing '-1' to '-6' and 'month' to 'year' all with no luck. Any ideas?

SELECT

StartTime.EventTime,

Nodes.Caption,

Nodes.Location,

StartTime.Message,

DATEDIFF(Mi, StartTime.EventTime,

(SELECT TOP 1

EventTime

FROM Events AS Endtime

WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5

AND EndTime.NetObjectType = 'N'

AND EndTime.NetworkNode = StartTime.NetworkNode

ORDER BY EndTime.EventTime)) AS OutageDurationInMinutes

FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID

WHERE (StartTime.EventType = 1) AND (StartTime.NetObjectType = 'N') AND

eventtime between dateadd(month, -1, getdate()) and getdate()

ORDER BY Nodes.Caption ASC

0 zackm over 4 years ago
the issue you're running into is a lack of events because they dropped out of your DB. default event table retention is only 30 days by default.
if you want to change this setting, you can; but it can cause some negative consequences re: your platform health because of the sheer amount of events that are generally created. there's a decent guide available that covers this topic.
Optimize the database used by Orion Platform products
for your reference, you were on the right track!
SQL Server DATEADD() Function
SQL Server DATEADD Function By Practical Examples
DATEADD (Transact-SQL) - SQL Server | Microsoft Docs
tl;dr -
DATEADD( <interval>, <(-) range>, <fromDateTime>)
from your example:
eventtime between dateadd(month, -1, getdate()) and getdate()
this is saying:
WHERE the 'EventTime' data from the table is between -1 month from today and today
getdate(), is a function that returns the datetime from the moment of query execution
So, when you changed "-1" to "-6", you effectively said, between 6 months ago and today.
That being said, because datetime() returns "now"; you could just as easily use a clause like this:
EventTime > DATEADD( month, -6, GETDATE() )
so; kudos on the effort!
Cancel
Vote Up 0 Vote Down

Cancel