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.

Exceptions Report

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;

  • 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.

  • 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;

  • I get the headers but no data under it.

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

  • 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.