Oracle - Cantidad de registros con consultas correlacionadas

Hola expertos.
Alguien podría ayudarme con el siguiente problema, soy nuevo en esto y me enfrento con algo complicado para mi.
se los agradeceré bastante.

mi escenario es el siguiente:

tengo una tabla dim de tiempo como la siguiente:

fecha | año | mes | dia |dia_laborable |
2013/08/01 | 2013 | 8 | 1 | SI
2013/08/02 | 2013 | 8 | 2 | SI
2013/08/03 | 2013 | 8 | 3 | NO
2013/08/04 | 2013 | 8 | 4 | NO
2013/08/05 | 2013 | 8 | 5 | SI
2013/08/06 | 2013 | 8 | 6 | SI
2013/08/07 | 2013 | 8 | 7 | SI
2013/08/08 | 2013 | 8 | 8 | SI
2013/08/09 | 2013 | 8 | 9 | SI
2013/08/09 | 2013 | 8 | 9 | NO
etc.....

y por otra parte tengo una tabla fact como la siguiente:

ID_ticket | FECHA_CREACION | FECHA_CIERRE |
1 | 2013/08/01 | 2013/08/05 |
2 |2013/08/02 | 2013/08/08 |
3 |2013/08/01 | 2013/08/06 |
4 |2013/08/01 | 2013/08/09 |
5 |2013/08/01 | 2013/08/04 |
6 |2013/08/01 | 2013/08/03 |
7 |2013/08/01 | 2013/08/09 |

el resultado que necesito es la cantidad de registros (para cada ticket) que hay en la tabla dim tiempo en base a la fecha de creacion y fecha cierrey que dia laborable='SI', algo asi como:

ID_ticket | FECHA_CREACION | FECHA_CIERRE | cantidad_dias
1 | 2013/08/01 | 2013/08/05 | 2
2 |2013/08/02 | 2013/08/08 | 4
3 |2013/08/01 | 2013/08/06 | 3
4 |2013/08/01 | 2013/08/09 | 6
5 |2013/08/01 | 2013/08/04 | 2
6 |2013/08/01 | 2013/08/03 | 2
7 |2013/08/01 | 2013/08/09 | 6

yo supongo que lo puedo obtener con subconsultas correlacionadas y mi query hasta ahora es el siguiente pero por alguna razon no obtengo el dato correcto.

select h.FC_CREACION , h.FC_CIERRE
, (select count(f.fecha) from dim_fecha f where (f.fecha=h.fc_creacion) and (f.fecha >= ALL (select h.FC_CREACION from fact_table h where f.fecha=h.fc_creacion ) and f.fecha < ALL (select h.FC_CIERRE from fact_table h where f.fecha=h.FC_CIERRE )) and (f.dia_laborable like 'SI') ) as cantidad
from fact_table h

alguna idea, sugerencia?

De ante mano muchas gracias.
Saludos!!!

Fácil, es un simple conteo, según lo que necesitas seria algo como esto:

SELECT FACT.IDTICKET, FACT.FECCREA , FACT.FECCIERRE,
(
SELECT COUNT(1)
FROM (
SELECT to_date('01/08/2013','dd/mm/yyyy') fecha , 'SI' LABORABLE FROM DUAL
UNION ALL
SELECT to_date('02/08/2013','dd/mm/yyyy') , 'SI' FROM DUAL
UNION ALL
SELECT to_date('03/08/2013','dd/mm/yyyy') , 'NO' FROM DUAL
UNION ALL
SELECT to_date('04/08/2013','dd/mm/yyyy') , 'NO' FROM DUAL
UNION ALL
SELECT to_date('05/08/2013','dd/mm/yyyy') , 'SI' FROM DUAL
UNION ALL
SELECT to_date('06/08/2013','dd/mm/yyyy') , 'SI' FROM DUAL
UNION ALL
SELECT to_date('07/08/2013','dd/mm/yyyy') , 'SI' FROM DUAL
UNION ALL
SELECT to_date('08/08/2013','dd/mm/yyyy') , 'SI' FROM DUAL
UNION ALL
SELECT to_date('08/08/2013','dd/mm/yyyy') , 'SI' FROM DUAL) TIEMPO
WHERE TIEMPO.FECHA>=TRUNC(FACT.FECCREA)
AND TIEMPO.FECHA<TRUNC(FACT.FECCIERRE)+1
AND TIEMPO.LABORABLE='SI'
) CANT_DIAS
FROM (
SELECT 1 IDTICKET, to_date('01/08/2013','dd/mm/yyyy') FECCREA, to_date('05/08/2013','dd/mm/yyyy') FECCIERRE FROM DUAL
UNION ALL
SELECT 2 , to_date('02/08/2013','dd/mm/yyyy') , to_date('08/08/2013','dd/mm/yyyy') FROM DUAL
UNION ALL
SELECT 3 , to_date('01/08/2013','dd/mm/yyyy') , to_date('06/08/2013','dd/mm/yyyy') FROM DUAL
UNION ALL
SELECT 4 , to_date('01/08/2013','dd/mm/yyyy') , to_date('09/08/2013','dd/mm/yyyy') FROM DUAL
UNION ALL
SELECT 5 , to_date('01/08/2013','dd/mm/yyyy') , to_date('04/08/2013','dd/mm/yyyy') FROM DUAL
UNION ALL
SELECT 6 , to_date('01/08/2013','dd/mm/yyyy') , to_date('03/08/2013','dd/mm/yyyy') FROM DUAL
UNION ALL
SELECT 7 , to_date('01/08/2013','dd/mm/yyyy') , to_date('09/08/2013','dd/mm/yyyy') FROM DUAL) FACT
ORDER BY 1 ;


Me retorna esto :

IDTICKET FECCREA FECCIERRE CANT_DIAS
1 01/08/2013 05/08/2013 3
2 02/08/2013 08/08/2013 6
3 01/08/2013 06/08/2013 4
4 01/08/2013 09/08/2013 7
5 01/08/2013 04/08/2013 2
6 01/08/2013 03/08/2013 2
7 01/08/2013 09/08/2013 7

Es decir para el ticket 1 que tiene como fecha de creacion y fecha de cierre existen 3 dias laborables entre ese rango de fechas.

Espero cumpla con tu requerimiento, sino me lo comentas para ayudarte.

Saludos

Luis

Hola Luis

Muchas gracias por tu respuesta.

Pero que pasaría si la tabla fact tiene miles y miles de registros¿?

De ante mano muchas gracias por tus comentarios

Que pasaría??
pues realizaría el conteo por los registros devueltos.

No me queda claro tu pregunta, que es lo que podría pasar?

Claro que por teoría de base de datos sabemos que un indice bien creado nos ayuda con la recuperación de datos.

No comprendo tus dudas si recupera miles de registros o millones de registros?

Saludos

Luis

Gracias de nuevo por tu respuesta Luis.

Mi comentario se refería a que por lo poco que conozco del lenguaje sql (Al final de cuentas quien tiene el expertis eres tu) me parece que la consulta se torna un tanto "estática", por lo que veo tendría que hacer "N" union all y select´s pues la fact table que manejo de ejemplo tiene miles de registros

ID_ticket | FECHA_CREACION | FECHA_CIERRE |
1 | 2013/08/01 | 2013/08/05 |
2 |2013/08/02 | 2013/08/08 |
3 |2013/08/01 | 2013/08/06 |
4 |2013/08/01 | 2013/08/09 |
5 |2013/08/01 | 2013/08/04 |
6 |2013/08/01 | 2013/08/03 |
7 |2013/08/01 | 2013/08/09 |
N...|N....|N....

cres que exista la forma en la que se pueda recuperar los datos de forma dinamica, por ejemplo id_ticket <1000 y no tener que hacer 1000 union all y select´s.
Muchas gracias por tus respuestas, espero ser lo suficientemente explicito.
Saludos!!

José si no te haz fijado en mi mensaje pongo "según lo que necesitas seria algo como esto" no es que deba ser así, yo uso select union con datos en duro porque yo no tengo datos de donde consultar, en cambio tu si tienes datos de donde consultar, en lugar de todos los "select union" tú pondrías solo el nombre de tu tabla. Yo construyo mi conjunto de datos para lograr lo que tu deseas pues yo no tengo tablas con los datos como tu mencionas pero la funcionalidad es la misma, solo reeemplaza mis conjuntos de datos por el nombre de tus tablas y listo.

Saludos

Luis

Tu select sin mis "select union" seria asi (si en caso tus tablas se llaman como las mías) :


SELECT FACT.IDTICKET, FACT.FECCREA , FACT.FECCIERRE,
( SELECT COUNT(1)
WHERE TIEMPO.FECHA>=TRUNC(FACT.FECCREA)
AND TIEMPO.FECHA<TRUNC(FACT.FECCIERRE)+1
AND TIEMPO.LABORABLE='SI'
) CANT_DIAS
FROM FACT
ORDER BY 1 ;


Es un select chiquito.

Saludos

Luis

Muchas gracias por tus respuestas Luis,

en breve estaré ajustando el query en mi base de datos, y te dejo saber.

es de gran ayuda tu aporte para personas que nos iniciamos en el sql, nuevamente gracias

Saludos!!!

SELECT FACT.IDTICKET, FACT.FECCREA , FACT.FECCIERRE,
( SELECT COUNT(1) FROM TIEMPO
WHERE TIEMPO.FECHA>=TRUNC(FACT.FECCREA)
AND TIEMPO.FECHA<TRUNC(FACT.FECCIERRE)+1
AND TIEMPO.LABORABLE='SI'
) CANT_DIAS
FROM FACT
ORDER BY 1 ;