SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 How to display mean datetime with aggregates?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kb
Starting Member

New Zealand
3 Posts

Posted - 06/18/2006 :  17:45:49  Show Profile  Reply with Quote
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 time-based 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 post-query 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
17439 Posts

Posted - 06/18/2006 :  20:08:46  Show Profile  Reply with Quote
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

Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 06/18/2006 :  21:29:05  Show Profile  Reply with Quote
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
Go to Top of Page

kb
Starting Member

New Zealand
3 Posts

Posted - 06/18/2006 :  21:32:46  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17439 Posts

Posted - 06/18/2006 :  22:53:00  Show Profile  Reply with Quote
"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
Go to Top of Page

kb
Starting Member

New Zealand
3 Posts

Posted - 06/28/2006 :  22:29:18  Show Profile  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 06/28/2006 :  23:03:37  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000