eHXQaZAX

1create or replace view mesi as
2select distinct month(dataesordio) as mese 
3from esordio order by month(dataesordio);
4
5drop table if exists drug_statistics;
6
7create table drug_statistics (
8	Farmaco char(100) not null,
9    Mese integer not null,
10    Anno integer not null,
11    TerapieInCorso integer default 0,
12    primary key(Farmaco , mese , anno)
13) engine = InnoDB;
14
15drop event if exists aggiornadrug;
16
17delimiter $$
18create event aggiornadrug
19on schedule every 1 month
20starts '2015-01-01'
21do
22begin
23
24	truncate table drug_statistics;
25	
26	insert into drug_statistics (
27		select nomecommerciale , mese , year(current_date) , 0
28		from farmaco cross join mesi
29        where mese = month(current_date));
30        
31	create or replace view finals as
32	select T.farmaco , D.mese , D.anno , count(*) as quante
33	from terapia T right outer join drug_statistics D on D.farmaco = T.farmaco
34	where ((year(datainizioterapia) = D.anno and datafineterapia is null)
35		or(year(DataInizioTerapia) = D.anno and year(datafineterapia) = D.anno)
36		or(year(datainizioterapia) < D.anno and datafineterapia is null)
37		or(year(Datainizioterapia) < D.anno and year(datafineterapia) = D.anno))
38	and (D.mese between month(datainizioterapia) and month(datafineterapia)
39			or datafineterapia is null)
40	group by T.farmaco, D.mese , D.anno;
41
42
43	update drug_statistics natural join finals
44	set Terapieincorso = quante ;
45    
46    select * from drug_statistics;
47end $$
48delimiter ;