1 Reply Latest reply on Feb 21, 2013 2:17 AM by LadaVarga

    Please help me to the SQL query modification for a specified period of time

    keegen

      Please help me to the SQL query modification for a specified period of time, for example, from 2012/12/01 to 2012/12/30,thanks!

       

       

      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