select
concat(ctitle.titlename,person.fname,' ',person.lname)as 'ชื่อ - สกุล',
person.birth as birth,
GetAgeYearNum(person.birth,visit.visitdate)as age,
person.hnomoi AS 'บ้านเลขที่',
person.mumoi AS 'หมู่',
max(case when personchronic.chroniccode = 'I10' then "/" else ' ' end) as HT,
max(case when personchronic.chroniccode between 'E10' and 'E15' then "/" else ' ' end) as DM,
max(CASE WHEN visit.visitdate between '2014-10-01' and '2014-10-31' THEN visitdate ELSE null END) as '10_57',
max(CASE WHEN visit.visitdate between '2014-10-01' and '2014-10-31' THEN visit.pressure ELSE null END) as 'BP10_57',
max(case when visitlabsugarblood.foodsuspend = '0' AND visit.visitdate between '2013-09-01' and '2013-09-30' 
then visitlabsugarblood.sugarnumdigit else NULL end ) as 'DTX,FBS1',
max(CASE WHEN visit.visitdate between '2013-10-01' and '2013-10-31' THEN visitdate ELSE null END) as '10_56',
max(CASE WHEN visit.visitdate between '2013-10-01' and '2013-10-31' THEN visit.pressure ELSE null END) as 'BP10_56',
max(case when visitlabsugarblood.foodsuspend = '0'AND visit.visitdate between '2013-10-01' and '2013-10-31' 
then visitlabsugarblood.sugarnumdigit else NULL end ) as 'DTX,FBS2',
max(CASE WHEN visit.visitdate between '2013-11-01' and '2013-11-30' THEN visitdate ELSE null END) as '11_56',
max(CASE WHEN visit.visitdate between '2013-11-01' and '2013-11-30' THEN visit.pressure ELSE null END) as 'BP11_56',
max(case when visitlabsugarblood.foodsuspend = '0'AND visit.visitdate between '2013-11-01' and '2013-11-30' 
then visitlabsugarblood.sugarnumdigit else NULL end ) as 'DTX,FBS3',
max(CASE WHEN visit.visitdate between '2013-12-01' and '2013-12-31' THEN visitdate ELSE null END) as '12_56',
max(CASE WHEN visit.visitdate between '2013-12-01' and '2013-12-31' THEN visit.pressure ELSE null END) as 'BP12_56',
max(case when visitlabsugarblood.foodsuspend = '0'AND visit.visitdate between '2013-12-01' and '2013-12-31' 
then visitlabsugarblood.sugarnumdigit else NULL end ) as 'DTX,FBS4',
max(CASE WHEN visit.visitdate between '2014-01-01' and '2014-01-31' THEN visitdate ELSE null END) as '01_57',
max(CASE WHEN visit.visitdate between '2014-01-01' and '2014-01-31' THEN visit.pressure ELSE null END) as 'BP01_57',
max(case when visitlabsugarblood.foodsuspend = '0'AND visit.visitdate between '2014-01-01' and '2014-01-31' 
then visitlabsugarblood.sugarnumdigit else NULL end ) as 'DTX,FBS5',
max(CASE WHEN visit.visitdate between '2014-02-01' and '2014-02-28' THEN visitdate ELSE null END) as '02_57',
max(CASE WHEN visit.visitdate between '2014-02-01' and '2014-02-28' THEN visit.pressure ELSE null END) as 'BP02_57',
max(case when visitlabsugarblood.foodsuspend = '0'AND visit.visitdate between '2014-02-01' and '2014-02-28' 
then visitlabsugarblood.sugarnumdigit else NULL end ) as 'DTX,FBS6',
max(CASE WHEN visit.visitdate between '2014-03-01' and '2014-03-31' THEN visitdate ELSE null END) as '03_57',
max(CASE WHEN visit.visitdate between '2014-03-01' and '2014-03-31' THEN visit.pressure ELSE null END) as 'BP03_57',
max(case when visitlabsugarblood.foodsuspend = '0'AND visit.visitdate between '2014-03-01' and '2014-03-31'
then visitlabsugarblood.sugarnumdigit else NULL end ) as 'DTX,FBS7'
from
person INNER JOIN visit ON visit.pcucodeperson = person.pcucodeperson AND visit.pid = person.pid
INNER JOIN ctitle on person.prename = ctitle.titlecode
INNER JOIN visitdiag ON visitdiag.pcucode = visit.pcucode AND visitdiag.visitno = visit.visitno
INNER JOIN visitlabsugarblood ON visitlabsugarblood.pcucode = visit.pcucode AND visitlabsugarblood.visitno = visit.visitno
LEFT JOIN personchronic ON personchronic.pcucodeperson = person.pcucodeperson AND personchronic.pid = person.pid
where visit.visitdate between '2013-09-01' and '2014-09-30' AND 
(visitdiag.diagcode between 'E10' and 'E15' or visitdiag.diagcode = 'I10') and visit.pressure is not null
and CONCAT(person.pid,person.pcucodeperson) NOT IN (select CONCAT(persondeath.pid,persondeath.pcucodeperson)from persondeath)
group by person.pid
order by visit.visitdate;