1 Reply Latest reply on Aug 30, 2019 9:12 PM by zackm

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

    ktearns

      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