¿Cómo harías esta consulta?

[moNTeZIon]

Buenas!
Tengo problemas para diseñar una SQL que me devuelva lo que necesito.
El entorno sería el siguiente, simplificando y reducido para aislar el problema en cuestión:

ASISTENCIAS (CodigoTrabajador, Fecha, HoraInicio, HoraFin)
PARTESTRABAJO (CodigoTrabajador, Fecha, HoraInicio, HoraFin)

La información que necesito obtener es:

Partes de Trabajo cerrados en un momento en que el trabajador no se encuentra en la empresa.
Siendo más explícito:
Registros de la tabla PARTESTRABAJO que su HORAFIN no esté comprendida entre una HORAINICIO y una HORAFIN de la tabla ASISTENCIAS, en el mismo día y para el mismo trabajador.

Espero que la informaciíon que facilito sea suficiente para la comprensión del problema. La verdad es que no se me ocurre cómo extraer esa información, pero esos datos están ahí, y los puedo ver, así que imagino que habrá la forma de extraerlos con SQL.
Muchas gracias por vuestra ayuda!
Saludos.

[flaviovich]

Basicamente seria esto:

Código sql:

SELECT P.*
FROM PARTESTRABAJO P
INNER JOIN ASISTENCIAS A ON P.CodigoTrabajador=A.CodigoTrabajador AND P.Fecha=A.Fecha
WHERE P.HoraFin NOT BETWEEN A.HoraInicio AND A.HoraFin
 

[moNTeZIon]

...Eliminar por favor...

[moNTeZIon]

Hola Flaviovich.
Gracias por responder. La verdad es que había pensado en esa SQL pero no me devolvía exactamente lo que necesito.
Te cuento.
En la tabla ASISTENCIAS puede haber N Asistencias para un mismo trabajador en un mismo día.
Igual que en PARTESTRABAJO que también puede haber N registros del mismo trabajador en un mismo día.
El tema está en controlar que las HoaFin de la tabla PARTESTRABAJO estén comprendidas en las franjas entre HoraInicio y HoraFin de la tabla ASISTENCIAS, del mismo trabajador y el mismo día.
Para simplificarlo, le he anadido algo más al WHERE de tu consulta, quedando así:

Código SQL:

SELECT P.*
FROM PARTESTRABAJO P
INNER JOIN ASISTENCIAS A ON P.CodigoTrabajador=A.CodigoTrabajador AND P.Fecha=A.Fecha
WHERE P.HoraFin NOT BETWEEN A.HoraInicio AND A.HoraFin
AND P:CodigoTrabajador='ERIC'
AND P.Fecha = '20090710'
 

De esta forma puedo comprobar si realmente funciona la consulta, puesto que el día 10 de Julio el trabajador ERIC tiene un PARTE cerrado a las 20:09 cuando su última asistencia termina a las 19:43.
Por lo tanto, en este caso lo que debería obtener como resultado son los campos de ese registro de la tabla PARTESTRABAJO, que terrmina a las 20:09
Actualmente existen 4 PARTESTRABAJO de ese día para ese trabajador, mientras que en la tabla ASISTENCIAS existen 2 (la de la mañana y la de la tarde).
Con la consulta que acabo de escribir, obtengo 5 registros:
Los 4 partes que tiene ese día + uno más repetido, que precisamente es el registro que tendría que salir solo, o sea, el que termina a las 20:09
Por qué motivo sale este resultado y como podría aislar unicamente el registro que necesito?
Espero que me puedan ayudar.
Muchas Gracias!!

Para más información, pongo una imagen con los registros existentes del trabajador ERIC el dia 10 de Julio, en los que se puede ver claramente que el parte que termina a las 20:09 termina fuera de la asistencia, que ésta termina a las 19:43.

[flaviovich]

De que tipos son HoraInicio y HoraFin?

[moNTeZIon]

Tienes toda la razón Son de tipo Varchar. Disculpa no haberlo comentado antes...
El campo Fecha si que es de tipo DateTime, pero estos otros para las horas no lo son.
Aunque imagino que con un CAST se puede solucionar este pequeño inciso, verdad?
Pero si, hay que tratarlas como Horas.
Gracias por tu ayuda Flaviovich! A ver si consigues sacarla porqué a mi me trae de cabeza...

[moNTeZIon]

Lo más que me está saliendo es la misma SQL haciéndole el CAST a los campos de tipo Hora para que trabajen como tal:

Código SQL:

SELECT P.*
FROM PARTESTRABAJO P
INNER JOIN ASISTENCIAS A ON P.CodigoTrabajador=A.CodigoTrabajador AND P.Fecha=A.Fecha
WHERE CAST(P.HoraFin AS DateTime) NOT BETWEEN CAST(A.HoraInicio AS DateTime) AND CAST(A.HoraFin AS DateTime)
AND P.CodigoTrabajador='ERIC'
AND P.Fecha = '20090710'
 

Pero como comenté, aparecen los cuatro registros de PARTESTRABAJO y uno más repetido.
Veo que lo que está haciendo esta consulta es mostrar el registro de PARTESTRABAJO una vez por cada registro de ASISTENCIAS y luego elimina las que no cumplen el WHERE. Pero exactamente no es eso.
Podemos ver que ese día hay 2 ASISTENCIAS, la consulta comprueba la HoraFin del PARTETRABAJO con cada una de ellas. Lo correcto sería que si en una de ellas ya está correcta (ya se encuentra la HoraFin dentro de la franja de alguna de las asistencias) no apareciera.. uff, no se si me he explicado correctamente... Espero que si.
Gracias por el esfuerzo a quien se tome un momento con esto.
Saludos.

[flaviovich]

Código SQL:

SELECT P.*
FROM PARTESTRABAJO P
INNER JOIN ASISTENCIAS A ON P.CodigoTrabajador = A.CodigoTrabajador AND P.Fecha = A.Fecha
WHERE CAST(P.HoraFin AS DATETIME) NOT BETWEEN CAST(A.HoraInicio AS DATETIME) AND CAST(A.HoraFin AS DATETIME)
AND CAST(P.HoraInicio AS DATETIME) BETWEEN CAST(A.HoraInicio AS DATETIME) AND CAST(A.HoraFin AS DATETIME)
AND CAST(P.HoraFin AS DATETIME) > CAST(A.HoraFin AS DATETIME)
AND P.CodigoTrabajador = 'ERIC'
AND P.Fecha = '20090710'
 

[moNTeZIon]

Hola flaviovich!
Disculpa no haber respondido antes, tuve que estar por otros temas, y ahora he retomado este.
La verdad es que la última consulta que expones funciona perfectamente para el caso en que un PARTE tenga una HoraInicio anterior a la ASISTENCIA del mismo trabajador en el mismo día. Genial! Este era el objetivo. Pero veo que si elimino la ASISTENCIA, ya no aparece el registro con esta consulta.
O sea, cuando si que existe una ASISTENCIA, aparece el registro porqué se trata de un PARTE con HoraInicia anterior a la ASISTENCIA del mismo día.
¿Pero qué ocurre si ese día no tiene ASISTENCIA? Se trata también de una situación en que existe un PARTE de un día en concreto, y en cambio no existe ninguna ASISTENCIA de ese dia y de ese trabajador. Por lo tanto también debería aparecer.
He realizado algunos intentos pero no lo consigo.
Igual una solución es hacer otra consulta diferente para obtener los registros que se encuentren en esta situación y luego hacer un UNION de las dos consultas... Aunque estaría muy bien que esta única consulta devolviera los registros que se encuentren en cualquiera de las dos situaciones.
¿Creeis que es posible?
Graciasssss!!!

[flaviovich]

¿Cuál sería la comparación si no cuentas con los registros de asistencia?
En este caso, creo que el union podría ser la solución.

[moNTeZIon]

Iniciado por moNTeZIon

Aunque estaría muy bien que esta única consulta devolviera los registros que se encuentren en cualquiera de las dos situaciones.

Ciertamente, no puede ser, jeje..
El problema que tengo con la nueva consulta, es que no he sabido rematarla sin tirar de una subconsulta, que imagino que ralentiza la ejecución de ésta bastante.
En la subconsulta con un LEFT JOIN entre PAERTES y ASISTENCIAS obtengo todos los PARTES que tienen relación con la tabla ASISTENCIAS y luego, con el NOT IN me quedo con los PARTES que no devuelva esa subconsulta. O sea, consigo realizar la consulta que devuelve justo lo contrario que necesito, por eso hago la subconsulta, para quedarme con los registros que no estén ahí.

Código SQL:

SELECT P.*
FROM PartesTrabajo P
WHERE IdParteTrabajo NOT IN (
SELECT IdParteTrabajo FROM PartesTrabajo P1
LEFT JOIN AsistenciasTrabajadores A1 ON P1.CodigoTrabajador = A1.CodigoTrabajador AND P1.Fecha = A1.Fecha
WHERE A1.TipoAsistencia='A' )
 

¿Puede conseguirse esto sin usar subconsultas?
Gracias de nuevo!

[flaviovich]

Código SQL:

SELECT P.*
FROM PartesTrabajo P
LEFT JOIN AsistenciasTrabajadores A ON P.CodigoTrabajador = A.CodigoTrabajador AND P.Fecha = A.Fecha
WHERE A.CodigoTrabajador IS NULL
 

[moNTeZIon]

Qué grande! Funciona a la perfección.
Gracias man. Me has enseñado unas cuantas cositas!
Un saludo!