Difference of hours query sql server

Navigation
2

I have the following data: 

2016-10-13 09:04:00.000  1
2016-10-13 20:33:00.000  4
2016-10-14 09:10:00.000  1
2016-10-14 21:04:00.000  4
2016-10-17 09:04:00.000  1
2016-10-17 19:50:00.000  4

How to tell the difference between hours between 4 and 1, example: 

2016-10-13 09:04:00.000  1
2016-10-13 20:33:00.000  4

My expected result would be around 14 hours, by example: 

20:33
21:33
22:33
23:33
00:33
01:33
02:33
03:33
04:33
05:33
06:33
07:33
08:33
09:04

That is, numero 4 é o final and numero 1 é o inicio , I need to know diferença entre final e o inicio .

    
asked by anonymous 18.10.2016 / 16:38

2 answers

4
  

Use DATEDIFF 

SELECT DATEDIFF ( HOUR , '2016-10-13 09:04:00.000' , '2016-10-13 20:33:00.000' ) Diferenca
    
18.10.2016 / 16:50
1

I tried to post in SQL Fiddle but it is giving error, here is my solution:

EXAMPLE TABLE: 

create table #dados (
    cpf int,
    data datetime ,
    tipo int
)

EXAMPLE INSERT: 

insert into #dados (cpf, data, tipo) values
(11111111111, '2016-10-13 09:04:00.000',  1),
(11111111111, '2016-10-13 20:33:00.000',  4),
(33333333333, '2016-10-14 09:10:00.000',  1),
(33333333333, '2016-10-14 21:04:00.000',  4),
(55555555555, '2016-10-17 09:04:00.000',  1),
(55555555555, '2016-10-17 19:50:00.000',  4)

I used the CPF to be able to pool: 

Agrupei por cpf, pegando a menor data e comparando com a maior data

        select cpf, datediff(hh, min(data), max(data)) as horas
            from #dados
            group by cpf

Acknowledgments: @Thiago Henrique and Leonardo Caetano

    
19.10.2016 / 23:56
How to import data from ACCESS to MYSQL Good practices Codeigniter