# 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?
###### 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.

Microsoft 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_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
``````
0
Commented:
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)
``````

[later edit - corrections made] :)
0
Commented:
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
;
``````
Example output at http://sqlfiddle.com/#!3/25278/49
0

Experts Exchange Solution brought to you by