0 Replies Latest reply on Oct 25, 2018 10:12 AM by lmissionsadm

    95 % Report Question

    lmissionsadm

      Hi

       

      I've made a custom table report for 95 Percentile for our main sites (IsWAN) that works but im trying to make it report only on the working hours of the day 8am - 6 pm and struggling to do so

       

      Please can someone help me filter only on the core working hours of the day 8am - 6 pm please

       

      SET NOCOUNT OFF
      SET ROWCOUNT 0

      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 Interfaces.InterfaceId,
      Nodes.NodeID,
      Nodes.Caption AS NodeName,
       
      Interfaces.Caption AS Interface_Caption,
      Interfaces.InterfaceIcon AS Interface_Icon,
      Interfaces.InterfaceSpeed,
      dbo.GetInBps95th(InterfaceID, @StartDate, @EndDate) / Interfaces.InterfaceSpeed * 100 AS PercentIN,
      dbo.GetOutBps95th(InterfaceID, @StartDate, @EndDate)/ Interfaces.InterfaceSpeed * 100 AS PercentOUT,
      dbo.GetMaxBps95th(InterfaceID, @StartDate, @EndDate)/ Interfaces.InterfaceSpeed * 100 AS Maxbps_95
      FROM Nodes
      INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)
      WHERE (Interfaces.IsWAN = 1)
        AND (Interfaces.InterfaceSpeed != 0)
      ORDER BY PercentIN, Interface_Caption