2 Replies Latest reply on Jun 6, 2018 9:03 PM by sirpaw

    Help with create percentage from SQL Query

    spyfly

      I would like to create a percentage column in a report using the following query, basically I want to do

       

      Interfaces.InterfaceSpeed / Maxbps_In95 * 100

      Interfaces.InterfaceSpeed / Maxbps_Out95 * 100

       

      here is the current query

      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,
        Nodes.VendorIcon AS Vendor_Icon,
        Interfaces.Caption AS Interface_Caption,
        Interfaces.InterfaceIcon AS Interface_Icon,
        Maxbps_In95,
        Maxbps_Out95,
        Maxbps_95,
                                      Interfaces.InterfaceSpeed AS Interface_Speed                               
      FROM Nodes
      INNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID
      INNER JOIN (
      SELECT InterfaceID,
         dbo.GetInBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_In95,
         dbo.GetOutBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95,
         dbo.GetMaxBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_95
      FROM InterfaceTraffic
      WHERE InterfaceTraffic.DateTime >= @StartDate AND InterfaceTraffic.DateTime <= @EndDate
      GROUP BY InterfaceID
      ) TrafficStat
      ON Interfaces.InterfaceID = TrafficStat.InterfaceID
      WHERE ${LIMITATION}  AND 
      (  (Nodes.TBU = 'TBU') AND   (Interfaces.Link_Type LIKE '%Primary%') )

      ORDER BY NodeName, Interface_Caption