Hi,

You should help us to help you by delivering ddl and sample data in a consumable format. Because you're relatively new, I did it for you this time.

Here's a possible solution that you should test and understand before using it.

CREATE TABLE Checador(

tarjetaint,

horadatetime,

fechadate,

relojint)

INSERT Checador VALUES

(30 , '02/08/2010 08:14' , '02/08/2010' , 1 ),

(30 , '02/08/2010 12:57' , '02/08/2010' , 1 ),

(30 , '02/08/2010 14:01' , '02/08/2010' , 1 ),

(30 , '02/08/2010 19:21' , '02/08/2010' , 1 ),

(35 , '02/08/2010 07:23' , '02/08/2010' , 1 ),

(35 , '02/08/2010 13:05' , '02/08/2010' , 1 ),

(16 , '02/08/2010 12:58' , '02/08/2010' , 1 );

WITH CTE AS(

SELECT tarjeta,

hora,

fecha,

reloj,

ROW_NUMBER() OVER( PARTITION BY tarjeta, fecha ORDER BY hora) % 2 AS inicio,

(ROW_NUMBER() OVER( PARTITION BY tarjeta, fecha ORDER BY hora) + 1) / 2 AS numero

FROM Checador)

SELECT tarjeta,

MAX( CASE WHEN inicio = 1 THEN hora END) hora_inicio,

MAX( CASE WHEN inicio = 0 THEN hora END) hora_termino,

fecha,

reloj

FROM CTE

GROUP BY tarjeta,

fecha,

reloj,

numero

DROP TABLE Checador