Group or sum date column (ATTLOG)

Question

Group or sum date column (ATTLOG)

x_jhofran_x

Valued Member

Points: 71
More actions
October 18, 2012 at 11:41 am

#263440

FILE ATTLOG.txt
TARJETA | HORA | FECHA | RELOJ
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
RESULT IN SQL
TARJETA | HORA_INICIO | HORA_TERMINO | FECHA | RELOJ
30 | 02/08/2010 08:14 | 02/08/2010 12:57 | 02/08/2010 | 1
30 | 02/08/2010 14:01 | 02/08/2010 19:21 | 02/08/2010 | 1
35 | 02/08/2010 07:23 | 02/08/2010 13:05 | 02/08/2010 | 1
16 | 02/08/2010 12:58 | NULL | 02/08/2010 | 1
HOW CAN I DO IN SQL....?????

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

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

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

BriPan SSCrazy Points: 2061 More actions · Answer 2

Same thing Using Join in CTE

declare @Checador TABLE (

i int identity (1,1),

TARJETA int,

HORA datetime,

FECHA date,

RELOJ int)

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 ch.i,ch.TARJETA ,ch.HORA as HORA_INICIO ,ch1.HORA as HORA_TERMINO ,ch.FECHA ,ch.RELOJ

from @Checador ch

left join @Checador ch1

on ch.i+1=ch1.i

)

select TARJETA,HORA_INICIO,HORA_TERMINO,FECHA,RELOJ

from cte where i%2<>0

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

But you're assuming that an identity column is available and that it has no gaps.

You're assuming as well that the data is being inserted to the table in the correct order.

That's why I used the ROW_NUMBER().

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2