4 Replies Latest reply on Oct 25, 2011 5:14 PM by klc2009

    Need help with SQL in report

    klc2009

      I am trying to create a new 95th Percentile traffic report that does the following each month for the last month:

      1. Gives me the top ten only

      2. Limits the report to Serial interfaces

      How would I adjust the below SQL to do what I need?

      TIA

      klc2009

      -----------------------------------------------------

      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

        • Re: Need help with SQL in report
          netlogix

          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 top 10 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)
           where Interfaces.Caption like 'Serial%'
           ORDER BY NodeName, Interface_Caption

          Drop table #tmpJoin

          1 of 1 people found this helpful