| Respuesta: Ayuda a buscar fechas
parece que ya resolvi el problema
esta seria mi solucion:
SELECT a.Fecha, b.MATRICULA, b.TARJETA, b.FECHA, b.ENTRADA, b.SALIDA, b.HORAENTRADA, b.HORASALIDA, b.NOMBRE_EMPL,b.RFC,b.CLAVE_CATEGORIA
FROM
(
select to_date('01 10 2007','DD MM YYYY')-1 + level as Fecha from dual
where (to_date('01 10 2007','DD MM YYYY')-1+level) <= last_day(to_date('16 10 2007','DD MM YYYY'))
connect by level<=round( to_date('16 10 2007','DD MM YYYY') - to_date('01 10 2007','DD MM YYYY') )
) A,
(
select DISTINCT A.MATRICULA, A.TARJETA,A.FECHA, B.CVE_EMPL, B.NOMBRE_EMPL,B.RFC,D.CLAVE_CATEGORIA,
max(CASE WHEN A.TIPO_MOV = 'B' THEN A.COMPLETO END) ENTRADA,
max(CASE WHEN A.TIPO_MOV = 'E' THEN A.COMPLETO END) SALIDA,
max(CASE WHEN A.TIPO_MOV = 'B' THEN A.HORA END) HORAENTRADA,
max(CASE WHEN A.TIPO_MOV = 'E' THEN A.HORA END) HORASALIDA
FROM ASIS_MOVTOS A, esquema1.tabla1 B, esquema1.tabla2 C, esquema1.tabla3 D
WHERE B.CVE_EMPL=A.MATRICULA AND
(B.CVE_EMPL=A.MATRICULA AND B.CVE_EMPL=C.CVE_EMPL and C.AREA_RESP=D.AREA_RESP AND C.CONSEC=D.CONSEC
AND B.NUM_QNA=C.NUM_QNA AND C.NUM_QNA=D.NUM_QNA AND B.NUM_QNA=D.NUM_QNA AND C.FECHA_FIN is null)
GROUP BY A.MATRICULA, A.TARJETA,A.FECHA,B.CVE_EMPL,B.NOMBRE_EMPL,B.RFC,
B.CVE_EMPL, B.NUM_QNA, C.NUM_QNA, C.AREA_RESP, C.CONSEC,C.FECHA_FIN,
D.NUM_QNA, D.ESTADO_PLAZA, D.CLAVE_CATEGORIA
ORDER BY A.MATRICULA ASC,A.FECHA ASC
) B
where A.FECHA = B.FECHA AND (((B.ENTRADA IS NULL OR B.SALIDA IS NULL))
OR (B.HORAENTRADA BETWEEN '08:31:00' AND '10:00:00' )
OR (B.HORASALIDA BETWEEN '15:31:00' AND '17:00:00'))
ORDER BY B.MATRICULA,A.FECHA |