I am using the SQL command below to generate weekdays report (business hour only) but to find out that it still covers the non-business hours. Please advise. Thanks.
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = '2014-12-08 07:00:00'
SET @EndDate = '2014-12-12 19:00:00'
set nocount on
create table #InterfaceIds (
InterfaceID int NOT NULL,
CONSTRAINT [PK_#InterfaceIds] PRIMARY KEY CLUSTERED
(
InterfaceID ASC
)
)
insert into #InterfaceIds
SELECT DISTINCT A.InterfaceID
FROM dbo.InterfaceTraffic A
WHERE A.DateTime >= @StartDate AND A.DateTime <= @EndDate
SELECT Interfaces.InterfaceId,
Nodes.NodeID,
Nodes.Caption AS NodeName,
Nodes.City,
Interfaces.Caption AS Interface_Caption,
Interfaces.InterfaceIcon AS Interface_Icon,
Interfaces.InterfaceSpeed,
ROUND(dbo.GetInBps95th(InterfaceID, @StartDate, @EndDate) / InBandwidth * 100,0) AS PercentIN,
ROUND(dbo.GetOutBps95th(InterfaceID, @StartDate, @EndDate)/ OutBandwidth * 100,0) AS PercentOUT
FROM Nodes
INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)
WHERE (Interfaces.InterfaceID in (4515,4517,4215,4214,3703,3228,4498,4496,4548,4550,4633,4635,2723,2728,3222,3226,3140,3128,3135,4320,4316,4318,4590,3170,3164,3167,3020,3172,3014,4544,4546,3742,3180,4739,4741,8780) )
AND (Interfaces.InterfaceSpeed != 0)
ORDER BY 8 DESC, 9 DESC
DROP TABLE #InterfaceIds
