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
;