0 Replies Latest reply on Aug 2, 2011 4:26 PM by Erin0

    "95th Percentile Traffic Rate - Last Month" Modify to be only M-F

    Erin0

      Hello,

      In the canned report "95th Percentile Traffic Rate - Last Month" I'm looking for a way to limit the days to Monday through Friday (or exclude Saturday and Sunday).  Any thoughts?

       

      Thank you

       

      Canned Report Code:

      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))

      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