0 Replies Latest reply on Oct 10, 2011 11:57 AM by darrenroback

    95th Percentile Historical Traffic Report - Business Hours Only

    darrenroback

      I am trying to modify the following SQL code so it will only run the report (and calculate the 95th percentile) during normal business hours.

      DECLARE @StartDate DateTime
      DECLARE @EndDate DateTime

      SET @StartDate = CAST((ROUND(CAST(GetDate() - 7 AS FLOAT), 0, 1)) as datetime)
      SET @EndDate = GetDate()

      set nocount on
      create table #tmpJoin (fromDate datetime, toDate datetime)
      insert into #tmpJoin values(@StartDate,@EndDate)
      set nocount off

      SELECT Interfaces.InterfaceId,
      Nodes.NodeID,
      Nodes.Caption AS NodeName,
      Nodes.VendorIcon AS Vendor_Icon,      
      Interfaces.Caption AS Interface_Caption,
      Interfaces.InterfaceIcon AS Interface_Icon,
      Maxbps_In95,
      Maxbps_Out95,
      Maxbps_95
      FROM Nodes
      INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)
      INNER JOIN   
      (
          SELECT InterfaceID, dbo.GetInBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_In95
          FROM (
              SELECT DISTINCT A.InterfaceID
              FROM dbo.InterfaceTraffic A JOIN #tmpJoin ON A.DateTime >= #tmpJoin.fromDate AND A.DateTime <=  #tmpJoin.toDate
          ) AS AA
      ) as RESULT_IN ON (Interfaces.InterfaceID = RESULT_IN.InterfaceID)
      INNER JOIN   
      (
          SELECT InterfaceID, dbo.GetOutBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95
          FROM (
              SELECT DISTINCT A.InterfaceID
              FROM dbo.InterfaceTraffic A
              JOIN #tmpJoin ON A.DateTime >= #tmpJoin.fromDate AND A.DateTime <=  #tmpJoin.toDate
          ) AS AA
      ) as RESULT_OUT     ON (Interfaces.InterfaceID = RESULT_OUT.InterfaceID)
      INNER JOIN   
      (
          SELECT InterfaceID, dbo.GetMaxBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_95
          FROM (
              SELECT DISTINCT A.InterfaceID
              FROM dbo.InterfaceTraffic A
              JOIN #tmpJoin ON A.DateTime >= #tmpJoin.fromDate AND A.DateTime <=  #tmpJoin.toDate
          ) AS AA
      ) as RESULT_MAX     ON (Interfaces.InterfaceID = RESULT_MAX.InterfaceID)
       ORDER BY NodeName, Interface_Caption

      Drop table #tmpJoin


      Can anyone provide some assistance?

      Thanks!

      Darren