I am working on a SQL query that calculates the average availability for certain nodes between the hours of 7AM and 11PM.
I have a feeling my @morning and @evening variables are incorrect, but i'm not exactly sure, the report just keeps returning blank results.
Here is my query so far:
DECLARE @startOfCurrentDay DATETIME
DECLARE @morning as DATETIME
DECLARE @evening as DATETIME
SET @morning = DATEADD(HOUR, 7, CAST(CAST(getdate() AS DATE) AS DATETIME))
SET @evening = DATEADD(HOUR, 23, CAST(CAST(getdate() AS DATE) AS DATETIME))
SET @startOfCurrentDay = DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
SELECT top 1
sub.SummaryMonth AS Day_Of,
AVG (sub.AVERAGE_of_Availability) as Total_Average
FROM
(
SELECT Nodes.VendorIcon AS Vendor_Icon,
Nodes.Caption AS NodeName,
Nodes.MachineType AS Machine_Type,
AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,
CONVERT(DateTime,
LTRIM(MONTH(DateTime)) + '/' + LTRIM(day(DateTime)) + '/' + LTRIM(YEAR(DateTime)),
101) AS SummaryMonth
FROM
Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)
WHERE
( datetime >= DATEADD(day, -1, @startOfCurrentDay) AND datetime < @startOfCurrentDay AND (DATETIME BETWEEN @morning and @evening) )
AND
(
(Nodes.Caption LIKE '%-FW%') and (Nodes.Caption NOT LIKE '%PG%') and (Nodes.Caption NOT LIKE '%C999%') and (Nodes.Caption NOT LIKE '%C0D%') and (Nodes.Caption NOT LIKE '%C0S%')
)
GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/' + LTRIM(day(DateTime)) + '/' + LTRIM(YEAR(DateTime)), 101),
Nodes.VendorIcon, Nodes.Caption, Nodes.MachineType
) sub
GROUP BY sub.SummaryMonth
order by sub.SummaryMonth asc
Any help would be greatly appreciated, thank you!
