BxinHnr6

1
2DROP PROCEDURE IF EXISTS healthy_patients_in_period;
3DELIMITER $$
4
5CREATE PROCEDURE healthy_patients_in_period (IN _from DATE, IN _to DATE)
6BEGIN
7	
8    CREATE TEMPORARY TABLE PazientiTarget(
9		Paziente VARCHAR(30) NOT NULL,
10        SanoPrima INT NOT NULL,        
11        SanoDopo INT NOT NULL,
12        PRIMARY KEY (Paziente)
13        );
14        
15	INSERT INTO PazientiTarget
16		SELECT	DISTINCT P.CodFiscale, DATEDIFF( _from,
17										IFNULL((SELECT	MAX(E2.DataGuarigione)
18												FROM	esordio E2
19												WHERE	E2.DataGuarigione < _from
20														AND E2.Paziente = P.CodFiscale)
21											   , P.DataNascita)
22										) AS GiorniSanoBefore,
23									    DATEDIFF( 
24												 IFNULL((SELECT	MIN(E3.DataEsordio)
25														FROM	esordio E3
26														WHERE	E3.DataEsordio > _to
27																AND E2.Paziente = P.CodFiscale)
28													   , CURRENT_DATE())
29										) AS GiorniSanoAfter
30        FROM	paziente P
31        WHERE	P.CodFiscale NOT IN ( -- pazienti sani in quel periodo
32					SELECT	DISTINCT T.Paziente
33                    FROM	terapia T
34                    WHERE	T.DataInizioTerapia BETWEEN _from AND _to
35							OR T.DataFineTerapia BETWEEN _from AND _to
36                            OR (T.DataInizioTerapia < _from AND T.DataFineTerapia IS NULL)
37					);
38                    
39	IF _from > _to OR _from IS NULL OR _to IS NULL THEN
40    SIGNAL SQLSTATE '45000'
41        SET MESSAGE_TEXT = "Errore, input non valido!";
42	END IF;
43
44END $$