Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Group or sum date column (ATTLOG) Expand / Collapse
Author
Message
Posted Thursday, October 18, 2012 11:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 31, 2012 9:46 AM
Points: 17, Visits: 57
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....?????
Post #1374519
Posted Thursday, October 18, 2012 12:15 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:27 PM
Points: 2,763, Visits: 5,899
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(
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 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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1374533
Posted Thursday, October 25, 2012 6:11 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:06 AM
Points: 83, Visits: 296
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

Post #1376946
Posted Thursday, October 25, 2012 7:51 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:27 PM
Points: 2,763, Visits: 5,899
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1376995
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse