Query to show average day, week and month in one query

I need a query that will show average data for day, week, month with the reults per line.

Data set would look like:
Device, Volume, SpaceUsed, DateTime
ServerA, C:\, 84.1, 2013-01-22
ServerA, C:\, 84.2, 2013-01-23
ServerA, C:\, 84.0, 2013-01-24
ServerA, C:\, 78.6, 2013-01-25
....
ServerB, C:\, 4.1, 2013-01-22
ServerB, C:\, 25.2, 2013-01-23
ServerB, C:\, 30.0, 2013-01-24
ServerB, C:\, 7.6, 2013-01-25
....

Results I would like to see (sample math):

Device   Vol     DayAVG    WkAVG    MnAVG
ServerA  C:\      82.73       82.70         75.0
ServerB  C:\      16.73       16.50         15.0

Possible?
edrz01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
You chould string together a CTE that contains the different averages, and return them in one set, like this:
Declare @dt date = '2013-01-25'
;
WITH avg_day as ( 
	SELECT Device, Volume, AVG(SpaceUsed) as av
	FROM #YourTable
	WHERE DateTime = @dt
	GROUP BY Device, Volume) 
WITH avg_week as ( 
	SELECT Device, Volume, AVG(SpaceUsed) as av
	FROM #YourTable
	WHERE DateTime BETWEEN @dt AND DATEADD(d, -7, @dt)
	GROUP BY Device, Volume) 
WITH avg_month as ( 
	SELECT Device, Volume, AVG(SpaceUsed) as av
	FROM #YourTable
	WHERE DateTime BETWEEN @dt AND DATEADD(m, -1, @dt)
	GROUP BY Device, Volume) 
SELECT yt.Device, yt.Vol, avg_day.av as DayAVG, avg_week.av as WkgAvg, avg_month as MnAvg
FROM #YourTable yt
	JOIN avg_day ad oN yt.device = ad.device AND yt.volume = ad.volume
	JOIN avg_week aw oN yt.device = aw.device AND yt.volume = aw.volume
	JOIN avg_month am oN yt.device = am.device AND yt.volume = am.volume
GROUP BY yt.Device, yt.Vol
ORDER BY yt.Device, yt.Vol

Open in new window

0
ArgentiCommented:
Or you can do it with outer joins:

use your_database;

declare @ParamDate as DATE = '2013-08-10'; -- put YOUR reference Date HERE

select A.*, B.DayAVG, C.WkAVG, D.MnAVG 
from 
(select distinct Device, Volume from Logs) A
left join (
	select Device, Volume, Avg(SpaceUsed) as DayAVG
	from Logs
	where RecDate = @ParamDate
	group by Device, Volume
) B on (B.Device = A.Device and B.Volume = A.Volume)
left join (
	select Device, Volume, AVG(SpaceUsed) as WkAVG
	from logs
	where (RecDate >= DATEADD(dd, -(DATEPART(dw, @ParamDate)-1), @ParamDate)) 
	  and (RecDate <= DATEADD(dd, 7-(DATEPART(dw, @ParamDate)), @ParamDate))
	group by Device, Volume
) C on (C.Device = A.Device and C.Volume = A.Volume)
left join (
	select Device, Volume, AVG(SpaceUsed) as MnAVG
	from logs
	where (RecDate >= datefromparts(year(@ParamDate),month(@ParamDate),1))
	  and (RecDate <= eomonth(@ParamDate))
	group by Device, Volume
) D on (D.Device = A.Device and D.Volume = A.Volume)

Open in new window


[later edit - corrections made] :)
0
jogosCommented:
If you want an average for a specific day and the week and month it belongs to then previous examples guide you the way.

If you want an overview of all your measures and see an evolution something like this will can be what you are looking for
;with CTE as(
SELECT Device, Volume,spaceused as spaceused
,measuredate
,substring(convert(varchar,measuredate,120),1,10) d
,substring(convert(varchar,measuredate,120),1,7) m
,substring(convert(varchar,measuredate,120),1,4) y
,substring(convert(varchar,measuredate,120),1,4)
  +'/'+convert(varchar,datepart(w,measuredate)) w
	FROM SpaceHist
)
select d.device,d.volume,d.d
,d.dayavg
,d.minspaceused,d.maxspaceused
,d.daysMeasured,d.FirstMeasure,d.LastMeasure
,w.w
,w.weekAvg
,m.m
,m.monthAvg
from 
(select device,volume
,sum(spaceused)/count(distinct(d)) as dayAvg
,min(spaceused) minspaceused
,max(spaceused) maxspaceused
, count(distinct(d)) as daysMeasured
, min(d) as FirstMeasure
, max(d) as LastMeasure
,d
,max(w) as w
,max(m) as m
,max(y) as y
from cte 
group by device,volume,d
) As d
inner join 
(select device,volume
,sum(spaceused)/count(distinct(d)) as weekAvg
,min(spaceused) minspaceused
,max(spaceused) maxspaceused
,w
,max(m) as m
,max(y) as y
from cte 
group by device,volume,w
) As w 
  on w.device=d.device 
     and w.volume = d.volume  
     and w.w = d.w
inner join 
(select device,volume
,sum(spaceused)/count(distinct(d)) as monthAvg
,min(spaceused) minspaceused
,max(spaceused) maxspaceused
,m
,max(y) as y
from cte 
group by device,volume,m
) As m on m.device=d.device 
          and m.volume = d.volume 
          and m.m = d.m
;

Open in new window

Example output at http://sqlfiddle.com/#!3/25278/49
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.