Creating a percentage column based on 2 know column values

  • spyfly

    Mr or Mrs. 500

    Points: 593

    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: 30356

    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: 715912

    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

    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: 715912

  • This was removed by the editor as SPAM

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply