SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
spyfly
spyfly
SSC Veteran
SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)

Group: General Forum Members
Points: 269 Visits: 48
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

John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (130K reputation)SSC Guru (130K reputation)SSC Guru (130K reputation)SSC Guru (130K reputation)SSC Guru (130K reputation)SSC Guru (130K reputation)SSC Guru (130K reputation)SSC Guru (130K reputation)

Group: General Forum Members
Points: 130765 Visits: 19090
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
spyfly
SSC Veteran
SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)

Group: General Forum Members
Points: 269 Visits: 48
@John,

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
John Mitchell-245523
SSC Guru
SSC Guru (130K reputation)SSC Guru (130K reputation)SSC Guru (130K reputation)SSC Guru (130K reputation)SSC Guru (130K reputation)SSC Guru (130K reputation)SSC Guru (130K reputation)SSC Guru (130K reputation)

Group: General Forum Members
Points: 130765 Visits: 19090
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
spyfly
SSC Veteran
SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)

Group: General Forum Members
Points: 269 Visits: 48
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search