WAN Bandwidth Utilization on Business hours only

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

Parents
  • The issue is the start and end date and time.  You are pulling data from the start of business on Monday until the end of business on Friday.  I'm not good enough with SQL to help you fix it.  I set up individual reports for each day during business hours since I couldn't get it to work like I was wanting.  I'm sorry I couldn't be of more help.

Reply
  • The issue is the start and end date and time.  You are pulling data from the start of business on Monday until the end of business on Friday.  I'm not good enough with SQL to help you fix it.  I set up individual reports for each day during business hours since I couldn't get it to work like I was wanting.  I'm sorry I couldn't be of more help.

Children