Group number of items into an hourly overview

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

SQL Server Development (2000)

Group number of items into an hourly overview

Author

Topic

banan
Starting Member

2 Posts

Posted - 2004-12-23 : 03:50:14

Hi people!

I'm trying to get an overview over how many orders we get during an hour, but I'm having serious trouble figuring out how to do this ..

My table; sOrder contains amongst others, these fields;

OrderId (int), OrderNo (int), OrderDateTime (datetime)

Now, everytime we get an order, it adds a new record with an id, number and timestamp.

How do I pull out something like the following;

OrderDateTime = '23/12/2004'

Hour Orders
0 10
1 23
2 9
.. ..
21 32
22 36
23 23

Or .. even better;

Date 0 1 2 .. 21 22 23
22/12/2004 21 12 5 12 15 18
23/12/2004 10 23 9 32 36 23

Any ideas? =) Thanks in advance!

b.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-23 : 03:52:44

select convert(varchar(8),dte,112), datepart(hh,dte), count(*)
from tbl
group by convert(varchar(8),dte,112), datepart(hh,dte)
order by convert(varchar(8),dte,112), datepart(hh,dte)

==========================================
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.

banan
Starting Member

2 Posts

Posted - 2004-12-23 : 04:13:41

Sweet - thanks nr :-)

Merry xmas.

b.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources