[COMMENTO]
Query 2:  listato pazienti UNIRE UNICA 2 2011
[SQL]
select distinct 
v.codmedico codice_medico,
today() "data_estrazione",
n.pa_uslcode cod_reg,
p.codice_fiscale "cod_fiscale",
p.nascita Data_nascita, 
p.sesso "sesso",
p.provincia_nascita prov_nascita, 

(select max (a.ac_val) from cart_accert a 
where a.codice=p.codice and (a.ac_des like '%rischio%cardio%vascolare%(ISS)%')  
and (a.ac_val is not null)
and not exists (select b.codice from cart_accert b where a.codice=b.codice 
and (b.ac_des like '%rischio%cardio%vascolare%(ISS)%') 
and (b.data_open>a.data_open or (b.data_open=a.data_open and b.rowid>a.rowid)) 
and b.ac_val is not null)
and a.data_open between today()-8000 and today())  __rcv_iss_ultimo__,
(select max (a.data_upd) from cart_accert a 
where a.codice=p.codice and a.ac_des like '%rischio%cardio%vascolare%(ISS)%' 
and (a.ac_val is not null)
and not exists (select b.codice from cart_accert b where a.codice=b.codice and b.ac_des like '%rischio%cardio%vascolare%(ISS)%' and 
(b.data_open>a.data_open or (b.data_open=a.data_open and b.rowid>a.rowid)) 
and b.ac_val is not null)
and a.data_open between today()-8000 and today())  _data_RCV_ISS_Ultimo__,
(select count(a.codice) from cart_accert a 
where a.codice=p.codice and a.ac_des 
like '%emoglobina glicata%' 
and a.data_open between today()-455 and today() group by a.codice)  
____Tot_HBA1c_15mesi____, 
(select max (a.ac_val+0) from cart_accert a 
where a.codice=p.codice 
and (a.ac_des like '%emoglobina glicata%')
and (a.ac_val is not null) 
and not exists (select b.codice from cart_accert b where a.codice=b.codice 
and (b.ac_des like '%emoglobina glicata%')
and (b.data_open>a.data_open or (b.data_open=a.data_open and b.rowid>a.rowid)) 
and b.ac_val is not null)
and a.data_open between today()-8000 and today() ) __HBA1c_ultima__,
(select max (a.data_upd) from cart_accert a 
where a.codice=p.codice 
and (a.ac_des like '%emoglobina glicata%')
and (a.ac_val is not null) 
and not exists (select b.codice from cart_accert b where a.codice=b.codice 
and (b.ac_des like '%emoglobina glicata%')
and (b.data_open>a.data_open or (b.data_open=a.data_open and b.rowid>a.rowid)) 
and b.ac_val is not null)
and a.data_open between today()-8000 and today() ) __data_HBA1c_ultima__,
(select max (a.ac_val+0) from cart_accert a 
where a.codice=p.codice and (a.ac_des like '%creatinina%' and a.ac_des not like '%clearance creatinina%'and a.ac_des not like '%creatinina clearance%') and (a.ac_val is not null) and not exists (select b.codice from cart_accert b where a.codice=b.codice and (b.ac_des like '%creatinina%' and b.ac_des not like '%clearance creatinina%'and b.ac_des not like '%creatinina clearance%') and 
(b.data_open>a.data_open or (b.data_open=a.data_open and b.rowid>a.rowid)) 
and b.ac_val is not null)
and a.data_open between today()-8000 and today() ) __creatinina_ultima__,
(select max (a.data_upd) from cart_accert a 
where a.codice=p.codice and (a.ac_des like '%creatinina%' and a.ac_des not like '%clearance creatinina%'and a.ac_des not like '%creatinina clearance%') and (a.ac_val is not null) and not exists (select b.codice from cart_accert b where a.codice=b.codice and (b.ac_des like '%creatinina%' and b.ac_des not like '%clearance creatinina%'and b.ac_des not like '%creatinina clearance%') and 
(b.data_open>a.data_open or (b.data_open=a.data_open and b.rowid>a.rowid)) 
and b.ac_val is not null)
and a.data_open between today()-8000 and today() ) __data_creatinina_ultima__,
/*  riserva
(select max(if locate(a.ac_val,',') > 0 then left(a.ac_val,locate(a.ac_val,',')-1)+'.'
+right(a.ac_val,length(a.ac_val)-locate(a.ac_val,',')+0) else a.ac_val+0 endif) 
from cart_accert a where a.codice = p.codice 
and a.ac_des like '%MICROALBUMINURIA UR. FRESCHE\creatinuria%' 
and a.ac_val is not null and not exists 
(select b.codice from cart_accert b where a.codice = b.codice 
and b.ac_des like '%MICROALBUMINURIA UR. FRESCHE\creatinuria%'  
and b.ac_val is not null and b.data_open > a.data_open) 
and a.data_open between today() - 8000 and today()) 
"Microalbuminuria_creatinuria_ultima",*/
/*  riserva 
(select max (a.data_upd)
from cart_accert a where a.codice = p.codice 
and a.ac_des like '%MICROALBUMINURIA UR. FRESCHE\creatinuria%' 
and a.ac_val is not null and not exists 
(select b.codice from cart_accert b where a.codice = b.codice 
and b.ac_des like '%MICROALBUMINURIA UR. FRESCHE\creatinuria%'  
and b.ac_val is not null and b.data_open > a.data_open) 
and a.data_open between today() - 8000 and today()) 
"Microalbuminuria_creatinuria_ultima",*/
(select max(if locate(a.ac_val,',') > 0 then left(a.ac_val,locate(a.ac_val,',')-1)+'.'
+right(a.ac_val,length(a.ac_val)-locate(a.ac_val,',')+0) else a.ac_val+0 endif) 
from cart_accert a where a.codice = p.codice 
and a.ac_des like '%MICROALBUMINURIA%'               
and a.ac_val is not null 
and not exists (select b.codice from cart_accert b where a.codice = b.codice 
and b.ac_des like '%MICROALBUMINURIA%'  
and b.ac_val is not null and b.data_open > a.data_open) 
and a.data_open between today() - 8000 and today()) 
"Microalbuminuria_ultima",
(select max (a.data_upd) 
from cart_accert a where a.codice = p.codice 
and a.ac_des like '%MICROALBUMINURIA%'               
and a.ac_val is not null 
and not exists (select b.codice from cart_accert b where a.codice = b.codice 
and b.ac_des like '%MICROALBUMINURIA%'  
and b.ac_val is not null and b.data_open > a.data_open) 
and a.data_open between today() - 8000 and today()) 
"data_Microalbuminuria__ultima",
(select max(a.data_upd) 
from cart_accert a where a.codice=p.codice
and (a.ac_des like '%visita oculistica%'
or a.ac_des like '%v.oculistica%'
or a.ac_des like '%fundus oculi%'
or a.ac_des like '%fondo oculare%' or a.ac_des like '%fotografia del fundus%')
and a.data_open between today()-8000 and today()) 
"__Data_Fundus_oculi_ultimo__",
(select max (a.data_upd) from cart_accert a where a.codice=p.codice 
and (a.ac_des like '%ECG%'  
or a.ac_des like '%v.cardiologica%' 
or a.ac_des like '%visita cardiologica%'  
or a.ac_des like '%elettrocardiogramma%'
or a.ac_des like '%visita cardiologica%')
and a.ac_des not like '%elettrocardiogramma dinamico%'   
and a.data_open between today()-8000 and today() group by a.codice) ___Data_ECG_ultimo__,
(select count(a.codice) from cart_visite a 
where a.codice=p.codice
and a.data_open between today()-365 and today() group by a.codice) ____N_accessi_annoMMG____,
(select count(a.codice) from cart_accert a 
where a.codice=p.codice and a.ac_des 
like 'ricov.%' 
and a.data_open between today()-1826 and today() group by a.codice)  ____N_ric_5anni____,
(select count(a.codice) from cart_accert a 
where a.codice=p.codice and a.ac_des 
like 'ricov.%' 
and a.data_open between today()-365 and today() group by a.codice)  ____N_ric_12_mesi____,
(select max (a.data_open) from cart_pazpbl a 
where a.codice=p.codice and (a.cp_code like '496%' or a.cp_code like '491%'))
 ___BPCO_data_inizio_,
 (select max (a.ac_val+0) from cart_accert a 
where a.codice=p.codice and a.ac_des like '%VOLUME ESP. MAX%' 
and (a.ac_val is not null)
and not exists (select b.codice from cart_accert b where a.codice=b.codice 
and b.ac_des like '%VOLUME ESP. MAX%'  and 
(b.data_open>a.data_open or (b.data_open=a.data_open and b.rowid>a.rowid)) 
and b.ac_val is not null))  ____VEMS_ultima_spirometria____,
(select max (a.data_upd)  from cart_accert a 
where a.codice=p.codice and a.ac_des like '%spirometria%' 
and (a.ac_val is not null)
and not exists (select b.codice from cart_accert b where a.codice=b.codice and b.ac_des like '%spirometria%' and 
(b.data_open>a.data_open or (b.data_open=a.data_open and b.rowid>a.rowid)) 
and b.ac_val is not null)
and a.data_open between today()-8000 and today())  ______data_spirometria__ultima____,
(select max (a.data_upd)  from cart_vaccini a
where a.codice=p.codice and a.va_tipo like '%influenza%' 
and (a.va_tipo is not null)
and not exists (select b.codice from cart_vaccini b where a.codice=b.codice 
and b.va_tipo like '%influenza%'  and 
(b.data_open>a.data_open or (b.data_open=a.data_open and b.rowid>a.rowid)) 
and b.va_tipo is not null)
and a.data_open between today()-8000 and today())  ______data_vaccino_influenza_Ultima_,
(select max (a.data_upd)  from cart_vaccini a
where a.codice=p.codice and a.va_tipo like '%pneumococco%' 
and (a.va_tipo is not null)
and not exists (select b.codice from cart_vaccini b where a.codice=b.codice 
and b.va_tipo like '%pneumococco%'   and 
(b.data_open>a.data_open or (b.data_open=a.data_open and b.rowid>a.rowid)) 
and b.va_tipo is not null)
and a.data_open between today()-8000 and today()) ______data_vaccino_pneumococco_Ultima_,
 (select max (a.data_upd)  from cart_accert a 
where a.codice=p.codice and a.ac_des like '%Rx torace%' 
and (a.ac_val is not null)
and not exists (select b.codice from cart_accert b where a.codice=b.codice 
and b.ac_des like '%Rx torace%'  and 
(b.data_open>a.data_open or (b.data_open=a.data_open and b.rowid>a.rowid)) 
and b.ac_val is not null)
and a.data_open between today()-8000 and today())  __data_rx_torace_ultimo__,
 (select max (a.ac_val+0) from cart_accert a 
where a.codice=p.codice and a.ac_des like '%MONITORAGGIO INCRUENTO DELLA SATURAZIONE ARTERIOSA%' 
and (a.ac_val is not null)
and not exists (select b.codice from cart_accert b where a.codice=b.codice 
and b.ac_des like '%MONITORAGGIO INCRUENTO DELLA SATURAZIONE ARTERIOSA%' and 
(b.data_open>a.data_open or (b.data_open=a.data_open and b.rowid>a.rowid)) 
and b.ac_val is not null))  ____saturimetria_ultima___,
(select max (a.data_upd) from cart_accert a 
where a.codice=p.codice and a.ac_des like '%MONITORAGGIO INCRUENTO DELLA SATURAZIONE ARTERIOSA%' 
and (a.ac_val is not null)
and not exists (select b.codice from cart_accert b where a.codice=b.codice 
and b.ac_des like '%MONITORAGGIO INCRUENTO DELLA SATURAZIONE ARTERIOSA%' and 
(b.data_open>a.data_open or (b.data_open=a.data_open and b.rowid>a.rowid)) 
and b.ac_val is not null))  ____data_saturimetria_ultima___,
(select distinct 'si' from cart_terap a 
where a.codice=p.codice and a.co_atc like '%v03an01%'
and a.data_open between today()-365 and today()) ____ossigeno_ultimi365gg____,
(select distinct 'si' from cart_riabil a 
where a.codice=p.codice and a.ri_des  like '%esercizi respiratori%' 
and a.data_open between today()-365 and today()) ____riabilitazione_ultimi_365gg____,
(select distinct 'si' from cart_terap a 
where a.codice=p.codice and (a.co_atc like '%R03AC02%' or a.co_atc like '%R03AC04%')
and a.data_open between today()-180 and today()) ____salbutamolo_o_breve_durata__,
(select distinct 'si' from cart_terap a 
where a.codice=p.codice and (a.co_atc like '%R03AC12%' or a.co_atc like '%R03AC13%')
and a.data_open between today()-180 and today()) ____beta_stim_long__,
(select distinct 'si' from cart_terap a 
where a.codice=p.codice and a.co_atc like '%H02ab3%'
and a.data_open between today()-180 and today()) ____cortisone_sist_,
(select distinct 'si' from cart_terap a 
where a.codice=p.codice and a.co_atc like '%R03ba%'
and a.data_open between today()-180 and today()) ____cortisone_inal_,
(select distinct 'si' from cart_terap a 
where a.codice=p.codice and (a.co_atc like '%R03AK03%' or a.co_atc like '%R03AK04%'  
or a.co_atc like '%R03AK06%' )
and a.data_open between today()-180 and today()) ____assoc_betablocc_cortisone_,
 (select max (a.data_open) from cart_pazpbl a 
where a.codice=p.codice and (a.cp_code like '428%' or a.cp_code like '429.3%' or a.cp_code like '425.4%'))
___SCOMPENSO____,
 (select max (a.data_upd) from cart_accert a 
where a.codice=p.codice and a.ac_des like '%ECOCARDIO%' 
and (a.ac_val is not null)
and not exists (select b.codice from cart_accert b where a.codice=b.codice 
and b.ac_des like '%ECOCARDIO%'  and 
(b.data_open>a.data_open or (b.data_open=a.data_open and b.rowid>a.rowid)) 
and b.ac_val is not null))  __data__ecocardio_ultima___,
(select max (a.ac_val) from cart_accert a 
where a.codice=p.codice and a.ac_des like '%ECOCARDIO%' 
and (a.ac_val is not null)
and not exists (select b.codice from cart_accert b where a.codice=b.codice 
and b.ac_des like '%ECOCARDIO%'  and 
(b.data_open>a.data_open or (b.data_open=a.data_open and b.rowid>a.rowid)) 
and b.ac_val is not null)
and a.data_open between today()-8000 and today())  ____FE_ECOCardio_ultima____, 
(select distinct 'si' from cart_terap a 
where a.codice=p.codice and a.co_atc like 'b01aa%'
and
a.data_open between today()-180 and today()) ____TAO____,
(select max (a.data_open) from cart_pazpbl a 
where a.codice=p.codice and a.cp_code like '427.3%')
__data_inizio_FA__,

(select distinct 'si' from cart_terap a 
where a.codice=p.codice and a.co_atc like 'b01ac%'
and a.data_open between today()-180 and today()) ____antiaggreganti____,

(select max (a.data_open) from cart_pazpbl a 
where a.codice=p.codice and (a.cp_code like '401%' or a.cp_code like '405%'))
___ipertensione_data_inizio__,

(select max (a.data_open) from cart_pazpbl a 
where a.codice=p.codice and a.cp_code like '272%')
___dislipidemia__data_inizio__,


(select max (a.data_upd)  from cart_accert a 
where a.codice=p.codice 
and (a.ac_des like '%urine%esame%completo%' or a.ac_des like '%urine esame chimico%')
and a.data_open between today()-8000 and today() group by a.codice) 
_data_urine_ultima____,
(select max (a.data_upd)  from cart_accert a 
where a.codice=p.codice 
and (a.ac_des like '%potassio' and a.ac_des not like '%potassio intraeritrocitario%' and a.ac_des not like '%potassio nelle urine%'and a.ac_des not like '%potassio nel sudore%') 
and a.data_open between today()-8000 and today() 
and a.ac_val is not null group by a.codice) ___Data_potassio_ultimo___


from pazienti p, nos_002 n,v_pazienti v
where p.codice=n.codice
and v.codice=p.codice
and ((days(p.nascita, today())/365) between 0 and 110)
AND  (n.pa_medi > '') 
and (n.pa_drevoca is null or (n.pa_drevoca > today()-90 and motivo_revoca ='S' )) and p.pa_convenzione = 'S' 
and (p.decesso is null)
and (p.pa_convenzione not like 'l')
order by cod_reg