TSQL query help structuring results

Question

I have a table with the following columns:

timestamp | value | desc

example of the data:

2014-01-27 10:00:00.000 | 100 | 101
2014-01-27 10:00:00.000 | 105 | 101
2014-01-27 11:00:00.000 | 160 | 101
2014-01-27 12:00:00.000 | 200 | 101
...
...
2014-01-28 10:00:00.000 | 226 | 101
2014-01-28 10:00:00.000 | 325 | 101
2014-01-28 11:00:00.000 | 145 | 101

what I would like to obtain is a grouping by the hour part but without merging the period interval. So that the result will be like this (in the select I will pass a date interval and a condition on the description like desc = '101':

Structure:

hour | count

Data:

10 | 2    (referring to the 20140127)
11 | 1    (referring to the 20140127)
12 | 1    (referring to the 20140127)
...
...
10 | 2    (referring to the 20140128)
11 | 1    (referring to the 20140128)

I thought about using a cursor but I was wondering if it is possible to achieve this result without it.

I'm using SQL server 2012 SP1.

Thanks for your attention.

Bye, F.

Answer 1

Try this:-

SELECT Count(*)                  AS [Count], 
       Datepart(hour, timestamp) AS [Hour] 
FROM   yourtable 
GROUP  BY CONVERT(DATE, timestamp), 
          Datepart(hour, timestamp) 
ORDER  BY CONVERT(DATE, timestamp)