healthy_patients_in_period

DROP PROCEDURE IF EXISTS healthy_patients_in_period;
DELIMITER $$

CREATE PROCEDURE healthy_patients_in_period(IN _from DATE, IN _to DATE)
BEGIN
    -- Tabella con pazienti sani
    DROP TEMPORARY TABLE IF EXISTS pazSani;
    CREATE TEMPORARY TABLE pazSani AS (
        SELECT P.CodFiscale
        FROM Paziente P
            INNER JOIN Esordio E
            ON E.Paziente = P.CodFiscale
        WHERE (
            SELECT COUNT(*)
            FROM Esordio E1
            WHERE E1.Paziente = P.CodFiscale
                AND E1.DataEsordio < _from
                AND (E1.DataGuarigione IS NULL OR E1.DataGuarigione > _from)
        ) = 0
    );
    -- Ultima malattia guarita (qualsiasi paz)
    DROP TEMPORARY TABLE IF EXISTS daQuanto;
    CREATE TEMPORARY TABLE daQuanto AS (
        SELECT P.CodFiscale,
            DATEDIFF(_from, MAX(E.DataGuarigione)) AS DQS
        FROM Paziente P
            INNER JOIN Esordio E
            ON E.Paziente = P.CodFiscale
        WHERE E.DataGuarigione IS NOT NULL
            AND E.DataGuarigione < _from
        GROUP BY P.CodFiscale
    );
    -- Prima malattia che si presenta (qualsiasi paz)
    DROP TEMPORARY TABLE IF EXISTS perQuanto;
    CREATE TEMPORARY TABLE perQuanto AS (
        SELECT P.CodFiscale,
            DATEDIFF(MIN(E.DataEsordio), _to) AS PQS
        FROM Paziente P
            INNER JOIN Esordio E
            ON E.Paziente = P.CodFiscale
        WHERE E.DataEsordio > _to
        GROUP BY P.CodFiscale
    );
    -- Query finale
    SELECT DISTINCT(P.CodFiscale) AS Paziente,
        DQ.DQS, PQ.PQS
    FROM pazSani P
        LEFT OUTER JOIN daQuanto DQ
            ON P.CodFiscale = DQ.CodFiscale
        LEFT OUTER JOIN perQuanto PQ
            ON P.CodFiscale = PQ.CodFiscale;
END$$

DELIMITER ;