hourly results - 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

General SQL Server Forums

New to SQL Server Programming

hourly results

Author

Topic

arusu
Yak Posting Veteran

60 Posts

Posted - 2010-09-28 : 17:02:45

I have a table with a datetime and a result and i want to make a view that will show the hourly result. So one column with the datetime showing hourly and one column with a result

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2010-09-28 : 17:11:57

Could you give an example with sample data of what you have and what you'd like to see?

Some days you're the dog, and some days you're the fire hydrant.

arusu
Yak Posting Veteran

60 Posts

Posted - 2010-09-28 : 17:34:16

ok so i have

date result
09/30/2010 09:30 11
09/30/2010 09:31 3
09/31/2010 08:30 4
09/30/2010 09:30 5

so the view should show:

date result
09/30/2010 09:00 14
09/31/2010 08:00 4
09/30/2010 09:00 5

so basically it's showing hourly results and if there is more than one result in an hour, it's added up

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-09-28 : 17:46:32

[code]select
dateadd(hh,datediff(hh,0,MyDateTime),0) as MyHour,
sum(result) as MyResult
from
MyTable
group by
dateadd(hh,datediff(hh,0,MyDateTime),0)
order by
dateadd(hh,datediff(hh,0,MyDateTime),0)[/code]

Finding the Start of Time Periods
The following links have functions that will find the start of Century, Decade, Year, Quarter, Month, Week, Day, Hour, 30 Minutes, 20 Minutes, 15 Minutes, 10 Minutes , 5 Minutes , x number of Minutes ,Minute , or Second.
Start of Time Period Functions:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755
Start of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

Date/Time Info and Script Links
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

CODO ERGO SUM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-30 : 12:18:56

quote:
Originally posted by arusu

ok so i have

date result
09/30/2010 09:30 11
09/30/2010 09:31 3
09/31/2010 08:30 4
09/30/2010 09:30 5

so the view should show:

date result
09/30/2010 09:00 14
09/31/2010 08:00 4
09/30/2010 09:00 5

so basically it's showing hourly results and if there is more than one result in an hour, it's added up

see

http://visakhm.blogspot.com/2010/02/aggregating-data-over-time-slots.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources