NcV46pfw

1DROP PROCEDURE IF EXISTS healty_patients_in_period;
2DELIMITER $$
3
4CREATE PROCEDURE healty_patients_in_period(IN _from DATE, IN _to DATE)
5BEGIN
6	-- Tabella con pazienti sani
7	DROP TEMPORARY TABLE IF EXISTS pazSani;
8    CREATE TEMPORARY TABLE pazSani AS (
9		SELECT P.CodFiscale
10        FROM Paziente P
11			INNER JOIN Esordio E
12            ON E.Paziente = P.CodFiscale
13		WHERE (
14			SELECT COUNT(*)
15            FROM Esordio E1
16            WHERE E1.Paziente = P.CodFiscale
17				AND E1.DataEsordio < _from 
18                AND (E1.DataGuarigione IS NULL OR E1.DataGuarigione > _from)
19		) = 0
20    );
21    -- Ultima malattia guarita (qualsiasi paz)
22    DROP TEMPORARY TABLE IF EXISTS daQuanto;
23    CREATE TEMPORARY TABLE daQuanto AS (
24		SELECT P.CodFiscale, 
25			DATEDIFF(_from, MAX(E.DataGuarigione)) AS DQS
26        FROM Paziente P
27			INNER JOIN Esordio E
28            ON E.Paziente = P.CodFiscale
29		WHERE E.DataGuarigione IS NOT NULL
30			AND E.DataGuarigione < _from
31		GROUP BY P.CodFiscale
32    );
33    -- Prima malattia che si presenta (qualsiasi paz)
34	DROP TEMPORARY TABLE IF EXISTS perQuanto;
35    CREATE TEMPORARY TABLE perQuanto AS (
36		SELECT P.CodFiscale,
37			DATEDIFF(MIN(E.DataEsordio), _to) AS PQS
38        FROM Paziente P
39			INNER JOIN Esordio E
40            ON E.Paziente = P.CodFiscale
41		WHERE E.DataEsordio > _to
42		GROUP BY P.CodFiscale
43    );
44    -- Query finale
45    SELECT DISTINCT(P.CodFiscale) AS Paziente,
46		DQ.DQS, PQ.PQS
47    FROM pazSani P
48		LEFT OUTER JOIN daQuanto DQ
49			ON P.CodFiscale = DQ.CodFiscale
50        LEFT OUTER JOIN perQuanto PQ
51			ON P.CodFiscale = PQ.CodFiscale;
52END$$
53
54DELIMITER ;