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;