5 Replies Latest reply on Nov 13, 2009 3:40 PM by r0berth1

    Exceptions Report

    r0berth1

      Can someone explain to me why this is not working? I need a report that shows only the locations that have a 98% or lower uptime and show the comments. the standard report works until i added the "SELECT * FROM ( at the beginning and the ) AS X WHERE AVERAGE_of_Availability <= 98;"

      SELECT * FROM (

      SELECT  TOP 10000 CONVERT(DateTime,
      LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
      101) AS SummaryMonth,
      Nodes.NodeID AS NodeID,
      Nodes.Branch AS Branch,
      AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,
      Nodes.Comments AS Comments

      FROM
      Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)


      WHERE
      ( DateTime BETWEEN 40055 AND 40084.9999884259 )
       AND 
      (
        (Nodes.Branch IS NOT NULL) AND
        (Convert(Char,DateTime,108) >= '07:00') AND
        (Convert(Char,DateTime,108) <= '19:00')
      )


      GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
      Nodes.NodeID, Nodes.Branch, Nodes.Comments


      ORDER BY SummaryMonth ASC, 3 ASC) AS X WHERE AVERAGE_of_Availability <= 98;

        • Re: Exceptions Report
          r0berth1

          OK, when i run this report, it pulls all info that i need as far as outages go:

          _______________________________________________________________

          SELECT  TOP 10000 CONVERT(DateTime,
          LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
          101) AS SummaryMonth,
          AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,
          Nodes.Branch AS Branch

          FROM
          Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)


          WHERE
          ( DateTime BETWEEN 39537 AND 39566.9999884259 )
           AND 
          (
            (Nodes.State = 'LA') AND
            (DATEPART(weekday, DateTime) <> 7) AND
            (DATEPART(weekday, DateTime) <> 1) AND
            (Convert(Char,DateTime,108) >= '07:00') AND
            (Convert(Char,DateTime,108) <= '19:00')
          )


          GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
          Nodes.Branch


          ORDER BY SummaryMonth ASC, 3 ASC

          _______________________________________________________________

          Now, i have adjusted it a little to pull only the devices that have a 98% or less uptime by doing the following but it it does not return any results even though i do have devices that return a 98% or lower up time on the above report:

          _______________________________________________________________

          SELECT * FROM (
          SELECT  TOP 10000 CONVERT(DateTime,
          LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
          101) AS SummaryMonth,
          AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,
          Nodes.Branch AS Branch,
          Nodes.Comments AS Comments

          FROM
          Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)


          WHERE
          ( DateTime BETWEEN 40085 AND 40115.9999884259 )
           AND  
          (
            (Nodes.Branch IS NOT NULL) AND
            (Convert(Char,DateTime,108) >= '07:00') AND
            (Convert(Char,DateTime,108) <= '19:00') AND
            (Nodes.Comments IS NOT NULL)
          )


          GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
          Nodes.Branch, Nodes.Comments


          ORDER BY SummaryMonth ASC) AS X WHERE AVERAGE_of_Availability <= 98;

          _______________________________________________________________

          Any help of this would be great.

            • Re: Exceptions Report
              lchance

              When you say you don't see any results, do you mean as in nothing?

              I ran it (substituting my own custom properties) and it looks good - I even included the Nodes and that adds a little more interest to it for me...

               

              SELECT * FROM (
              SELECT  TOP 10000 CONVERT(DateTime,
              LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
              101) AS SummaryMonth,
              AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,
              Nodes.Region AS Region,
              Nodes.Department AS Department,
              Nodes.Caption AS NodeName

              FROM
              Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)


              WHERE
              ( DateTime BETWEEN 40085 AND 40115.9999884259 )
               AND 
              (
                (Nodes.Region IS NOT NULL) AND
                (Convert(Char,DateTime,108) >= '07:00') AND
                (Convert(Char,DateTime,108) <= '19:00') AND
                (Nodes.Department IS NOT NULL)
              )


              GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
              Nodes.Region, Nodes.Department, Nodes.Caption


              ORDER BY SummaryMonth ASC) AS X WHERE AVERAGE_of_Availability <= 98;

                • Re: Exceptions Report
                  r0berth1

                  I get the headers but no data under it.

                    • Re: Exceptions Report
                      lchance

                      Hmmmm. My guess is an issue with the NOT NULL.

                        • Re: Exceptions Report
                          r0berth1

                          Sorry guys. This is the correct code that is giving the problem. It shows the headers but no data.

                          SELECT * FROM (
                          SELECT  TOP 10000 CONVERT(DateTime,
                          LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
                          101) AS SummaryMonth,
                          AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,
                          Nodes.Branch AS Branch,
                          Nodes.Comments AS Comments,
                          Nodes.Caption AS NodeName

                          FROM
                          Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)


                          WHERE
                          ( DateTime BETWEEN 40085 AND 40115.9999884259 )
                           AND
                          (
                            (Nodes.Branch IS NOT NULL) AND
                            (Nodes.State = 'LA') AND
                            (DATEPART(weekday, DateTime) <> 7) AND
                            (DATEPART(weekday, DateTime) <> 1) AND
                            (Convert(Char,DateTime,108) >= '07:00') AND
                            (Convert(Char,DateTime,108) <= '19:00') AND
                            (Nodes.Comments IS NOT NULL)
                          )


                          GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
                          Nodes.Branch, Nodes.Comments, Nodes.Caption


                          ORDER BY SummaryMonth ASC) AS X WHERE AVERAGE_of_Availability <= 98;

                           

                          Looks like the problem comes in when i add the custom field (Nodes.State = 'LA') AND but that is in all of my other reports and works fine, just not in this report. Again, sorry for posting the wrong one.