XxjYRsyJ

1DROP PROCEDURE IF EXISTS discount_drug;
2DELIMITER $$
3
4CREATE PROCEDURE discount_drug (IN s VARCHAR(100), IN k INT)
5BEGIN
6	
7    -- creazione tab. temporanea per i farmaci più utilizzati...
8    CREATE TEMPORARY TABLE FarmaciTarget (
9		Farmaco VARCHAR(100) NOT NULL,
10        NumeroTerapie INT,
11        PRIMARY KEY (Farmaco)
12    );
13    
14    CREATE TEMPORARY TABLE ScontoFarmaci (
15		Farmaco VARCHAR(100) NOT NULL,
16        Sconto DOUBLE,
17        PRIMARY KEY (Farmaco)
18    );
19    
20    INSERT INTO FarmaciTarget
21		SELECT	T.Farmaco, COUNT(*) AS NumTerapieFarmaco
22        FROM	terapia T INNER JOIN patologia PA ON T.Patologia = PA.Nome
23        WHERE	YEAR(T.DataInizioTerapia) = 2015
24				AND PA.SettoreMedico = s
25		GROUP BY T.Farmaco
26        ORDER BY COUNT(*) DESC
27        LIMIT k;
28
29	INSERT INTO ScontoFarmaci
30		SELECT	FT.Farmaco, FT.NumTerapieFarmaco * (
31													1 +
32													(k*SUM(SQRT(1 / FT.NumTerapieFarmaco)))
33												   ) / (
34														SELECT	MAX(FT2.NumTerapieFarmaco)
35                                                        FROM	FarmaciTarget FT2
36                                                        ) AS ScontoFarmaco
37        FROM	FarmaciTarget FT;
38		
39        UPDATE 	farmaco F
40				INNER JOIN
41                ScontoFarmaci SF ON F.NomeCommerciale = SF.Farmaco
42		SET	F.Costo = F.Costo - SF.Sconto * F.Costo;
43	
44END $$