SELECT
person.pid,
person.idcard,
ctitle.titlename,
person.fname,
person.lname,
CONCAT(DATE_FORMAT(person.birth,'%d-%m-'),DATE_FORMAT(person.birth,'%Y')+543)as วันเกิด,
getAgeYearNum(person.birth,('2014-07-01')) AS age,
house.hno, right((house.villcode),2) AS moo,
personchronic.chroniccode,
personchronic.cup,
GROUP_CONCAT(cdrug.drugname),
GROUP_CONCAT(sysdrugdose.dosedescription)
FROM person
INNER JOIN house ON person.hcode = house.hcode
INNER JOIN ctitle ON person.prename = ctitle.titlecode
INNER JOIN personchronic on person.pid=personchronic.pid
INNER JOIN visit   ON person.pcucodeperson = visit.pcucodeperson AND person.pid = visit.pid
INNER JOIN visitdrug ON visit.visitno=visitdrug.visitno
LEFT JOIN cdrug ON visitdrug.drugcode=cdrug.drugcode
INNER JOIN sysdrugdose ON visitdrug.drugcode=sysdrugdose.drugcode
LEFT JOIN persondeath ON person.pid = persondeath.pid
where personchronic.cup=person.pcucodeperson
AND persondeath.pid is null
AND cdrug.drugtype='01'
AND visit.visitdate BETWEEN '20141001' AND '20150930'
GROUP BY person.pid;
 