SELECT
person.pid,
person.idcard,
concat(ctitle.titlename,person.fname," ",person.lname)as"ชื่อ-สกุล",
DATE_FORMAT(person.birth,'%d-%m-%Y')as วันเกิด,
getAgeYearNum(person.birth,'20131001') AS age,house.hno, right((house.villcode),2) AS moo
,convert(concat('หมู่ที่ ',village.villno,' ',village.villname ) USING utf8 )as village
,MAX(vlb.datecheck) as"วันเดือนปีที่ตรวจมะเร็งเต้านมครั้งหลังสุด"
,case when vlb.result ='0' THEN 'ปกติ' when vlb.result ='x' then 'ผิดปกติ' ELSE null end as 'ผลการคัดกรอง'
,chospital.hosname as 'หน่วยงานที่ตรวจ'
,CONCAT(p1.fname," ",p1.lname)as 'อสม'
FROM person INNER JOIN house ON person.hcode = house.hcode and person.pcucodeperson = house.pcucode
INNER JOIN village on house.villcode = village.villcode
INNER JOIN ctitle ON person.prename = ctitle.titlecode
LEFT JOIN (select* from visitlabcancer where visitlabcancer.typecancer='1' and visitlabcancer.datecheck between '20141001' and '20150930' )as vlb
on person.pid=vlb.pid and person.pcucodeperson=vlb.pcucodeperson
LEFT JOIN chospital on vlb.hosservice=chospital.hoscode
LEFT JOIN person as p1 on house.pidvola=p1.pid and house.pcucodepersonvola=p1.pcucodeperson
WHERE
((person.dischargetype is null) OR (person.dischargetype != '1'))
AND getAgeYearNum(person.birth,'20141001') BETWEEN 30 AND 70
AND person.sex IN ('2')
AND right((house.villcode),2) != 00 # เป็นหมู่บ้านในเขตบริการ
and person.typelive in('1','3','0')
GROUP BY person.pid
ORDER BY moo,hno*1,age