Selecting count of record by hour with zero values

[BbEsy]

How simple I can make query for MSSQL for selecting number of records step by 1 hour.. Including zero value..
I need return liek this..


2010/06/24 01:00 ; 2
2010/06/24 02:00 ; 0
2010/06/24 03:00 ; 10
2010/06/24 04:00 ; 11


thanks for all replies

[mwasif]

Moved to MSSQL forum.

[gpl]

How simple I can make query for MSSQL for selecting number of records step by 1 hour.. Including zero value..
I need return liek this..


2010/06/24 01:00 ; 2
2010/06/24 02:00 ; 0
2010/06/24 03:00 ; 10
2010/06/24 04:00 ; 11


thanks for all replies

This can be simple
I assume you know how to extract the hour from the time, so you need to make sure that you have a full list of the possible hours in a day, simply create a table called AllHours and give it a numeric column called EachHour and insert the numbers 0 to 23 into it.

Now your query should be against the AllHours table and left join against your data table on the hours value. The COUNT will now identify all those hours which failed to match against your data table and give a value of zero.

[viktorka]

You my try:

SELECT * FROM Table1 WHERE DATEPART(nn, ColDate) = 0

It should select only records where minutes = 0 - only hours