select
person.pid,
concat(ctitle.titlename,person.fname,' ',person.lname)as 'ชื่อ-นามสกุล',
person.idcard as 'เลขบัตรประชาชน',
person.hnomoi as 'บ้านเลขที่',
village.villname as 'หมู่ที่',
v.pregno,visitancpregnancy.lmp,visitancpregnancy.edc,
ROUND(DATEDIFF(current_date,visitancpregnancy.lmp)/7) as 'อายุครรภ์-สัปดาห์'
from
person inner join ctitle on person.prename = ctitle.titlecode
inner join house on person.pcucodeperson = house.pcucode and person.hcode = house.hcode
inner join village on house.pcucode = village.pcucode and village.villcode = house.villcode
inner join visitanc v on person.pid = v.pid
inner join visitancpregnancy on v.pid = visitancpregnancy.pid and v.pregno = visitancpregnancy.pregno
where person.pid not in (select persondeath.pid from persondeath) and v.pid not in (select visitancdeliver.pid from visitancdeliver)
and ROUND(DATEDIFF(current_date,visitancpregnancy.lmp)/7)> '40'
group by person.pid
#create by a team