Ayuda con una consulta

[quimfv]

Código:

SELECT TODAS.Semana as Semana,
            SUM(TODAS.NewOpen) as Abiertas,
            SUM(TODAS.Closed) as Cerradas
FROM
((SELECT V.semana as Semana, 
            COUNT(DECODE(I.CREATEWEEK, SEMANA,1,NULL)) as NewOpen, 
            0 as Closed 
  FROM V_DIM_SEMANA V LEFT JOIN incidence I 
                  ON V.SEMANA = I.CREATEWEEK 
  WHERE V.semana BETWEEN TO_CHAR(TO_DATE('01011990', 'DDMMYYYY'), 'YYYYWW') 
              AND TO_CHAR(TO_DATE('04062008', 'DDMMYYYY'), 'YYYYWW') 
  group by V.semana 
  ORDER BY V.SEMANA)
UNION ALL
(SELECT V2.semana as Semana, 
            0 as NewOpen, 
            COUNT(DECODE(I2.CLOSEWEEK, SEMANA,1,NULL)) as Closed 
  FROM V_DIM_SEMANA V2 LEFT JOIN incidence I2 
                  ON V2.SEMANA = I2.CLOSEWEEK) 
  WHERE V2.semana BETWEEN TO_CHAR(TO_DATE('01011990', 'DDMMYYYY'), 'YYYYWW') 
              AND TO_CHAR(TO_DATE('04062008', 'DDMMYYYY'), 'YYYYWW') 
  group by V2.semana 
   ORDER BY V2.SEMANA)) as TODAS
GROUP BY TODAS.Semana
ORDER BY TODAS.Semana; 

Entiendo que en V_DIM_SEMANA tienes todas las semanas, luego con LEFT JOIN consigues que las muestre todas. Espero que estes usando InnoDB con MyISAM no te funcionará.

Si quieres que te comente que he hecho, repregunta.

Acerca de join

Quim