select q1.a as visitdate,q1.b as HN,q1.c as 'Name',q1.d as Age,q1.e as Hno,q1.f as Moo,q1.g as Disease,q1.h as Symptoms
 ,concat(q1.i1,'/',q1.i2) as BP
 ,case when q1.i1 < 120 and q1.i2 < 80 then 1 else 0 end as 'ขาว-HT'
 ,case when (q1.i1 between 120 and 139) and (q1.i2 between 80 and 89) then 1
 when (q1.i1 between 120 and 139) and (q1.i2 < 80) then 1
 when (q1.i1 < 130) and (q1.i2 between 80 and 89) then 1 else 0 end as 'เขียว-HT'
 ,case when (q1.i1 between 140 and 159) and (q1.i2 between 90 and 99) then 1
 when (q1.i1 between 140 and 159) and (q1.i2 < 90) then 1
 when (q1.i1 < 140) and (q1.i2 between 90 and 99) then 1 else 0 end as 'เหลือง-HT'
 ,case when (q1.i1 between 160 and 179) and (q1.i2 between 100 and 109) then 1
 when (q1.i1 between 160 and 179) and (q1.i2 < 100) then 1
 when (q1.i1 < 160) and (q1.i2 between 100 and 109) then 1 else 0 end as 'ส้ม-HT'
 ,case when (q1.i1 >= 180) or (q1.i2 >= 110) then 1 else 0 end as 'แดง-HT'
 ,q1.j as BSL,q1.k as HbA1c
 ,case when q1.j < 100 then '1' else 0 end as 'ขาว-DM'
 ,case when q1.j BETWEEN 100 and 125 then 1 else 0 end as 'เขียว-DM'
 ,case when (q1.j BETWEEN 126 and 154) and (q1.k < 7) then 1 else 0 end as 'เหลือง-DM'
 ,case when (q1.j BETWEEN 155 and 182) and (q1.k BETWEEN 7 and 7.9) then 1 else 0 end as 'ส้ม-DM'
 ,case when (q1.j > 182) and (q1.k >=8 ) then 1 else 0 end as 'แดง-DM'
 from (SELECT
 visit.visitdate as a
 ,person.pid AS b
 ,concat(ctitle.titlename,person.fname," ",person.lname) AS c
 ,getAgeYearNum(person.birth,CURDATE()) AS d
 ,house.hno AS e
 ,right(house.villcode,2) AS f
 ,GROUP_CONCAT(case when pc.chroniccode like 'E1%' THEN 'เบาหวาน' when pc.chroniccode like 'I1%' THEN 'ความดันโลหิตสูง' else '' end) as g
 ,visit.symptoms as h
 ,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 i1
 ,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 i2
 ,visitlabsugarblood.sugarnumdigit as j
 ,vl.labresultdigit as k
 FROM
 person
 LEFT JOIN ctitle ON ctitle.titlecode = person.prename
 INNER JOIN house ON person.pcucodeperson = house.pcucode AND person.hcode = house.hcode
 INNER JOIN (SELECT * FROM personchronic WHERE personchronic.chroniccode LIKE 'I1%' or personchronic.chroniccode LIKE 'E1%') AS pc ON person.pid = pc.pid
 LEFT JOIN visit ON person.pcucodeperson = visit.pcucode AND person.pid = visit.pid
 LEFT JOIN visitlabsugarblood ON visit.pcucode = visitlabsugarblood.pcucode AND visit.visitno = visitlabsugarblood.visitno
 INNER JOIN (select * from visitdiag where visitdiag.diagcode like 'I1%' or visitdiag.diagcode like 'E1%') as vd on visit.pcucode = vd.pcucode AND visit.visitno = vd.visitno
 left JOIN (select * from visitlabchcyhembmsse where visitlabchcyhembmsse.labcode ='CH99') as vl ON visit.visitno = vl.visitno
 group by visit.visitno
 UNION
 SELECT
 visit.visitdate as l
 ,person.pid AS m
 ,concat(ctitle.titlename,person.fname," ",person.lname) AS n
 ,getAgeYearNum(person.birth,CURDATE()) AS o
 ,house.hno AS p
 ,right(house.villcode,2) AS q
 ,GROUP_CONCAT(case when pc.chroniccode like 'E1%' THEN 'เบาหวาน' when pc.chroniccode like 'I1%' THEN 'ความดันโลหิตสูง' else '' end) as r
 ,visit.symptoms as s
 ,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 t1
 ,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 t2
 ,visitlabsugarblood.sugarnumdigit as u
 ,vl.labresultdigit as v
 FROM
 person
 LEFT JOIN ctitle ON ctitle.titlecode = person.prename
 INNER JOIN house ON person.pcucodeperson = house.pcucode AND person.hcode = house.hcode
 INNER JOIN (SELECT * FROM personchronic WHERE personchronic.chroniccode LIKE 'I1%' or personchronic.chroniccode LIKE 'E1%') AS pc ON person.pid = pc.pid
 LEFT JOIN visit ON person.pcucodeperson = visit.pcucode AND person.pid = visit.pid
 LEFT JOIN visitlabsugarblood ON visit.pcucode = visitlabsugarblood.pcucode AND visit.visitno = visitlabsugarblood.visitno
 INNER JOIN (select * from visitdiag where visitdiag.diagcode like 'I1%' or visitdiag.diagcode like 'E1%') as vd on visit.pcucode = vd.pcucode AND visit.visitno = vd.visitno
 left JOIN (select * from visitlabchcyhembmsse where visitlabchcyhembmsse.labcode ='CH99') as vl ON visit.visitno = vl.visitno
 group by visit.visitno) as q1
 order by q1.a;