Min, Max, Avg per server for the month

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Min, Max, Avg per server for the month

Author

Topic

jvnvcigor
Starting Member

5 Posts

Posted - 2014-01-09 : 22:34:20

Hi Guys/Girls,

Been rackign my head with this one for a couple days now and I have officialy exausted my google searching.

I have the following Table of Data (Entire Month):

Date ,Server Name,Min CPU %,Avg CPU %,Max CPU %
01/01/2014,Server A ,10 ,7 ,15
01/01/2014,Server B ,43 ,25 ,66
01/01/2014,Server C ,45 ,48 ,67
02/01/2014,Server A ,58 ,5 ,98
02/01/2014,Server B ,22 ,29 ,80
02/01/2014,Server C ,33 ,42 ,67

What I need is output like the below:

Month ,Server Name, Min CPU%, Avg CPU %, Max CPU %
JANUARY ,Server A , 10 , 6 , 98
JANUARY ,Server B , 22 , 27 , 80
JANUARY ,Server C , 33 , 45 , 67

Important thing being, I want the MIN, AVG, MAX for Entire Month, by Server Name

Any suggestions?

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-01-09 : 23:07:05

select (select distinct datename(mm,date) from @tab where datename(mm,date) like 'J%') as 'Month',
servername,
min(MinCPU) as 'Min CPU %',
avg(AvgCPU) as 'Avg CPU %',
max(MaxCPU) as 'Max CPU %'
from @tab
group by ServerName

veeranjaneyulu

jvnvcigor
Starting Member

5 Posts

Posted - 2014-01-09 : 23:20:49

Hi Veeranjaneyulu

Thank you very much for your help, this has worked exactly as I wanted.

Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-10 : 06:14:17

quote:
Originally posted by jvnvcigor

Hi Veeranjaneyulu

Thank you very much for your help, this has worked exactly as I wanted.

Thanks again.

How?
I cant see any grouping on month so would not expect it to give month level totals. DId you try for earlier year? It will work for 2014 as its still in Jan. Try for 2013 and see if it works

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-10 : 06:15:53

I think it should be this?


select DATEADD(mm,DATEDIFF(mm,0,[Date]),0) AS MonthDate,
servername,
min(MinCPU) as 'Min CPU %',
avg(AvgCPU) as 'Avg CPU %',
max(MaxCPU) as 'Max CPU %'
from @tab
group by DATEADD(mm,DATEDIFF(mm,0,[Date]),0),ServerName

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jvnvcigor
Starting Member

5 Posts

Posted - 2014-01-12 : 16:42:42

SELECT
DATENAME(Month,[Date Time]) AS [Month],
[Server Name],
MIN([Min CPU %]) AS [Min CPU %],
ROUND(AVG([Avg CPU %]), 2) AS [Avg CPU %],
MAX([Max CPU %]) AS [Max CPU %],
FROM @Table

GROUP BY DATENAME(Month,[Date Time]) AS [Month],
[Server Name],

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-13 : 05:56:36

quote:
Originally posted by jvnvcigor

SELECT
DATENAME(Month,[Date Time]) AS [Month],
[Server Name],
MIN([Min CPU %]) AS [Min CPU %],
ROUND(AVG([Avg CPU %]), 2) AS [Avg CPU %],
MAX([Max CPU %]) AS [Max CPU %],
FROM @Table

GROUP BY DATENAME(Month,[Date Time]) AS [Month],
[Server Name],

WOnt work as expected if there are data for more than 1 year

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jvnvcigor
Starting Member

5 Posts

Posted - 2014-01-13 : 06:39:42

My data is all one year so that's not an issue.

But what I have discovered is that it's not giving me a unique values.

That is I have the same Server come up for a month twice instead of once.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-13 : 07:42:26

quote:
Originally posted by jvnvcigor

My data is all one year so that's not an issue.

But what I have discovered is that it's not giving me a unique values.

That is I have the same Server come up for a month twice instead of once.


select DATEADD(mm,DATEDIFF(mm,0,[Date]),0) AS MonthDate,
servername,
min(MinCPU) as 'Min CPU %',
avg(AvgCPU) as 'Avg CPU %',
max(MaxCPU) as 'Max CPU %'
from (select row-number() over (partition by DATEADD(mm,DATEDIFF(mm,0,[Date]),0) order by Date) As Rn,* from @tab)t
where Rn=1
group by DATEADD(mm,DATEDIFF(mm,0,[Date]),0),ServerName

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jvnvcigor
Starting Member

5 Posts

Posted - 2014-01-13 : 15:46:20

That didnt work, returned only 3 results one of which was a double aswell

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-14 : 04:19:41

quote:
Originally posted by jvnvcigor

That didnt work, returned only 3 results one of which was a double aswell


select DATEADD(mm,DATEDIFF(mm,0,[Date]),0) AS MonthDate,
servername,
min(MinCPU) as 'Min CPU %',
avg(AvgCPU) as 'Avg CPU %',
max(MaxCPU) as 'Max CPU %'
from (select row-number() over (partition by DATEADD(mm,DATEDIFF(mm,0,[Date]),0),Servername order by Date) As Rn,* from @tab)t
where Rn=1
group by DATEADD(mm,DATEDIFF(mm,0,[Date]),0),ServerName

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources