Challenges with Report Writing

sayrematics

Hi everyone.

I am trying to create a custom report to give me a list of sites with availability less than 95% over a period of 1 week.I have selected node name, IP address and availability under fields. The challenge comes when I try to filter fields specifying records that contain only records where availability is less than 95. The result however comes out showing a lot of sites that are not in this category and every site is listed with availability of 0.00%. My query statement is shown below.

SELECT TOP 10000 Convert(DateTime,Floor(Cast((DateTime) as Float)),0) AS SummaryDate,
Nodes.Caption AS NodeName,
Nodes.IP_Address AS IP_Address,
AVG(ResponseTime.Availability) AS AVERAGE_of_Availability

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


WHERE
( DateTime BETWEEN 40028 AND 40029 )
 AND
(
  (ResponseTime.Availability <> 100)
)


GROUP BY Convert(DateTime,Floor(Cast((DateTime) as Float)),0),
Nodes.Caption, Nodes.IP_Address


ORDER BY SummaryDate ASC, 4 DESC

Thanks for the help.

lchance

Try this one - but - in my own case I had to use LAST 12 MONTHS instead of a week to get anything of significance to report on.

 

SELECT  TOP 10000 Convert(DateTime,Floor(Cast((DateTime) as Float)),0) AS SummaryDate,
Nodes.Caption AS NodeName,
Nodes.IP_Address AS IP_Address,
AVG(ResponseTime.Availability) AS AVERAGE_of_Availability

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

WHERE
( DateTime BETWEEN 39665 AND 40031 )
 AND 
(
  (ResponseTime.Availability < 95) AND
  (
   NOT (ResponseTime.Availability = 0))
)

GROUP BY Convert(DateTime,Floor(Cast((DateTime) as Float)),0),
Nodes.Caption, Nodes.IP_Address

ORDER BY SummaryDate ASC, 4 ASC