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

  • spyfly

    Mr or Mrs. 500

    Points: 533

    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

    SSC Guru

    Points: 148246

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

    @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: 148246

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

    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

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

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