select
concat(ctitle.titlename,ps.fname,' ',ps.lname)as pname,
ps.birth as birth,
ps.sex as sex,
ps.idcard as idcard,
ps.hnomoi,
max(v.visitdate) as vdate,
v.pressure as vpressure,
substr(v.pressure,1,instr(v.pressure,"/")-1) as bps,
substr(v.pressure,instr(v.pressure,"/")+1,length(v.pressure)-instr(v.pressure,"/")) as bpd,
max(case when pc.chroniccode = 'I10' then 'HT' else ' ' end) as HT,
max(case when pc.chroniccode between 'E10' and 'E15.9' then 'DM' else ' ' end) as DM,
if(substr(v.pressure,1,instr(v.pressure,"/")-1) <140 or substr(v.pressure,instr(v.pressure,"/")+1,length(v.pressure)-instr(v.pressure,"/")) <90,'Green',
if(substr(v.pressure,1,instr(v.pressure,"/")-1) <160 or substr(v.pressure,instr(v.pressure,"/")+1,length(v.pressure)-instr(v.pressure,"/")) <100,'Yellow',
if(substr(v.pressure,1,instr(v.pressure,"/")-1) <180 or substr(v.pressure,instr(v.pressure,"/")+1,length(v.pressure)-instr(v.pressure,"/")) <110,'Orange ','Red'))) as 'ระดับความดัน',
s.sugarnumdigit as FBS,
max(case when s.sugarnumdigit < 126 then 'Green' when s.sugarnumdigit between 126 and 154 then 'Yellow' when s.sugarnumdigit between 155 and 182 then 'Orange'when s.sugarnumdigit >= 183 then 'Red' else ' ' end) as 'ระดับเบาหวาน',
max(case when visitlabchcyhembmsse.labcode = 'CH99' then visitlabchcyhembmsse.labresultdigit else ' ' end) as 'HbA1c',
max(case when visitlabchcyhembmsse.labcode = 'CH99' and visitlabchcyhembmsse.labresultdigit < 7 then 'Yellow' when visitlabchcyhembmsse.labcode = 'CH99' and visitlabchcyhembmsse.labresultdigit < 8 then 'Orange' when visitlabchcyhembmsse.labcode = 'CH99' and visitlabchcyhembmsse.labresultdigit >= 8 then 'Red' else ' ' end) as 'ระดับHbA1c'
from
person ps inner join visit v on ps.pcucodeperson = v.pcucodeperson and ps.pid = v.pid
inner join ctitle on ps.prename = ctitle.titlecode
inner join house on ps.pcucodeperson = house.pcucode and ps.hcode = house.hcode
Left join visitlabsugarblood s on v.pcucode = s.pcucode
and v.visitno = s.visitno
inner join personchronic pc on ps.pcucodeperson = pc.pcucodeperson
and ps.`pid` = pc.pid
Left join visitlabchcyhembmsse on v.pcucode = visitlabchcyhembmsse.pcucode
and v.visitno = visitlabchcyhembmsse.visitno
where v.visitdate between '2014-05-01' and '2014-05-31'
and (pc.chroniccode between 'E10' and 'E15.9' or pc.chroniccode = 'I10') and v.pressure is not null
and CONCAT(ps.pid,ps.pcucodeperson) NOT IN (select CONCAT(persondeath.pid,persondeath.pcucodeperson)from persondeath)
and pc.cup = pc.pcucodeperson and SUBSTRING(house.villcode,7,2) <> '00'
group by ps.pid;