Curioso Problema(¿SQL puede hacerlo?)

[CapLurker]

Veréis, el otro día en la universidad se planteó una pregunta entre los compañeros,y ni el profesor de bbdd ha sido capaz de saber cómo se hace (pero el caso es que sí que se debería poder).
Se para confirmar asistencias de una serie de usuarios por ejemplo para una empresa, como una maquina de picar.
Presentamos una sola tabla con tuplas de 3 campos: Usuario, date, time. No hay un campo Key.
Formato: (0001,110101,800256), Correspondiente a id usuario, otro a la fecha de hoy, formato aammdd , y tiempo de ingreso, hhmmss.
Ejemplo de tabla:
[0001,110101,080256]
[0002,110101,080300]
[0001,110101,140200]
[0002,110101,140351]
[0001,110101,153612]
[0002,110101,153123]
[0001,110101,171215]
[0002,110101,171121]
Correspondería esto al primer día de trabajo, con dos trabajadores, que entran por la mañana, salen a comer, vuelven de comer y se van por la tarde.
El caso es que la consulta que se desea realizar es la siguiente:
Para un sólo día, se pretende conseguir una tupla que contenga los siguientes campos:
[date,usuario,hora de entrada por la mañana(<9h), hora de salida por la mañana(>9h y <=15h), hora de entrada por la tarde(>=15h y <16h), hora de salida por la tarde>=16h]
El resultado sería, para la tabla anterior, dos tuplas con los dos usuarios, con fecha del 110101, y sus distintas horas de picar.
Nadie sabe si se puede hacer un UNIQUE date con JOINs a la misma tabla aplicando los filtros de la hora.
¿Alguna idea?
Salu2!

[wchiquito]

Hola CapLurker,

Una posible opción es como se muestra abajo, sin embargo, es importante mencionar que esa consulta no es escalable, con pocos datos no tendrás problema pero al incrementar los datos el rendimiento disminuirá. Discutir en clase alternativas para optimizar/mejorar la consulta con grandes cantidades de datos.

Código MySQL:

/*Table structure for table `horario` */
 
DROP TABLE IF EXISTS `horario`;
 
CREATE TABLE `horario` (
  `usuario` varchar(10) default NULL,
  `fecha` date default NULL,
  `hora` time default NULL
) ENGINE=InnoDB;
 
/*Data for the table `horario` */
 
insert  into `horario`(`usuario`,`fecha`,`hora`)
values
('0001','2011-01-01','08:02:56'),('0002','2011-01-01','08:03:00'),
('0001','2011-01-01','14:02:00'),('0002','2011-01-01','14:03:51'),
('0001','2011-01-01','15:36:12'),('0002','2011-01-01','15:31:23'),
('0001','2011-01-01','17:12:15'),('0002','2011-01-01','17:11:21');
 
select
    h.fecha,
    h.usuario,
    min(hem.hora) as 'entrada mañana',
    max(hsm.hora) as 'salida mañana',
    min(het.hora) as 'entrada tarde',
    max(hst.hora) as 'salida tarde'
from horario h
    left join horario hem on h.usuario = hem.usuario and hem.hora < time('09:00:00')
    left join horario hsm on h.usuario = hsm.usuario and hsm.hora between time('09:00:01') and time('15:00:00')
    left join horario het on h.usuario = het.usuario and het.hora between time('15:00:01') and time('16:00:00')
    left join horario hst on h.usuario = hst.usuario and hst.hora > time('16:00:01')
where h.fecha = date('2011-01-01')
group by h.usuario, h.fecha;
 

[CapLurker]

Es una gran ayuda. Te lo agradezco, no se nos había ocurrido el LEFT JOIN. Luego lo probamos y te cuento.
Un saludo.

[CapLurker]

Lo cierto es que nos da error de sintaxis:
La tabla se llama EVENT_LOG, y los campos son:
EVENT_TIME para la hora, formato 120023, para las 12 horas, cero minutos, veintitrés segundos.
EVENT_DATE para la fecha, formato 01112011, para el uno de noviembre de 2011.
USER_ID, para el id del usuario.
Llevamos varias horas intentando sacar algo, pero siempre nos da error.
Nos encantaría trabajar bajo MySQL o Posgres, pero estamos bajo Access 2003, por cuestiones de requisitos de escenario, y tampoco podemos insertar los datos de DATE y TIME de otra forma.
El código es el siguiente:



SELECT
h.EVENT_DATE,
h.USER_ID,
min(hem.hora) AS 'entrada mañana',
max(hsm.hora) AS 'salida mañana',
min(het.hora) AS 'entrada tarde',
max(hst.hora) AS 'salida tarde'
FROM EVENT_LOG h
LEFT JOIN h hem ON h.USER_ID = hem.USER_ID AND hem.hora < 090000
LEFT JOIN h hsm ON h.USER_ID = hsm.USER_ID AND hsm.hora BETWEEN 090001 AND 150000
LEFT JOIN h het ON h.USER_ID = het.USER_ID AND het.hora BETWEEN 150001 AND 160000
LEFT JOIN h hst ON h.USER_ID = hst.USER_ID AND hst.hora > 160001
WHERE EVENT_LOG.EVENT_DATE = 01112011
GROUP BY USER_ID,EVENTLOG_TBL. EVENT_DATE;

PD: Sería la leche convertir el DATE en formato fecha y el TIME en formato hora, pero contando en que no sacamos esto, ya es complicada la conversión.

[gnzsoloyo]

Nos encantaría trabajar bajo MySQL o Posgres, pero estamos bajo Access 2003, por cuestiones de requisitos de escenario, y tampoco podemos insertar los datos de DATE y TIME de otra forma.

Bueno, vamos por el principio: Si no estás trabajando en MySQL, Postgre o incluso e Oracle o SQL Server, estás haciendo la pregunta en el foro equivocado.
Access no es un sistema de gestión de base de datos (DBMS), sino un aplicativo de manejo de tablas con recursos de SQL incrustados, que son extremadamente rudimentarios.
En ese sentido, las respuestas que te propongan tanto aquí como en los otros subforos no te servirán para tu problema, porque las capacidades y funcionalidades que los DBMS le agregan al estandar ANSI-SQL no funcionan en su inmensa mayoría en Access.
En otras palabras: Lo que los demás DBMS pueden hacer con el SQL, Access no lo puede lograr. Tu pregunta es para el foro de Bases de Datos General, y no para los subforos.
Por más que te intentemos ayudar, nuestra práctica fundamental no es con Access, sino con MySQL, de hecho, lo que para Access está mal escrito, en MySQL tiene una correctísima sintaxis, como es el caso de la consulta que posteas, por lo que nosotros no tenemos nada que modificar en esa consulta.

Lo central es que en el post inicial no mencionaste en ningún momento que tu desarrollo era en Access. DE lo contrario desde el principio te hubiésemos advertido que estabas preguntando en el sitio equivocado, y que lo que Access entiende de SQL es muy limitado.

Pide que pasen tu post al Foro de Base de Datos General.

[CapLurker]

Lo cierto es que nunca había trabajado con Access, estoy más acostumbrado a diseñar con Toad y trabajar con Posgre y MySQL, y estoy dándome cuenta que es una absoluto despropósito trabajar con Access, así que sí, debí mencionarlo.(Esto ocurre por requisitos del escenario, no puedo cambiar de software, aunque me encantaría)
Gracias por la puntualización.
Pido que pasen mi post al Foro de Base de Datos General.

[gnzsoloyo]

Yo ya notifiqué a los de FDW que lo muevan al Foro BBDDG. Esperemos que lo hagan pronto.