SELECT DISTINCT
person.pid AS HN,
concat(ctitle.titlename,person.fname,' ',person.lname) AS `ชื่อ-นามสกุล`,
person.idcard AS `เลขบัตรปชช`,
house.hno AS `บ้านเลขที่`,
village.villno AS `หมู่ที่`,
left(person.birth,4) + 543 AS `วันเกิด`,
person.mate AS `ชื่อคู่สมรส`,
CASE WHEN women.fptype = 1 THEN concat(substr(women.datesurvey,9,2),'/',substr(women.datesurvey,6,2),'/',substr(women.datesurvey,1,4)+543) ELSE NULL END AS 'ยาเม็ด',
CASE WHEN women.fptype = 2 THEN concat(substr(women.datesurvey,9,2),'/',substr(women.datesurvey,6,2),'/',substr(women.datesurvey,1,4)+543) ELSE NULL END AS 'ยาฉีด',
CASE WHEN women.fptype = 3 THEN concat(substr(women.datesurvey,9,2),'/',substr(women.datesurvey,6,2),'/',substr(women.datesurvey,1,4)+543) ELSE NULL END AS 'ยาฝัง',
CASE WHEN women.fptype = 4 THEN concat(substr(women.datesurvey,9,2),'/',substr(women.datesurvey,6,2),'/',substr(women.datesurvey,1,4)+543) ELSE NULL END AS 'ห่วง',
CASE WHEN women.fptype = 5 THEN concat(substr(women.datesurvey,9,2),'/',substr(women.datesurvey,6,2),'/',substr(women.datesurvey,1,4)+543) ELSE NULL END AS 'ถุงยาง',
CASE WHEN women.fptype = 6 THEN concat(substr(women.datesurvey,9,2),'/',substr(women.datesurvey,6,2),'/',substr(women.datesurvey,1,4)+543) ELSE NULL END AS 'หมันชาย',
CASE WHEN women.fptype = 7 THEN concat(substr(women.datesurvey,9,2),'/',substr(women.datesurvey,6,2),'/',substr(women.datesurvey,1,4)+543) ELSE NULL END AS 'หมันหญิง',
CASE WHEN women.fptype = 8 THEN concat(substr(women.datesurvey,9,2),'/',substr(women.datesurvey,6,2),'/',substr(women.datesurvey,1,4)+543) ELSE NULL END AS 'นับระยะปลอดภัย',
CASE WHEN women.fptype = 9 THEN concat(substr(women.datesurvey,9,2),'/',substr(women.datesurvey,6,2),'/',substr(women.datesurvey,1,4)+543) ELSE NULL END AS 'ไม่คุม'
FROM
village
INNER JOIN house ON house.pcucode = village.pcucode AND house.villcode = village.villcode
INNER JOIN person ON person.pcucodeperson = house.pcucode AND person.hcode = house.hcode
INNER JOIN women ON women.pid = person.pid AND women.pcucodeperson = person.pcucodeperson
INNER JOIN ctitle ON ctitle.titlecode = person.prename
WHERE
#village.villcode <> 770600 AND
person.typelive IN (1,3) AND
person.dischargetype = 9 AND
person.sex = 2 AND person.marystatus = 2 and
getAgeYearNum(birth,'20140101')BETWEEN '15' and '49';