Problemas al crear una consulta sql

Question

Buenas tardes, escribo por que se me presento un problema a la hora de traer unos registros a traves de una consulta sql para generar un grafico, explico lo que quiero hacer:

Necesito generar esta consulta

Atraso en retiro libro de clases, por docente (se sabe que hubo atraso en el retiro del libro cuando la hora de la columna FECHA RETIRO es mayor a la hora correspondiente al HORA INICIO en la foto)

vienen siendo 2 tablas:

Tabla Archivo

Tabla BloqueAcademico

estan unidas a traves de modulo_inicio = id_bloque, asi que las uni a traves de esta query

SELECT T1.nombre_docente, T1.modulo_inicio, T1.fecha_retiro, T2.hora_inicio
FROM archivo T1 INNER JOIN BloqueAcademico T2 ON T1.modulo_inicio = T2.id_bloque
WHERE T1.id_archivo= 2 AND nombre_docente <> '' AND (DATEDIFF(MINUTE, T2.hora_inicio,T1.fecha_retiro  )>1)
ORDER BY nombre_docente

El detalle es que debo calcular por docente, si retiro el libro es mayor a hora de inicio, intente con el DATEDIFF pero algo no va bien, aparte de que se repiten los docentes, supongo que por el group by q faltaria.

resultado de esta manera

Gracias de antemano, este foro siempre me ayuda.

Answer 1

Hola erick1987v:

Hay algo que no has puesto y se te esta escapando.

if (OBJECT_ID(N'archivo','U')) IS NOT NULL
begin
drop table archivo
end
create table archivo (id int identity(1,1),nombre_Docente varchar(100), modulo_inicio int, fecha_Retiro datetime)
go
INSERT INTO archivo (nombre_Docente, modulo_inicio, fecha_Retiro)
VALUES
('AEDO V',3,'20180813 09:49'),
('AEDO V',1,'17590101 00:00'),
('ARANCIBIA B',6,'20180813 11:00'),
('ARANCIBIA B',4,'20180813 10:59'),
('ARAYA V',1,'20180813 14:31'),
('ARAYA V',3,'20180813 14:31'),
('BLACIO ABAD',1,'20180813 08:18'),
('CABRERA J',1,'20180813 16:24'),
('CARRILLO A',6,'20180813 10:50'),
('CARRILLO A',4,'20180813 10:50'),
('CAVIERES P',1,'20180813 08:12'),
('CORREA O',6,'20180813 11:30')
GO
IF (OBJECT_ID('BLOQUEACADEMICO','U')) IS NOT NULL
	BEGIN
	DROP TABLE BLOQUEACADEMICO;
	END;
	CREATE TABLE BLOQUEACADEMICO (id_bloque int, horainicio time)
	go
	insert into BLOQUEACADEMICO (id_bloque, horainicio)
	values
	(1,'08:30'),
	(2,'09:15'),
	(3,'10:15'),
	(4,'11:00'),
	(5,'11:45'),
	(6,'12:30'),
	(7,'13:45'),
	(8,'14:30'),
	(9,'15:15'),
	(10,'16:00'),
	(11,'16:45'),
	(12,'17:30')
	go
	select t1.nombre_docente,T1.modulo_inicio, T1.fecha_retiro, T2.horainicio
	from archivo t1 inner join BLOQUEACADEMICO t2 on t1.modulo_inicio = t2.id_bloque

Salida:

Como ves la relación parece correcta, y sobre 12 nombres insertados, hay 12 filas.

Por tanto me supongo que tienes muchos registros en la tabla Archivo.

Según lo expones, parece que tus tablas no estan del todo normalizadas.

Ya comentas

Answer 2

Otra posibilidad, es que no entienda del todo tu modelo de datos.

¿la tabla bloque_Academico, es como un horario de clases y nada más, sin tener relación real con la cantidad de registros de arhcivos?

Algo así, como si para el docente AEDO V cuando entra en el modulo 3 sea, el dia que sea tiene que entrar a las 10:15....

Answer 3

Buenas tardes, si, es asi como me indico al final. Si las tablas no estan normalizadas. Se trata de la tabla que se llena para generar los graficos con los que me ayudo hace dias, haciendome una paginacion que quedo perfecto gracias a ud. Pero esta vez la condicion que necesito para poder generar los graficos e ir paginandolos es, por docente mostrar cuantas veces buscó el libro de clases con retraso. Esto seria cuando la hora de la columna fecha de retiro sea mayor a la hora de hora_inicio de la tabla bloqueAcademico

Si, la tabla bloqueacademico es meramente un horario de clases.

He estado probando y lo mas que he podido lograr es esto:

SELECT T1.nombre_docente, T1.fecha_retiro, hora_inicio = CONVERT(DATETIME, CONVERT(CHAR(8), T1.fecha_retiro, 112) + ' ' + CONVERT(CHAR(8), T2.hora_inicio, 108))
FROM archivo T1 INNER JOIN BloqueAcademico T2 ON T1.modulo_inicio = T2.id_bloque
WHERE T1.id_archivo= 2 AND nombre_docente <> '' AND T1.fecha_retiro <> '01-01-1759' 
ORDER BY nombre_docente

lo cual me arroja este resultado

pero al parecer no puedo aplicar una comparacion en el where de una columna que es calculada, aparte de modificarla para incluirla al paginado que me ayudo a hacer ud

Muchas gracias Javi Fernandez, me ha ayudado un monton estos dias

Answer 4

Consegui una solución que pude integrar con el paginado que me hizo ud, creé una vista que me devolviera los registros, y filtro esa vista comparando las fechas, desde la vista si me lo permitia y no directo del query anterior por ser una columna calculada, no se si seria la mejor opcion pero ya funciona.

Muchisimas gracias Javi Fernandez

Answer 5

Hola erick1987:

Aunque ya lo hayas logrado, lo cual me parece genial, te pongo otra posible solución sin la vista.

	select t1.nombre_docente,T1.modulo_inicio, COUNT(*) as numVeces
	from archivo t1 inner join BLOQUEACADEMICO t2 on t1.modulo_inicio = t2.id_bloque
	where 
/* la parte izquierda, extrae la fecha_retiro y le resta 15 minutos. */
	cast(dateadd(minute,-15,(t1.fecha_Retiro) ) as time) >= t2.horainicio
and t1.fecha_Retiro > '17590101' and t1.nombre_docente <>''
	group by t1.nombre_Docente, t1.modulo_inicio
/* el group by, te sirve para saber cuantas veces tiene retraso un docente por módulo */

Si lo que quieres es sacar la fecha, del día, y el tiempo de retraso.

SELECT t1.nombre_docente,
       T1.modulo_inicio,
       t1.fecha_Retiro,
       t2.horainicio,
       CONVERT(DATETIME, CONVERT(CHAR(8), T1.fecha_retiro, 112)+' '+CONVERT(CHAR(8), T2.horainicio, 108)) AS horaEntradaOficial,
       DATEDIFF(minute, t2.horainicio, CAST(t1.fecha_Retiro AS TIME)) AS retrasoMinutos
FROM archivo t1
     INNER JOIN BLOQUEACADEMICO t2 ON t1.modulo_inicio = t2.id_bloque
WHERE CAST(DATEADD(minute, -15, (t1.fecha_Retiro)) AS TIME) >= t2.horainicio
      AND t1.fecha_Retiro > '17590101'
      AND t1.nombre_docente <> '';

Un saludo

Answer 6

Buenas noches Javi Fernandez, me funciono tal como me dijo, muchas gracias de verdad por su ayuda. estoy ya terminando el proyecto gracias a ud y a este foro.

Answer 7

De nada. Ojalá la entrega del mismo sea genial :)