Use a select for 1 column and another select for all others

Question

Use a select for 1 column and another select for all others

spyfly

Mr or Mrs. 500

Points: 593
More actions
November 2, 2017 at 6:30 am

#333513

Basically I am trying to use a select for 1 column and another select for all others. I want the COUNT(Interfaces.Caption) AS COUNT_of_Interface_Caption column to only show a count each time the Out_AverageBps/OutBandwidth)*100 and the (In_AverageBps/InBandwidth)*100 goes over 90%. So the end result will be the COUNT_of_Interface_Caption column will only show the number of times an interface when over 90% of usage during the past month and the other columns will show all data for the past month.
Thanks in advance
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 Nodes.NodeID AS NodeID,
  Interfaces.InterfaceID AS InterfaceID,
  Nodes.Caption AS NodeName,
  Interfaces.Caption AS Interface_Caption,
  AVG(Case InBandwidth
            When 0 Then 0
            Else (In_Averagebps/InBandwidth) * 100
            End) AS AVERAGE_of_Recv_Percent_Utilization,
  AVG(Case OutBandwidth
            When 0 Then 0
            Else (Out_Averagebps/OutBandwidth) * 100
            End) AS AVERAGE_of_Xmit_Percent_Utilization,
  COUNT(Interfaces.Caption) AS COUNT_of_Interface_Caption,
  Maxbps_In95,
  Maxbps_Out95,
  Maxbps_95,
  Recieve_Percentage = (Maxbps_In95 / Interfaces.InterfaceSpeed ) * 100,
                                Transmit_Percentage = (Maxbps_Out95 / Interfaces.InterfaceSpeed) * 100,
                                Interfaces.InterfaceSpeed AS Interface_Speed
FROM Nodes
LEFT JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID
LEFT JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.NodeID = Nodes.NodeID)
LEFT 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
GROUP BY InterfaceID
) TrafficStat ON Interfaces.InterfaceID = TrafficStat.InterfaceID

WHERE
(
(Nodes.TBU = 'TBU') AND
(Interfaces.Link_Type = 'Primary Link') AND
(
   (DatePart(Hour,DateTime) >= 7) AND
   (DatePart(Hour,DateTime) <= 16)) AND
(Case
            When InBandwidth+OutBandwidth=0 Then 0
            When InBandwidth=0 Then
            (Out_Averagebps/OutBandwidth) * 100
            When OutBandwidth=0 Then
            (In_Averagebps/InBandwidth) * 100
            Else
            Case
            When (Out_AverageBps/OutBandwidth) > (In_AverageBps/InBandwidth) Then
            (Out_AverageBps/OutBandwidth)*100
            Else
            (In_AverageBps/InBandwidth) *100
            End
            End >= 90)
)

GROUP BY Nodes.NodeID, Interfaces.InterfaceID, Nodes.Caption, Interfaces.Caption, Interfaces.InterfaceSpeed, Maxbps_In95, Maxbps_Out95, Maxbps_95

ORDER BY 3 ASC, 4 ASC

Viewing 5 posts - 1 through 4 (of 4 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

SUM( CASE WHEN Out_AverageBps/OutBandwidth*100.0 > 90 AND In_AverageBps/InBandwidth*100.0 > 90 THEN 1 ELSE 0 END ) AS COUNT_of_Interface_Caption

John

spyfly Mr or Mrs. 500 Points: 593 More actions · Answer 2

@john-2,

I will give it a try, not going to lie I am not an SQL pro at all where would I put this statement? What do I replace?

Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Replace the line that ends "AS COUNT_of_Interface_Caption". You need to make sure you understand it though, and not just use it because someone off the internet gave you it. It'll be you supporting the code, after all.

John

spyfly Mr or Mrs. 500 Points: 593 More actions · Answer 4

John Mitchell-245523 - Thursday, November 2, 2017 8:03 AM
Replace the line that ends "AS COUNT_of_Interface_Caption". You need to make sure you understand it though, and not just use it because someone off the internet gave you it. It'll be you supporting the code, after all.
John

I thank you for that, I am watching youtube videos now on the SUM Case and it starting to make sense