SELECT
' ' AS `ลำดับที่`,
visitanc.pid AS PID,
concat(ctitle.titlename,person.fname,' ',person.lname) AS `ชื่อ - สกุล`,
getAgeYearNum(person.birth,CURDATE()) AS อายุ,
house.hno AS `บ้านเลขที่`,
village.villno AS `หมู่ที่`,
visitanc.datecheck AS `วันที่รับบริการ`,
visitanc.pcucode AS `สถานบริการ`,
visitanc.pregno AS `ครรภ์ที่`,
visitancpregnancy.lmp,
visitancpregnancy.edc,
max(case when visitanc.pregage BETWEEN 1 and 12 THEN visitanc.datecheck else NULL end)as 'ANC ช่วงที่ 1' ,
max(case when visitanc.pregage BETWEEN 16 and 20 THEN visitanc.datecheck else NULL end)as 'ANC ช่วงที่ 2' ,
max(case when visitanc.pregage BETWEEN 24 and 28 THEN visitanc.datecheck else NULL end)as 'ANC ช่วงที่ 3' ,
max(case when visitanc.pregage BETWEEN 30 and 34 THEN visitanc.datecheck else NULL end)as 'ANC ช่วงที่ 4' ,
max(case when visitanc.pregage BETWEEN 36 and 40 THEN visitanc.datecheck else NULL end)as 'ANC ช่วงที่ 5' ,

min(visitanc.pregage) as 'อายุครรภ์',
case when visitanc.ancres = 0 THEN 'ปกติ'
when visitanc.ancres = 1 THEN 'ผิดปกติ' else null end as 'ผลการตรวจ'
FROM
person
INNER JOIN house ON person.pcucodeperson = house.pcucode AND person.hcode = house.hcode
INNER JOIN village ON house.pcucode = village.pcucode AND house.villcode = village.villcode
INNER JOIN visitanc ON visitanc.pcucodeperson = person.pcucodeperson AND visitanc.pid = person.pid
INNER JOIN ctitle ON ctitle.titlecode = person.prename
INNER JOIN visitancpregnancy ON visitancpregnancy.pcucodeperson = visitanc.pcucodeperson AND visitancpregnancy.pid = visitanc.pid
WHERE
RIGHT(village.villcode,2)<> '00' and person.pid not in (select persondeath.pid from persondeath) and person.typelive IN ('1', '3')
and visitanc.datecheck BETWEEN 20131001 and 20140430
GROUP BY
person.pid;