select q2.pcucode,count(distinct q2.pid)as'PT_DM มา F/U ทั้งหมด'

,count(distinct if(q2.FBS <70,q2.pid,null))as 'White < 70'

,count(distinct if(q2.FBS between 70 and 130,q2.pid,null))as 'White 70-130'

,count(distinct if(q2.color='Green',q2.pid,null))as Green

,count(distinct if(q2.color='Yellow',q2.pid,null))as Yellow,count(distinct if(q2.color='Orange',q2.pid,null))as Orange

,count(distinct if(q2.color='Red',q2.pid,null))as Red

,count(distinct if(q2.color='Black_G',q2.pid,null))as 'Black_G[<125]'

,count(distinct if(q2.color='Black_Y',q2.pid,null))as 'Black_Y[125-154]'

,count(distinct if(q2.color='Black_O',q2.pid,null))as 'Black_O[155-182]'

,count(distinct if(q2.color='Black_R',q2.pid,null))as 'Black_R[>182]'

,count(distinct if(q2.color is null,q2.pid,null))as 'N/A' 

,count(distinct case when q2.bps <=130 and q2.bpd <=80 then q2.pid else null end )as 'BP.<=130/80'

,count(distinct case when (q2.bps >130 and q2.bpd >80)or (q2.bps >130 and q2.bpd <=80)

or(q2.bps <=130 and q2.bpd >80) then q2.pid else null end )as 'BP.>130/80' from ( 

-- ลบตั้งแต่บบรทัดแรก ถึงตรงนี้จะได้ รายชื่อครับ

SELECT q1.pcucode,q1.pid,q1.name as 'ชื่อ-สกุล',q1.Age as อายุ,q1.CID,q1.Address as 'ที่อยู่',q1.chroniccode as รหัสโรคเรื้อรัง,q1.organname as 'ภาวะแทรกซ้อน'

,q1.cup as รักษาที่ ,q1.visitdate,q1.FBS,q1.BPS,q1.BPD

,case when q1.complication is null and q1.fbs <125 then 'Green'

when q1.complication is null and q1.fbs between 125 and 154 then 'Yellow'

when q1.complication is null and q1.fbs between 155 and 182 then 'Orange'

when q1.complication is null and q1.fbs >182 then 'Red'

when q1.complication is not null and q1.fbs <125 then 'Black_G'

when q1.complication is not null and q1.fbs between 125 and 154 then 'Black_Y'

when q1.complication is not null and q1.fbs between 155 and 182 then 'Black_O'

when q1.complication is not null and q1.fbs >=183 then 'Black_R' else null end as Color

from (SELECT person.pcucodeperson as pcucode,person.pid,concat(ctitle.titlename,' ',person.fname,' ',person.lname) AS 'name'

,YEAR(FROM_DAYS(DATEDIFF(CURDATE(),person.birth))) as Age,person.idcard as CID,concat(house.hno,'  ','ม.',village.villno) AS Address

,GROUP_CONCAT(distinct personchronic.chroniccode) as chroniccode,cp.organcomplica as complication,corgan.organname

,personchronic.cup,if(GROUP_CONCAT(distinct personchronic.chroniccode) like'E1%,I1%' or GROUP_CONCAT(DISTINCT personchronic.chroniccode)like'I1%,E1%',1,0) as w1

,v.visitdate,v.fbs,v.bps,v.bpd

FROM person #phuwakorn sirichaimetha 2014-04-11 14:49

INNER JOIN house ON person.hcode = house.hcode AND person.pcucodeperson = house.pcucode

INNER JOIN village ON house.villcode = village.villcode AND house.pcucode = village.pcucode

LEFT JOIN ctitle ON person.prename = ctitle.titlecode

INNER JOIN personchronic ON person.pcucodeperson = personchronic.pcucodeperson AND person.pid = personchronic.pid

left join personchroniccomplica cp on personchronic.pid=cp.pid left join corgan on cp.organcomplica=corgan.organcode

inner join (SELECT visit.pcucode,visit.pid,visit.visitno,

concat(substr(visit.visitdate,9,2),'/',substr(visit.visitdate,6,2),'/',substr(visit.visitdate,1,4)+543) as visitdate

,visit.symptoms,visitdiag.diagcode

,case when visit.pressure2 IS NULL then substr(visit.pressure,1,INSTR(visit.pressure,'/')-1) else substr(visit.pressure2,1,INSTR(visit.pressure2,'/')-1) end as bps

,case when visit.pressure2 IS NULL then substr(visit.pressure,INSTR(visit.pressure,'/')+1,3) else substr(visit.pressure2,INSTR(visit.pressure2,'/')+1,3) end as bpd

,visitlabsugarblood.sugarnumdigit as fbs

FROM visit INNER JOIN visitdiag ON visit.pcucode = visitdiag.pcucode and visit.visitno = visitdiag.visitno

left JOIN visitlabsugarblood ON visit.pcucode = visitlabsugarblood.pcucode and visit.visitno = visitlabsugarblood.visitno

inner join (select visit.pid,Max(visit.visitno) as visitno from visit

INNER JOIN visitdiag ON visit.pcucode = visitdiag.pcucode and visit.visitno = visitdiag.visitno

where visitdiag.diagcode between 'E10' and 'E14.9' and visit.visitno not in (select vh.visitno from visithomehealthindividual vh)

and visit.visitdate between '2013-10-01' and '2013-12-31' 

group by visit.pid)vmax on visit.pid=vmax.pid and visit.visitno=vmax.visitno)v on personchronic.pid=v.pid

WHERE (personchronic.chroniccode between 'E10' and 'E14.9' or personchronic.chroniccode between 'I10' and 'I15')

and personchronic.typedischart ='03' and personchronic.cup =person.pcucodeperson

and right(village.villcode,2)!= '00' and person.typelive in (0,1,3)and person.dischargetype=9

GROUP BY person.pid order by personchronic.cup,village.villno,house.hno*1 )q1

where q1.chroniccode between 'E10' and 'E14.9' or q1.w1=1 group by q1.pid 

)q2 group by q2.pcucode; -- ลบบรรทัดนี้จะได้รายชื่อครับ