Registros distintos en una misma fila

[chelodelsur]

Hola

Mi lío es le siguiente, tengo una tabla que guarda los datos de login y logoff de usuarios, entonces necesito levantar una consulta que me diga por cada usuario la fecha que estuvo logeado, la hora inicio, la hora termino, y el tiempo de logon.
Pues bien, tengo este select pero no logro que me devuelva en la misma line los datos de fecha, login, logoff. Lo que hace es traerme dos veces la fecha, en una me muestra el login y en la otra el logoff

fecha ------------- nombres -------apellidos --------horalogin ---------horalogoff
2008-12-01 00:00:00 NATACHA ZUñIGA NAVARRO 2008-12-01 12:27:00 0000-00-00 00:00:00
2008-12-01 00:00:00 NATACHA ZUñIGA NAVARRO 0000-00-00 00:00:00 2008-12-01 22:03:00
2008-12-02 00:00:00 NATACHA ZUñIGA NAVARRO 2008-12-02 12:33:00 0000-00-00 00:00:00
2008-12-02 00:00:00 NATACHA ZUñIGA NAVARRO 0000-00-00 00:00:00 2008-12-02 12:37:00

Código:

Select
       b.fecha,
       a.nombres, 
       a.apellidos,
       b.horalogin,
       b.horalogoff
From razontime b
Left Join usuarios a
On b.idagente= a.userkey
Where b.idagente='224'
And hasrazon='0'

he probado con group pero me desaparece o el login o el logoff

Como siempre se agradece su ayuda

Chelo

[jurena]

SELECT a.nombre, a.apellidos, DATE( b.fecha ) , SEC_TO_TIME( SUM( TIME_TO_SEC( TIMEDIFF( b.horalogoff, b.horalogin ) ) ) ) TIEMPO
FROM usuarios a
LEFT JOIN razontime b ON a.userkey = b.idagente
GROUP BY DATE( b.fecha ) , a.userkey

Prueba esto, pero creo que tendrás algún problema para delimitar los datos de quienes inicien el login en un día y hagan el logoff al día siguiente.
No sé por qué usas un datetime para fecha, cuando sólo guardas año, mes y día. Usa un date, y evitarás tener que usar la función date(), como yo he hecho.

[chelodelsur]

Como siempre MUchas Gracias Juerna por la disposición.

Mira lo del tipo de dato del campo fecha, es porque la aplicación que gatilla estos datos me los entrega en timestamp , sin embargo no hay problema de migrarlos como date solamente.
Por otro lado probé la query y me dá error en la suma de login y logoff (sintaxis).
Por ultimo necesito visualizar la hora de login y la hora de logoff, pero en la misma fila.

Una vez más gracias por la ayuda

[jurena]

chelodelsur,

lo de la fecha, no te preocupe: con el DATE() está resuelto, aunque si siempre sale 00:00:00 ni siquiera sería necesario, aunque creo que irá algo más rápido.

A mí no me da problemas ese SUM. ¿Qué versión de MySQL estas usando?, porque DATEDIFF funciona desde la 4.1.1. Yo uso MySQL 5 en localhost.

En cuanto a tu segunda petición, es difícil complacerte, pues piensa que una misma persona puede entrar y salir en el mismo día 30 veces (¿o no?), por ej. Cuál querrías ver de esas 30, si no le indicas nada, te mostrará la primera. La agrupación para sumar por usuario y día sólo te permite mostrar una por usuario y día. Explica un poco más y tal vez encontremos alguna otra solución.

[chelodelsur]

Haber

He cambiado la resta, ahora ya no da eror, pero tampoco resta correctamente. como lista en filas distintas el login y el logoff se produce eso.

Código:

SELECT 
a.nombres, 
a.apellidos, 
b.fecha,
horalogin,
horalogoff,
SEC_TO_time(unix_timestamp(horalogoff)-unix_timestamp(horalogin)) TIEMPO
FROM usuarios a
LEFT JOIN razontime b 
ON a.userkey = b.idagente
where b.idagente='224'
and hasrazon='0'
GROUP BY b.fecha, a.userkey

la idea es tenr algo así como esto:

FECHA ------nombres ------apellidos --------horalogin ----------horalogoff -----------total logon
2008-12-01 / NATACHA / ZUñIGA NAVARRO / 2008-12-01 14:00:00 / 2008-12-01 21:00:00 / 07:00:00

En condiciones normales un usuario solo entra una vez por día, para las escepciones cuando entre más de una vez por día podría ser el primer login y el último logoff, eso igual me serviría.

Tengo Mysql 5.0.67

[jurena]

Eso no puede salir bien. Estás restando y agrupando como si en cada día sólo hubiera una entrada y salida. Por otra parte, veo que horalogin y horalogoff son tipo timestamp. ¿Es así? Contesta a si existe la posibilidad de que en un mismo día alguien entre y salga varias veces. Porque si eso ocurre no podrás sumar el total de un día para una persona y mostrar en la misma consulta todas las horas de entrada y salida de ese día.
Dinos eso y trataremos de ayudarte.

[chelodelsur]

Haber
vamos desde cero.
1.-esta es la tabla, los campos en cuestion son datetime, la aplicación que los gatilla los guarda (informix) en timestamp, luego al pasarlos a mi BD los guardo en la tabla con este nuevo formato.

Código:

CREATE TABLE `razontime` (               
             `idsitio` int(11) default NULL,        
             `idagente` int(11) default NULL,       
             `fecha` datetime default NULL,             
             `haslogin` int(11) default NULL,       
             `haslogoff` int(11) default NULL,      
             `hasrazon` int(11) default NULL,       
             `tipomedio` int(11) default NULL,      
             `horalogin` datetime default NULL,     
             `horalogoff` datetime default NULL,    
             `razon` varchar(30) default NULL,      
             `razontimein` datetime default NULL,   
             `razontimeout` datetime default NULL,  
             `razonduracion` int(11) default NULL,  
             `idrazon` int(11) default NULL         
           ) TYPE=MyISAM

2.- Si existe la posibilidad de entrar más de una vez por día, en ese caso me interesa el primer login, y el ultimo logoff. ¿Es eso posible??

[jurena]

A ver si esto te sirve...

SELECT a.nombre, a.apellidos, DATE( b.fecha ) , SEC_TO_TIME( SUM( TIME_TO_SEC( TIMEDIFF( b.horalogoff, b.horalogin ) ) ) ) TIEMPO, TIME( MIN( b.horalogin ) ) horaprimeraentrada, TIME( MAX( b.horalogoff ) ) horaultimasalida
FROM usuarios a
LEFT JOIN razontime b ON a.userkey = b.idagente
GROUP BY DATE( b.fecha ) , a.userkey

[chelodelsur]

Hola

Para retomar el tema, hice las pruebas y no tiene caso me arroja error. Pero me temo que no es por la sec_to _time, si no por el campo, ya que el error em lo arroja despues de la función date = Date (b.fecha) , si le quito la función date, la query avanza hasta = ( b.horalogoff, b.horalogin.......

Error Code : 1064
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '( b.fecha ) , SEC_TO_TIME( SUM( TIME_TO_SEC( TIMEDIFF( b.horalo

Error Code : 1064
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '( b.horalogoff, b.horalogin ) ) ) ) TIEMPO, TIME( MIN( b.horalo

he revisado lso campos pero no veo nada anormal (según yo)

a ver si continúan dandome una mano con esto

[jurena]

Dime cómo escribes la consulta, pues la he probado la mía con tu tabla y funciona en mi ordenador. Tal vez el problema es el alias b, que lo has borrado. Escribe tu consulta completa y la probaré en mi ordenador.

[chelodelsur]

Pues tal y como tu lo has escrito

lo he copiado del foro y pegado al sql editor, pero en definitiva es el campo fecha el que no pasa no se porqué

[jurena]

Pues me extraña, pues, como te digo, lo he hecho funcionar en localhost con las tablas que nos has puesto. De todas formas, si la parte del time de ese campo datetime llamado fecha es 00:00:00 no importa si quitas DATE() tanto en la selección como en la agrupación, en el group by. De todas formas deberías probar DATE_FORMAT(b.fecha, '%Y-%m-%d') en el select y en el group by dejando todo lo demás.
Prueba a ver si eso resuelve el problema o es que hay alguna otra cosa.

[chelodelsur]

Ya
Vamos avanzando, con el format, ya no reclama por el campo fecha, pero sigue deteniendose

Error Code : 1064
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(b.horalogoff, b.horalogin ) ) ) ) TIEMPO

Código:

SELECT a.nombres, 
a.apellidos, 
DATE_format( b.fecha,'%y-%m-%d' ),
SEC_TO_TIME( SUM( TIME_TO_SEC( timeDIFF(b.horalogoff, b.horalogin ) ) ) ) TIEMPO,
TIME( MIN( b.horalogin ) ) horaprimeraentrada, 
TIME( MAX( b.horalogoff ) ) horaultimasalida 
FROM usuarios a
LEFT JOIN razontime b on a.userkey = b.idagente
GROUP BY date_format(b.fecha,'%y-%m-%d' ), a.userkey;

Gracias por seguir ayudando jejeje

[jurena]

No sé, pero me resulta sospechoso el uso del alias y del nombre de los campos. Parece como si el alias b estuviera siendo usado en otra parte de la consulta y confundiera, o que tal vez el nombre tiene alguna diferencia (un espacio, un acento, o algo así). No obstante, te hago otra propuesta usando TIMESTAMPDIFF, por si fuera el TIMEDIFF el causante del problema

Código sql:

SELECT a.nombres, 
a.apellidos, 
DATE_format( b.fecha,'%Y-%m-%d' ),
SEC_TO_TIME( SUM( TIME_TO_SEC( TIMESTAMPDIFF(SECOND, b.horalogin, b.horalogoff ) ) ) ) TIEMPO,
TIME( MIN( b.horalogin ) ) horaprimeraentrada, 
TIME( MAX( b.horalogoff ) ) horaultimasalida 
FROM usuarios a
LEFT JOIN razontime b ON a.userkey = b.idagente
GROUP BY date_format(b.fecha,'%Y-%m-%d' ), a.userkey;
 

Otra opción sería revisar los espacios tras las funciones y antes de los paréntesis y también tras el paréntesis. Quita esos espacios y prueba.