Creating a percentage column based on 2 know column values

• spyfly

  Mr or Mrs. 500

  Points: 593

  More actions

  October 10, 2017 at 7:23 am

  #324071

  I am trying to create a query that will create a percentage based on 2 know column values

  I want to make a column of percentage based on
  Maxbps_In95 / Interfaces.InterfaceSpeed and
  Maxbps_Out95 / Interfaces.InterfaceSpeed
  So really I want 2 extra cloumns

  Thanks

  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 (1=1)  AND 
  (  (Nodes.TBU = 'TBU') AND   (Interfaces.Link_Type LIKE '%Primary%') )

  ORDER BY NodeName, Interface_Caption

• ZZartin

  SSC-Dedicated

  Points: 30921

  More actions

  October 10, 2017 at 8:06 am

  #1963000

  Is there some reason you can't just add two new columns with those formulas to the SELECT?

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738083

  More actions

  October 10, 2017 at 8:46 am

  #1963024

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

• spyfly

  Mr or Mrs. 500

  Points: 593

  More actions

  October 10, 2017 at 9:31 am

  #1963045

  thanks @steve-2 Jones, that worked perfectly... I am new to this I am sure you can tell, I swore I tried what you did for me but I guess I have missing something

  thanks again

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738083

  More actions

  October 10, 2017 at 9:49 am

  #1963052

  Cheers