October 10, 2017 at 7:23 am
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
October 10, 2017 at 8:06 am
Is there some reason you can't just add two new columns with those formulas to the SELECT?
October 10, 2017 at 8:46 am
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
;
October 10, 2017 at 9:49 am
Cheers
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy