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.
Author 
Topic 
kb
Starting Member
3 Posts 
Posted  20060618 : 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)
17689 Posts 
Posted  20060618 : 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)
7020 Posts 
Posted  20060618 : 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 


kb
Starting Member
3 Posts 
Posted  20060618 : 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)
17689 Posts 
Posted  20060618 : 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



kb
Starting Member
3 Posts 
Posted  20060628 : 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)
7020 Posts 
Posted  20060628 : 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 






