[COMMENTO] Query 1: listato pazienti UNIRE UNICA 1 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.data_open) from cart_pazpbl a WHERE a.codice = p.codice and a.cp_code like '250%') data_diagnosi_diabete_tipo_1e2, /*consigli alimentazione = A or B*/ (select max(a.ac_val) from cart_accert a where a.codice=p.codice and a.ac_des like '%consigli alimentazione%' 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 '%consigli alimentazione%' and (b.data_open>a.data_open ) and b.ac_val is not null)) "consigli_alimentazione", /*consigli attivita fisica = A or B*/ (select max(a.ac_val) from cart_accert a where a.codice=p.codice and a.ac_des like '%consigli%attivita%fisica%' 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 '%consigli%attivita%fisica%' and (b.data_open>a.data_open ) and b.ac_val is not null)) "consigli_attivita_fisica", (select distinct 'si' from cart_terap a where a.codice=p.codice and (a.co_atc like 'a10ba%' or a.co_atc like 'a10bd01'or a.co_atc like 'a10bd02%' or a.co_atc like 'a10bd03%' or a.co_atc like 'a10bd05'or a.co_atc like 'a10bd07%' or a.co_atc like 'a10bd08%') and a.data_open between today()-180 and today()) _metformina_anche_associata, (select distinct 'si' from cart_terap a where a.codice=p.codice and (a.co_atc like 'a10bb%' or a.co_atc like 'a10bf%' or a.co_atc like 'a10bg%' or a.co_atc like 'a10bh%' or a.co_atc like 'a10bx04%' or a.co_atc like 'a10bx%' or a.co_atc like 'a10bd04%' or a.co_atc like 'a10bd06') and a.data_open between today()-180 and today()) _altri_antidiabetici_, (select distinct 'si' from cart_terap a where a.codice=p.codice and a.co_atc like 'a10a%' and a.data_open between today()-180 and today()) _insulina_, (select max (a.data_open) from cart_pazpbl a where a.codice=p.codice and a.cp_code like '413%' ) ____angina_pect___, (select max (a.data_open) from cart_pazpbl a where a.codice=p.codice and (a.cp_code like '410%' or a.cp_code like '412%')) __infarto_mioc__, (select max (a.data_open) from cart_pazpbl a where a.codice=p.codice and (a.cp_code like '414.05%' or a.cp_code like '414.04%' or a.cp_code like '414.03%' or a.cp_code like 'V45.81%')) __Bypass__, (select max (a.data_open) from cart_pazpbl a where a.codice=p.codice and (a.cp_code like 'V45.82%' or a.cp_code like 'V45.89%')) __angioplastica__, (select max (a.data_open) from cart_pazpbl a where a.codice=p.codice and a.cp_code like '435.9%') ___TIA__, (select max (a.data_open) from cart_pazpbl a where a.codice=p.codice and a.cp_code like '436%') __ictus___, (select distinct 'si' from cart_terap a where a.codice=p.codice and (a.co_atc like 'c03%' or a.co_atc like 'c07c%' or a.co_atc like 'c09ba%' or a.co_atc like 'c09da%') and a.data_open between today()-180 and today()) ____diuret_anche_associati__, (select distinct 'si' from cart_terap a where a.codice=p.codice and ( a.co_atc like 'c07A%' or a.co_atc like 'c07c%') and a.data_open between today()-180 and today()) ____betablocc_anche_assoc__, (select distinct 'si' from cart_terap a where a.codice=p.codice and a.co_atc like 'c08%' and a.data_open between today()-180 and today()) ____Ca_antag_anche_ass__, (select distinct 'si' from cart_terap a where a.codice=p.codice and (a.co_atc like 'c09a%' or a.co_atc like '%c09b%') and a.data_open between today()-180 and today()) __ACE_anche_assoc__, (select distinct 'si' from cart_terap a where a.codice=p.codice and (a.co_atc like 'c09c%' or a.co_atc like '%c09d%') and a.data_open between today()-180 and today()) __sartani_anche_assoc__, (select distinct 'si' from cart_terap a where a.codice=p.codice and (a.co_atc like 'c02ac01%' or a.co_atc like 'C02CA%') and a.data_open between today()-180 and today()) ____alfablocc_clonidina____, (select distinct 'si' from cart_terap a where a.codice=p.codice and (a.co_atc like 'c10aa%' or a.co_atc like 'c10ab%') and a.data_open between today()-180 and today()) ____statine_fibrati____, (select max (a.ac_val+0) from cart_accert a where a.codice=p.codice and (a.ac_des like '%glicemia%' and a.ac_des not like '%glicemia%post%prandiale%' and a.ac_des not like '%ultima misurazione glicemia%' and a.ac_des not like '%glicemia con stick%' and a.ac_des not like '%episodi ipoglicemia%' and a.ac_des not like '%strisce reattive per glicemia%') 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 '%glicemia%' and b.ac_des not like '%glicemia%post%prandiale%' and b.ac_des not like '%ultima misurazione glicemia%' and b.ac_des not like '%glicemia con stick%' and b.ac_des not like '%episodi ipoglicemia%' and b.ac_des not like '%strisce reattive per glicemia%') 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() ) ______glicemia_ultima______, (select max (a.data_upd) from cart_accert a where a.codice=p.codice and (a.ac_des like '%glicemia%' and a.ac_des not like '%glicemia%post%prandiale%' and a.ac_des not like '%ultima misurazione glicemia%' and a.ac_des not like '%glicemia con stick%' and a.ac_des not like '%episodi ipoglicemia%' and a.ac_des not like '%strisce reattive per glicemia%') 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 '%glicemia%' and b.ac_des not like '%glicemia%post%prandiale%' and b.ac_des not like '%ultima misurazione glicemia%' and b.ac_des not like '%glicemia con stick%' and b.ac_des not like '%episodi ipoglicemia%' and b.ac_des not like '%strisce reattive per glicemia%') 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_glicemia_ultima__, (select max (a.ac_val+0) from cart_accert a where a.codice=p.codice and a.ac_des like '%trigliceridi%' 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 '%trigliceridi%' 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()) __trigliceridi_ultimo____, (select max (a.data_upd) from cart_accert a where a.codice=p.codice and a.ac_des like '%trigliceridi%' 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 '%trigliceridi%' 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_trigliceridi_ultimo__, (select max (a.ac_val+0) from cart_accert a where a.codice=p.codice and a.ac_des like '%colesterolo totale%' 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 '%colesterolo totale%' 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()) _colest_tot_ultimo_, (select max (a.data_upd) from cart_accert a where a.codice=p.codice and a.ac_des like '%colesterolo totale%' 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 '%colesterolo totale%' 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_colest_tot_ultimo__, (select max (a.ac_val+0) from cart_accert a where a.codice=p.codice and a.ac_des like '%HDL%' 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 '%HDL%' 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()) __HDL_ultimo__, (select max (a.data_upd) from cart_accert a where a.codice=p.codice and a.ac_des like '%HDL%' 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 '%HDL%' 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_HDL_ultimo_, (select a.ac_val from cart_accert a where a.codice=p.codice and a.ac_des like '%fumo%' 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 '%fumo%' 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()-1825 and today()) _fumo_, (select max(a.ac_val) from cart_accert a where a.codice=p.codice and a.ac_des like '%consigli fumo%' 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 '%consigli fumo%' and (b.data_open>a.data_open ) and b.ac_val is not null)) "consigli fumo", ( select max (a.ac_val) from cart_accert a where a.codice = p.codice and a.ac_des like '%attivit%fisica%' and ( a.data_open <= today() ) and not exists ( select b.codice from cart_accert b where a.codice=b.codice and b.ac_des like '%attivit%fisica%' and ( b.data_open <= today() ) and ( b.data_open>a.data_open or ( b.data_open=a.data_open and b.rowid>a.rowid ) ) ) ) "__attivita_fisica_ultima__", (select max(a.p_max+0) from cart_press a where a.codice = p.codice and a.p_max is not null and not exists (select b.codice from cart_press b where a.codice = b.codice and b.p_max is not null and b.time_last > a.time_last) and a.data_open between today() - 8000 and today()) "PA_sistolica_ultima", (select max(a.p_min+0) from cart_press a where a.codice = p.codice and a.p_min is not null and not exists (select b.codice from cart_press b where a.codice = b.codice and b.p_min is not null and b.time_last > a.time_last) and a.data_open between today() - 8000 and today()) "PA_diastolica_ultima", (select max (a.data_upd) from cart_press a where a.codice = p.codice and a.p_max is not null and not exists (select b.codice from cart_press b where a.codice = b.codice and b.p_max is not null and b.time_last > a.time_last) and a.data_open between today() - 8000 and today()) "data_ultima_PA_" , (select max (a.ac_val) from cart_accert a where a.codice=p.codice and (a.ac_des like '%bmi%' or a.ac_des like '%body mass index%') 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 '%bmi%' or b.ac_des like '%body mass index%') 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() ) __bmi_ultimo__, (select max (a.data_upd) from cart_accert a where a.codice=p.codice and (a.ac_des like '%bmi%' or a.ac_des like '%body mass index%') 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 '%bmi%' or b.ac_des like '%body mass index%') 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_bmi_ultimo_, (select a.ac_val+0 from cart_accert a where a.codice=p.codice and (a.ac_des like '%altezza%' or a.ac_des like '%height%') 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 '%altezza%' or b.ac_des like '%height%') 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() ) ______Altezza_val_ultimo_7200gg______, (select max (a.ac_val+0) from cart_accert a where a.codice=p.codice and ((a.ac_des like '%peso' and a.ac_des not like '%peso specifico%') or a.ac_des like '%weight%') 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 '%peso' and b.ac_des not like '%peso specifico%') or a.ac_des like '%weight%') 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()) __peso_val_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