SELECT COUNT(DISTINCT CASE when q1.DM is null and q1.HT='H' then q1.pid ELSE null end) as pt_HT
,COUNT(DISTINCT CASE when q1.DM is null and q1.HT='H' and q2.sbp1<140 and q2.sbp2<140 and q2.dbp1<90 and q2.dbp2<90 then q1.pid ELSE null end) pt_HT_OK
,COUNT(DISTINCT CASE when q1.DM = 'D'and q1.HT='H' then q1.pid ELSE null end) as pt_DMHT
,COUNT(DISTINCT CASE when q1.DM = 'D'and q1.HT='H' and q2.sbp1<140 and q2.sbp2<140 and q2.dbp1<80 and q2.dbp2<80 then q1.pid ELSE null end) pt_DMHT_OK
from (SELECT pc.pcucodeperson,pc.pid,p.fname,p.lname,max(CASE when pc.chroniccode like 'e11%' then 'D' ELSE null end) as DM,max(CASE when pc.chroniccode like 'I1%' then 'H' ELSE null end) as HT
from personchronic pc
INNER JOIN person p on pc.pcucodeperson=p.pcucodeperson and pc.pid=p.pid
WHERE pc.typedischart='03'
and cup=p.pcucodeperson
GROUP BY pc.pcucodeperson,pc.pid)as q1
LEFT JOIN(SELECT ncd1.pcucodeperson,ncd1.pid,
REPLACE(substring(substring_index(ncd1.sbp,',',1),LENGTH(substring_index(ncd1.sbp,',',1-1))+1),',','')as sbp1,
REPLACE(substring(substring_index(ncd1.sbp,',',2),LENGTH(substring_index(ncd1.sbp,',',2-1))+1),',','')as sbp2,
REPLACE(substring(substring_index(ncd1.sbp,',',3),LENGTH(substring_index(ncd1.sbp,',',3-1))+1),',','')as sbp3,
REPLACE(substring(substring_index(ncd1.dbp,',',1),LENGTH(substring_index(ncd1.dbp,',',1-1))+1),',','')as dbp1,
REPLACE(substring(substring_index(ncd1.dbp,',',2),LENGTH(substring_index(ncd1.dbp,',',2-1))+1),',','')as dbp2,
REPLACE(substring(substring_index(ncd1.dbp,',',3),LENGTH(substring_index(ncd1.dbp,',',3-1))+1),',','')as dbp3
FROM
(SELECT n.pcucodeperson,n.pid,GROUP_CONCAT(REPLACE(substring(substring_index(v.pressure,'/',1),LENGTH(substring_index(v.pressure,'/',1-1))+1),',','')ORDER BY n.date_serv DESC)as sbp
,GROUP_CONCAT(case when LENGTH(v.pressure)<= 6 then RIGHT((v.pressure),2) when LENGTH(v.pressure) = 7 then RIGHT((v.pressure),3) else null end ORDER BY n.date_serv DESC)as dbp
from ncdservice n
INNER JOIN visit v on n.pid=v.pid and n.pcucodeperson=v.pcucodeperson and n.seq=v.visitno
GROUP BY n.pcucodeperson,n.pid
ORDER BY date_serv DESC ) as ncd1)as q2 on q1.pid=q2.pid and q1.pcucodeperson=q2.pcucodeperson
WHERE q1.HT is not NULL