Records per Hour - 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 2005 Forums

Transact-SQL (2005)

Records per Hour

Author

Topic

Heinduplessis
Starting Member

23 Posts

Posted - 2009-09-18 : 08:44:29

Hi all

I can't find an elegant solution for this seemingly simple problem.

I have a table (Job) with a field that includes a timestamp.

I need to draw a report for the number of records in table Job per day / hour / minute for the day / week month.

Something like this:


Date Time           JobCount
2009/09/18 00:00    0
2009/09/18 01:00    1
2009/09/18 02:00    5
2009/09/18 03:00    29

etc.

I can't do a simple group by on the Job table, since there might be hours where there are no Jobs.

Am I forced to join in a calendar table?

Many thanks

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-09-18 : 09:03:07

Joining to a calendar-table or a table with a sequential number (with no gaps) is the way to go if you also need hours where there are no jobs.

- Lumbago

Heinduplessis
Starting Member

23 Posts

Posted - 2009-09-18 : 10:22:41

Thanks, yeah seems I'll have to. Was hoping for a fancy date calculation based on record numbers or something ;)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources