SQL Server: How to group by a datetime column based on a time interval (Such as within 2 hours)

Question

I want to group data based on the time interval, let us say group of 3 hours. How can I group a data within a time frame of data.

My data is like

DocId,    UserCode,     ProcessCode, ProcessDone
 1            1             10       21/11/2015 11:04:00  
 2            1             10       21/11/2015 12:14:00
 3            1             20       21/11/2015 11:04:00
 4            1             20       21/11/2015 11:54:00
 5            1             30       21/11/2015 13:04:00

For example in above data I want to group the data based on UserCode process using within frame of a time let us say 10-12.

like

UserCode, Process, Total
   1        10       1
   1        20       2

As this code total count is done based on Time between 10-12 and group by UserCode and ProcessCode.

Answer 1

Try this way:

select UserCode, ProcessCode, count(1) Total
from tab
where   convert(time,ProcessDone) between '10:00' and '12:00'
group by UserCode, ProcessCode 

Sql Fiddle Demo

or

select UserCode, ProcessCode, count(1) Total
from tab
where  DATEPART(hh,ProcessDone) > 10 and DATEPART(hh,ProcessDone) < 12
group by UserCode, ProcessCode 

Sql Fiddle Demo

or including date in group by

select UserCode, ProcessCode, count(1) Total
from tab
where   convert(time,ProcessDone) between '10:00' and '12:00'
group by UserCode, ProcessCode, convert(date,ProcessDone)

Sql Fiddle Demo