Agrupar a quantidade de registros por intervalo de hora

[twpesquisa 1]

Bom Dia!

Amigos,

 

tenho uma tabela que possui duas colunas, DATA e outra de HORA.

Necessito agrupar os registros em uma DATA em intervalos de hora em hora com a quantidade.

 

Exemplo:

 

27/05/2013 | 07:00:00 - 07:59:59 | 9

27/05/2013 | 08:00:00 - 08:59:59 | 18

27/05/2013 | 09:00:00 - 09:59:59 | 32

 

 

Como posso montar o select ?

[Motta 645]

Use as funções de agrupamento e as funções de data/hora.

[twpesquisa 1]

Montei da forma abaixo e deu certo.

 

select Data, InitialUserID as 'Ramal', Case (HOUR(Time)) 
						WHEN 1 THEN '01:00:00 as 01:59:59'
						WHEN 2 THEN '02:00:00 as 02:59:59'
						WHEN 3 THEN '03:00:00 as 03:59:59'
						WHEN 4 THEN '04:00:00 as 04:59:59'
						WHEN 5 THEN '05:00:00 as 05:59:59'						
						WHEN 6 THEN '06:00:00 as 06:59:59'
						WHEN 7 THEN '07:00:00 as 07:59:59'
						WHEN 8 THEN '08:00:00 as 08:59:59'
						WHEN 9 THEN '09:00:00 as 09:59:59'
						WHEN 10 THEN '10:00:00 as 10:59:59'
						WHEN 11 THEN '11:00:00 as 11:59:59'
						WHEN 12 THEN '12:00:00 as 12:59:59'
						WHEN 13 THEN '13:00:00 as 13:59:59'
						WHEN 14 THEN '14:00:00 as 14:59:59'
						WHEN 15 THEN '15:00:00 as 15:59:59'
						WHEN 16 THEN '16:00:00 as 16:59:59'
						WHEN 17 THEN '17:00:00 as 17:59:59'
						WHEN 18 THEN '18:00:00 as 18:59:59'						
						WHEN 19 THEN '19:00:00 as 19:59:59'
						WHEN 20 THEN '20:00:00 as 20:59:59'
						WHEN 21 THEN '21:00:00 as 21:59:59'
						WHEN 22 THEN '22:00:00 as 22:59:59'
						WHEN 23 THEN '23:00:00 as 23:59:59'
						WHEN 24 THEN '24:00:00 as 24:59:59'
						ELSE 'Sem registro'
						END as 'Intervalo', count(*) as 'Ligações recebida'
from bilhetagem
where InitialUserID = '4444'
and CommunicationType = 'Incoming'
group by Data, HOUR(Time)

[Motta 645]

beleza mas

WHEN 24 THEN '24:00:00 as 24:59:59'

 

non ecxiste ...

 

seria

WHEN 0 THEN '00:00:00 as 00:59:59'