SELECT COUNT(DISTINCT pc.pid) as pt_DM
,COUNT(DISTINCT case when q2.fbs1 BETWEEN 70 and 130 and q2.fbs2 BETWEEN 70 and 130 then pc.pid Else null end) as pt_DM_control_ok
from personchronic pc
INNER JOIN person p on pc.pcucodeperson=p.pcucodeperson and pc.pid=p.pid
LEFT JOIN (SELECT q1.pcucodeperson,q1.pid,
REPLACE(substring(substring_index(q1.fbs,',',1),LENGTH(substring_index(q1.fbs,',',1-1))+1),',','')fbs1,
REPLACE(substring(substring_index(q1.fbs,',',2),LENGTH(substring_index(q1.fbs,',',2-1))+1),',','')fbs2,
REPLACE(substring(substring_index(q1.fbs,',',3),LENGTH(substring_index(q1.fbs,',',3-1))+1),',','')fbs3
from
(
SELECT vfbs.pcucodeperson,vfbs.pid,GROUP_CONCAT(convert(vfbs.sugarnumdigit USING utf8) order by vfbs.visitdate desc)as fbs
from (
SELECT v.pcucodeperson,v.pid,v.visitdate,vl.sugarnumdigit
from visitlabsugarblood vl
INNER JOIN visit v on vl.pcucode=v.pcucode and vl.visitno=v.visitno
ORDER BY visitdate DESC)as vfbs
GROUP BY vfbs.pcucodeperson,vfbs.pid) as q1) as q2 on pc.pcucodeperson=q2.pcucodeperson and pc.pid = q2.pid
where chroniccode like'e1%'
and typedischart='03'
and cup =p.pcucodeperson;
