Count/Avg/Sum per hour without a cursor

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)

Count/Avg/Sum per hour without a cursor

Author

Topic

k420
Starting Member

32 Posts

Posted - 2006-08-04 : 12:21:50

Hi All,

I have a requirement to get a list of counts or averages or sums for readings within a table on a per hour basis. The table has basic fields on an ID, a timestamp and a reading.

The problem I'm having is that I want to see every hour for a day regardless of whether it has readings or not. I can do this with a cursor no problem by simply looping from 0 to 23 but I want to know if its possible without a cursor.

Having looked through the forum I've found various different ways to do the whole group by hour thing e.g.

GROUP BY DATEADD(hh, DatePart(hour, Flow.dtDateTime), Convert(datetime, Convert(varchar(8), Flow.dtDateTime, 112)))

but this only lists the hours in the results if there were any readings for it in the first place.

Can anyone point me in the right direction or am I stuck with cursors?

Thanks in advance for your help

K

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-04 : 12:30:23

Do you have v2005?
If so it's simple with a cte
see
http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-common-table-expressions/

even with v2000

(select i = i1.i + i2.i + i3.i + i4.i + i5.i + i6.i
from
(select i = 0 union select 1) as i1 ,
(select i = 0 union select 2) as i2 ,
(select i = 0 union select 4) as i3 ,
(select i = 0 union select 8) as i4 ,
(select i = 0 union select 16) as i5 ,
(select i = 0 union select 32) as i6
) as ints
where ints.i between 1 and 24

join to that to get the hours.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-04 : 12:35:33

Post some sample data and expected results

Srinika

k420
Starting Member

32 Posts

Posted - 2006-08-05 : 04:43:49

Hi Guys,

I had a friend looking into this for me as well and he found a solution at:

[url]http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21845923.html[/url]

Thanks for looking into this for me.

K

Code Carpenter
Starting Member

4 Posts

Posted - 2006-08-07 : 10:09:46

So let me get this straight. You want us to pay 10 dollars to get the solution to YOUR problem?

You might be a member of that group, but not everyone here is.

Please provide the solution for the rest of us...

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-08-07 : 10:32:10

quote:
Originally posted by Code Carpenter

So let me get this straight. You want us to pay 10 dollars to get the solution to YOUR problem?

You might be a member of that group, but not everyone here is.

Please provide the solution for the rest of us...

Do what Nigel suggested: left join a tally from 0 to 23 to your hour-grouped data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources