Author 
Topic 

kb
Starting Member
New Zealand
3 Posts 
Posted  06/18/2006 : 17:45:49

I have a table of dates (dt, datetime) and values (svalue, float). The table could potentially contain 20 years or more of data at up to 1 value per minute (resolution required to 1 second). I want to generate mean values on an hourly, daily, weekly, monthly and yearly basis  not all at the same time though :). This is straightforward enough (e.g. for monthly averages for the last 24 months):
select avg(svalue), datepart(month,dt) from values where dt > dateadd(month,24,getdate()) group by datepart(month,dt) order by datepart(month,dt)
The problem is that I need to eventually display this data as a timebased line graph, with each value centred over the corresponding time period  so the datepart(month,dt) is no use to me. What I am looking for is generating the median point of each time period, together with my mean value, so I end up with something like:
Date .............. Mean  06/15/2006 24:00 ... 23.7 07/16/2006 12:00 ... 21.8 08/16/2006 12:00 ... 19.5
I can do this with some postquery coding on my application but it would be much easier (and faster) if I could get SQL Server to return the data in this format. The Date column in the resulting dataset needs to be a datetime.
Can anyone help here? If I can get this going for monthly averages than I should be able to sort out the yearly, monthly, daily and hourly ones using the same principles.
Thanks
Kevin 

khtan
In (Som, Ni, Yak)
Singapore
17642 Posts 
Posted  06/18/2006 : 20:08:46

for hourly average
select avg(svalue), [b]dateadd(hour, datediff(hour, 0, dt), 0)[b] as dte
from values
where dt > dateadd(month,24,getdate())
group by [b]dateadd(hour, datediff(hour, 0, dt), 0)[b]
order by dte
for daily average
select avg(svalue), [b]dateadd(day, datediff(day, 0, dt), 0)[b] as dte
from values
where dt > dateadd(month,24,getdate())
group by [b]dateadd(day, datediff(day, 0, dt), 0)[b]
order by dte
KH



Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7020 Posts 
Posted  06/18/2006 : 21:29:05

You need a table with the Midpoint, Start, and End for each time period that you want the average for. This code shows how to do it for months.
declare @StartDt datetime
select @StartDt = '20050101'
declare @t table(
MidPointDate datetime not null,
StartDate datetime not null,
EndDate datetime not null)
 Load a table with the Midpoint, Start, and End for each time period
insert into @t
select
MidPointDate =
Compute Mid point between Start Date and End Date
dateadd(ss,datediff(ss,a.StartDate,a.EndDate)/2,a.StartDate),
StartDate,
EndDate
from
(
select
StartDate = dateadd(mm,aa.number,@StartDt),
EndDate = dateadd(mm,aa.number+1,@StartDt)
from
 Function from SQL Team Script Library Forum
dbo.F_TABLE_NUMBER_RANGE(0,11) aa
) a
 Join Value table to Date table to get average for each period
select
b.MidPointDate,
AcerageValue = avg(isnull(a.value,0))
from
@t b
left join
MyValueTable a
on a.DT >= b.StartDate and a.DT < b.EndDate
group by
b.MidPointDate
order by
b.MidPointDate
CODO ERGO SUM 
Edited by  Michael Valentine Jones on 06/18/2006 21:36:47 


kb
Starting Member
New Zealand
3 Posts 
Posted  06/18/2006 : 21:32:46

Thanks KH
That's pretty close to what I was after. I want the dates to be the median of each aggregate group (i.e. midday instead of midnight for daily average) but that's easily achieved from the starting point you gave me:
select avg(svalue), dateadd(hour,12,dateadd(day, datediff(day, 0, dt), 0)) as dte from values where dt > dateadd(month,24,getdate()) group by dateadd(hour,12,dateadd(day, datediff(day, 0, dt), 0)) order by dte
I came up with a similar method using dateparts but your's is a litle faster to execute. I can use the same principle for hourly and weekly averages, but I'm still stuck for monthly averages. How do I get the median point of each month? I suppose I could just get the start and end of each month, cast to float, average and then cast back to datetime.
My testing has thrown up another problem though  there are sporadic gaps in the data and both your method and my attempts end up with no values for time periods with no data. What I really need is a complete set of consecutive time periods, with either values or NULLS alongside each one. I'm thinking along the lines of building a temp table containing all the required time periods, then iterating the table to populate the asociated average values. Here goes ....
Cheers
Kevin 


khtan
In (Som, Ni, Yak)
Singapore
17642 Posts 
Posted  06/18/2006 : 22:53:00

"My testing has thrown up another problem though  there are sporadic gaps in the data and both your method and my attempts end up with no values for time periods with no data. What I really need is a complete set of consecutive time periods, with either values or NULLS alongside each one. I'm thinking along the lines of building a temp table containing all the required time periods, then iterating the table to populate the asociated average values." Make use of the F_TABLE_DATE from MVJ here. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762&searchterms=F_TABLE_DATE and LEFT JOIN to your table
KH

Edited by  khtan on 06/18/2006 23:09:39 


kb
Starting Member
New Zealand
3 Posts 
Posted  06/28/2006 : 22:29:18

Thanks for the link KH.
I had a similar idea myself, but have been laid up with food poisoning for a while and in the meantime the goalposts have moved. Looks like I can now get away with using the crossover boundary, e.g. midnight last day of month, instead of the median of the month. This is, of course, much easier to accomplish :)
But the original problem is going to eat away at me so I'll come back to it sooner or later when I have chance and look at joining the date table in.
Thanks for taking the time to help out with this  much appreciated.
Kevin 


Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7020 Posts 
Posted  06/28/2006 : 23:03:37

quote: Originally posted by kb
Thanks for the link KH.
I had a similar idea myself, but have been laid up with food poisoning for a while and in the meantime the goalposts have moved. Looks like I can now get away with using the crossover boundary, e.g. midnight last day of month, instead of the median of the month. This is, of course, much easier to accomplish :)
But the original problem is going to eat away at me so I'll come back to it sooner or later when I have chance and look at joining the date table in.
Thanks for taking the time to help out with this  much appreciated.
Kevin
You seem to have never looked at the solution I posted.
I believe that it not only solves your problem exactly, but is easy to extend to other time periods, like quarters, years, etc.
Edit: I think I'm talking to myself here. I don't think he will ever see what I posted.
CODO ERGO SUM 
Edited by  Michael Valentine Jones on 06/29/2006 01:01:52 



Topic 


