sql question - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

sql question

Author

Topic

wireless
Starting Member

8 Posts

Posted - 2003-04-04 : 07:07:09

I'm trying to return an average from 24 hours worth of data but only
from a certain daily hour range. The database table has 24-hour data.

Let's say it's temperature data and I want the daily average
temperature between the hours of 8 am and 3 pm.

Something like this:

SELECT day, AVG(temp) as temp
FROM TableName
WHERE date >= '3/15/03 8 am' AND date >= '3/18/03 3 pm'
GROUP BY day

with, the output as something like this

Date........Daily_Avg
3/15/03.........52
3/16/03.........58
3/17/03.........62
3/18/03.........55

Is it possible to do this with an sql statement?

Obviously the statement above causes a problem. It returns discrete
days but the first day average will be from 8 am to midnight, the
in-between days will contain the 24-hour average, and the last day
will contain the average of midnight to 3 pm. Each day's average
should only contain the average between 8 am and 3 pm.

Any suggestions would be appreciated.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-04-04 : 07:14:48

You can use the datepart() function to specify which hours of the day you are interested in.

where datepart(hh,date) between 8 and 15

Jay White
{0}

joe.ingle
Starting Member

14 Posts

Posted - 2003-04-04 : 07:16:27

First thought would be to use a view to select your hourly data, then to select your average from the view.

Do you want to post table structure / sample data?

wireless
Starting Member

8 Posts

Posted - 2003-04-08 : 16:13:35

quote:

You can use the datepart() function to specify which hours of the day you are interested in.

where datepart(hh,date) between 8 and 15

Jay White
{0}

Jay, thanks, it really was that easy.

The thing is I never find this stuff in sql books or in sql online help. I had looked at datapart() but the examples don't use it in the context you expressed. I even looked in a couple of sql for sql server books this weekend at B&N after verifying the solution above and they didn't have this usage either.

These forums are great for getting arcane (to me) sql answers.

And nice web site by the way. I'm still contemplating the Neumann idea.

-David

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources