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