SELECT DISTINCT  person.pcucodeperson AS pcucode, person.pid, person.idcard AS cid,
IF(personchronic.datefirstdiag IS NULL OR TRIM(personchronic.datefirstdiag)='' 
OR personchronic.datefirstdiag LIKE '0000-00-00%','',DATE_FORMAT(personchronic.datefirstdiag,'%Y%m%d')) AS datedx,  
UCase(REPLACE(cdisease.diseasecode,'.','')) AS chronic,  
IF(personchronic.datedischart IS NULL OR TRIM(personchronic.datedischart)='' 
OR personchronic.datedischart LIKE '0000-00-00%','',DATE_FORMAT(personchronic.datedischart,'%Y%m%d')) AS datedis,  
personchronic.typedischart AS typedis,  IF( personchronic.dateupdate IS NULL OR TRIM(personchronic.dateupdate)='' 
OR personchronic.dateupdate LIKE '0000-00-00%','',DATE_FORMAT(personchronic.dateupdate,'%Y%m%d%H%i%s') ) AS d_update  
,if(hospfirstdiag is null or trim(hospfirstdiag)='','',hospfirstdiag) hospfirstdiag  ,if(cup is null or trim(cup)='','',cup) cup 
FROM personchronic  
LEFT JOIN cdisease ON (personchronic.chroniccode=cdisease.diseasecode)  
LEFT JOIN person ON (personchronic.pcucodeperson=person.pcucodeperson AND personchronic.pid=person.pid) 
WHERE typelive IN('0','1','3') 
AND personchronic.pcucodeperson='04600'  
AND ((ucase(left(chroniccode,3)) between 'I60' AND 'I69') 
OR (ucase(left(chroniccode,3)) between 'I10' AND 'I15') 
OR (ucase(left(chroniccode,3)) = 'I64') 
OR (ucase(left(chroniccode,3)) = 'J44.9') 
OR (ucase(left(chroniccode,3)) between 'I20' AND 'I25') 
OR (ucase(left(chroniccode,3)) between 'E10' AND 'E14') 
OR (ucase(left(chroniccode,3)) between 'J45' AND 'J46') 
OR (ucase(left(chroniccode,3)) = 'J43') OR (ucase(left(chroniccode,3)) = 'E66') 
OR (ucase(left(chroniccode,3)) between 'C00' AND 'C97') 
OR (ucase(left(chroniccode,3)) between 'A15' AND 'A19') 
OR (ucase(left(chroniccode,3)) between 'B20' AND 'B24') 
OR (ucase(left(chroniccode,3)) = 'K70.3') 
OR (ucase(left(chroniccode,3)) = 'K71.7') 
OR (ucase(left(chroniccode,3)) = 'K74') 
OR (ucase(left(chroniccode,3)) = 'K73') 
OR (ucase(left(chroniccode,3)) = 'N18') 
OR (ucase(left(chroniccode,3)) between 'M15' AND 'M19') 
OR (ucase(left(chroniccode,3)) = 'M47') 
OR (ucase(left(chroniccode,3)) between 'M05' AND 'M06') ) 
AND (DATE(personchronic.dateupdate) >= '2015-01-01') 
AND ((personchronic.datefirstdiag BETWEEN '2014-10-31' AND '2015-11-30') 
OR (DATE(personchronic.dateupdate) BETWEEN '2014-10-01' AND '2016-09-30') ) 
ORDER BY person.pcucodeperson ASC, person.pid ASC