cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 7

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

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

Tags (2)
0 Kudos
1 Reply
Level 15

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!

0 Kudos