Creating a column using a select where statement

Question

Creating a column using a select where statement

spyfly

Mr or Mrs. 500

Points: 593
More actions
December 8, 2017 at 1:10 pm

#341223

I am trying to create columns by a time frame where column a would be from 8am to 10am, column b would be from 10am to 12pm, etc..
This is what I have so far, I would like to create a column using the AS Average_of_Rev_Percent_Utilization, AVERAGE_of_Xmit_Percent_Utilization, COUNT_of_Interface_Caption for each of my time slot windows. (I know there where statements in the Case function are not valid I am just giving you an example of what I am trying to pull out)
AVG(Case InBandwidth
                                         When 0 Then 0
                                         Else (In_Averagebps/InBandwidth) * 100
                                         End) ""WHERE ((DatePart(Hour,DateTime) >= 8) AND (DatePart(Hour,DateTime) <= 10))AS AVERAGE_of_Recv_Percent_Utilization_0800_1000,
AVG(Case InBandwidth
                                         When 0 Then 0
                                         Else (In_Averagebps/InBandwidth) * 100
                                         End) ""WHERE ((DatePart(Hour,DateTime) >= 10) AND (DatePart(Hour,DateTime) <= 12))AS AVERAGE_of_Recv_Percent_Utilization_1000_1200,
Here is the code that I am using in my query
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,
  SUM(    CASE
            WHEN Out_MAXBps/OutBandwidth*100.0 > 90 OR In_MAXBps/InBandwidth*100.0 > 90 THEN 1
            ELSE 0
        END
        ) AS COUNT_of_Interface_Caption,


thanks in advance

Viewing 3 posts - 1 through 2 (of 2 total)

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

Bert-701015 SSCommitted Points: 1615 More actions · Answer 1

First, based on the code you've posted, data for the 10am hour will duplicated in both time frames you've defined. There was another article posted by Bob Hovious today on this very subject: http://www.sqlservercentral.com/articles/Date+Manipulation/145336/

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

Bert-701015 - Friday, December 8, 2017 1:58 PM
First, based on the code you've posted, data for the 10am hour will duplicated in both time frames you've defined. There was another article posted by Bob Hovious today on this very subject: http://www.sqlservercentral.com/articles/Date+Manipulation/145336/

Thank you I will read over this