2 Replies Latest reply on Feb 9, 2018 12:56 PM by ricks22

    Availability reports for "last month" show February instead of January

    ricks22

      We run the "Application Availability - Last Month" report every month.  This month it just keep showing February, instead of January.

       

      Can somebody explain why that would be happening?  Here is the SQL form report writer:

       

      DECLARE @utcOffset int

      DECLARE @actualMonths int

       

      SET @utcOffset = datediff(minute, getdate(), getutcdate())

      SET @actualMonths = datediff(month, 0, getdate())

       

      SELECT

          max(APM_ApplicationStatus.[TimeStamp]) as [Month],

          Nodes.Caption,

          APM_Application.Name AS ApplicationName,

          sum(APM_ApplicationStatus.PercentAvailability * APM_ApplicationStatus.RecordCount) / sum(APM_ApplicationStatus.RecordCount) AS PercentAvailability

      FROM APM_ApplicationStatus with(nolock)

      INNER JOIN APM_Application with(nolock) ON APM_Application.ID = APM_ApplicationStatus.ApplicationID

      INNER JOIN Nodes with(nolock) ON Nodes.NodeID = APM_Application.NodeID

      WHERE APM_ApplicationStatus.[TimeStamp] >= dateadd(minute, @utcOffset, dateadd(month, @actualMonths - 1, 0)) AND

          APM_ApplicationStatus.[TimeStamp] < dateadd(minute, @utcOffset, dateadd(month, @actualMonths, 0))

      GROUP BY APM_Application.ID, APM_Application.Name, Nodes.Caption

      ORDER BY Nodes.Caption, APM_Application.Name

        • Re: Availability reports for "last month" show February instead of January
          jrouviere

          I believe your issue is with this statement:

           

          max(APM_ApplicationStatus.[TimeStamp]) as [Month],

           

          This results in it setting the current application status time as the Month.  If you set it to:

           

          max(APM_ApplicationStatus.[TimeStamp] -1) as [Month],

           

          for example, then your report would show it as January 31st (since that's the max application status for last month).

           

          I am not exactly a SQL expert so I'm not sure what the preferred method of updating your time stamp would be, but perhaps checking some other monthly reports might give you a better show for it or dropping that field entirely.  It seems like the rest of your statements get the desired results, but this Month field is the one that's misleading.

          1 of 1 people found this helpful