CREATE OR REPLACE VIEW EsordiReflusso AS
SELECT  E.Paziente, YEAR(E.DataEsordio)-YEAR(P.DataNascita) AS Eta, E.DataEsordio
FROM    esordio E INNER JOIN paziente P ON E.Paziente = P.CodFiscale
WHERE   E.Patologia = 'Reflusso gastroesofageo'
        AND YEAR(E.DataEsordio) = 2008;--  YEAR(CURRENT_DATE)-10;

CREATE OR REPLACE VIEW MeseCasi AS
SELECT  D.Mese, IF(MONTH(ER.DataEsordio) IS NULL, 0, COUNT(*)) AS NumCasi, AVG(E.Eta) AS Media
FROM    EsordiReflusso ER
        RIGHT OUTER JOIN
        ( -- tutti i mesi
            SELECT  MONTH(E.DataEsordio) AS Mese
            FROM esordio E
            GROUP BY MONTH(E.DataEsordio)
        ) AS D ON MONTH(ER.DataEsordio) = D.Mese
        LEFT OUTER JOIN
        EsordiReflusso E ON D.Mese = MONTH(E.DataEsordio)
GROUP BY D.Mese
ORDER BY D.Mese;

CREATE OR REPLACE VIEW MesiPiuCasi AS
SELECT  MC.Mese, MC.NumCasi
FROM    MeseCasi MC
WHERE   MC.NumCasi >= ALL (
                    SELECT  MC2.NumCasi
                    FROM    MeseCasi MC2
                    )
GROUP BY MC.Mese;

SELECT  IF(MPC.Mese IS NULL,MC.Mese, MPC.Mese) AS Mese,
        IF(MC.NumCasi = 0, NULL, MC.NumCasi) AS NumeroCasi,
        MC.Media AS EtaMediaPrec
FROM    MeseCasi MC
        NATURAL LEFT OUTER JOIN
        MesiPiuCasi MPC
WHERE   (MPC.Mese IS NULL AND MC.NumCasi = 0)
            OR (MC.NumCasi = MPC.NumCasi)
GROUP BY MC.Mese
ORDER BY MC.Mese;