It is possible to calculate the average in the availability column

0

I would like to know if it is possible to add the availability and divide it by the number of teams to obtain the average of availability (Availability)

The Query is as follows:

DECLARE @StartDate DateTime DECLARE @EndDate DateTime
SET @StartDate = DATEADD(d, DATEDIFF(d, 0, DATEADD(m, -1, DATEADD(d, 1 - day(getdate()), getdate()))), 0)
SET @EndDate = DATEADD(ms, -2,DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate())), 0));

SELECT SummaryMonth,
       NodeID,
       Vendor_Icon,
       NodeName,
       IP_Address,
       AVERAGE_of_Availability,
       SLA_Node,
       SLA,
       SLA_Status
FROM
  (SELECT TOP 100 CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101) AS SummaryMonth,
              Nodes.NodeID AS NodeID,
              Nodes.VendorIcon AS Vendor_Icon,
              Nodes.Caption AS NodeName,
              Nodes.IP_Address AS IP_Address,
              AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,
              Nodes.SLA_Node AS SLA_Node,
              CASE
                  WHEN AVG(ResponseTime.Availability) > Nodes.SLA_Node THEN 'CUMPLE SLA'
                  ELSE 'NO CUMPLE SLA'
              END AS SLA,
              CASE
                  WHEN AVG(ResponseTime.Availability) > Nodes.SLA_Node THEN 'Up'
                  ELSE 'Down'
              END AS SLA_Status
   FROM Nodes
   INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)
   WHERE (DateTime BETWEEN @StartDate AND @EndDate)
     AND (Nodes.Caption LIKE '%SOL%') 
     GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
            Nodes.NodeID,
            Nodes.VendorIcon,
            Nodes.Caption,
            Nodes.IP_Address,
            Nodes.SLA_Node ) AS r
ORDER BY SummaryMonth ASC,
         4 ASC

*********************** Result *******

    
asked by Rodolfo 29.08.2017 в 22:08
source

0 answers