SELECT p.idcard
, p.pid AS HN
, CONCAT(ctitle.titlename,p.fname,' ',p.lname) AS `ชื่อ`
, getAgeYearNum(p.birth,'2014-10-01') AS `อายุ`
, h.hno
, RIGHT(h.villcode, 2) AS `หมู่ที่`
, cd.subdistname AS `ตำบล`
, ve1.TT1
, ve1.TT2
, ve1.TT3
, p1.osm AS `อสม`
, p.telephoneperson AS `โทรศัพท์`
, Concat(`chospital`.`hosname`,' จ.',`cprovince`.`provname`) AS `หน่วยบริการ`
FROM
person p
INNER JOIN house h ON p.pcucodeperson = h.pcucode
AND p.hcode = h.hcode
INNER JOIN ctitle ON p.prename = ctitle.titlecode
INNER JOIN csubdistrict cd ON LEFT(h.villcode, 2)= cd.provcode
AND SUBSTR(h.villcode, 3, 2)= cd.distcode
AND SUBSTR(h.villcode, 5, 2)= cd.subdistcode
inner join chospital on p.pcucodeperson=chospital.hoscode
Inner Join `cprovince` ON `chospital`.`provcode` = `cprovince`.`provcode`
LEFT JOIN(
SELECT
*
FROM
(
SELECT
visitepi.pcucodeperson,
visitepi.pid,
max(
CASE
WHEN cdrug.files18epi IN( '106', '201')THEN
dateepi
ELSE
NULL
END
)AS TT1,
max(
CASE
WHEN cdrug.files18epi IN('107', '202')THEN
dateepi
ELSE
NULL
END
)AS TT2,
max(
CASE
WHEN cdrug.files18epi IN( '108', '901')THEN
dateepi
ELSE
NULL
END
)AS TT3
FROM
visitepi







INNER JOIN cdrug ON visitepi.vaccinecode = cdrug.drugcode
GROUP BY
visitepi.pid
)AS ve
WHERE
TT1 IS NOT NULL
OR TT2 IS NOT NULL
OR TT3 IS NOT NULL
)AS ve1 ON p.pcucodeperson = ve1.pcucodeperson
AND p.pid = ve1.pid
LEFT JOIN(
SELECT
person.pcucodeperson,
person.pid,
concat(
person.fname,
' ',
person.lname
)AS osm
FROM
person
)AS p1 ON h.pcucodepersonvola = p1.pcucodeperson
AND h.pidvola = p1.pid
WHERE
CONCAT(p.pcucodeperson, p.pid)NOT IN(
SELECT
concat(
persondeath.pcucodeperson,
persondeath.pid
)
FROM
persondeath
)
AND p.typelive IN('1', '3')
AND getAgeYearNum(p.birth,concat('2014-10-01')) BETWEEN 20 AND 50
ORDER BY RIGHT(h.villcode, 2), h.pidvola, h.hno, getAgeYearNum(p.birth,'2014-10-01') ASC;