SELECT
	person.idcard AS `เลขประจำตัวประชาชน`,
	person.pid AS HN,
	concat(
		ctitle.titlename,
		person.fname,
		" ",
		person.lname
	) AS `ชื่อ-สกุล`,
	person.sex AS `รหัสเพศ`,
	person.birth AS `ว.ด.ป.เกิด`,
	person.marystatus AS `รหัสสถานะภาพสมรส`,
	person.occupa AS `รหัสอาชีพ Jhcis`,
	coccupa.mapoccupa AS `รหัสอาชีพสำหรับส่งออก 21 แฟ้ม`,
	person.nation AS `รหัสสัญชาติ`,
	person.religion AS `รหัสศาสนา`,
	person.educate AS `รหัสการศึกษา`,
	person.dischargetype AS `รหัสสถานะการจำหน่าย`,
	person.typelive AS `รหัสสถานะการอยู่อาศัย`,
	max(visit.visitdate) AS 'last visit',
  person.dateupdate
FROM
	person
INNER JOIN ctitle ON ctitle.titlecode = person.prename
INNER JOIN coccupa ON person.occupa = coccupa.occupacode
INNER JOIN visit ON person.pcucodeperson = visit.pcucodeperson
AND person.pid = visit.pid
WHERE
	person.idcard IS NULL
OR length(person.idcard) <> 13
OR person.sex IS NULL
OR person.sex NOT IN ('1', '2')
OR person.birth IS NULL
OR person.marystatus IS NULL
OR person.occupa IS NULL
OR LENGTH(coccupa.mapoccupa) <> 4
OR person.nation IS NULL
OR person.dischargetype IS NULL
OR person.educate IS NULL
OR person.typelive IS NULL
OR person.dateupdate> '2015-09-30'
GROUP BY
	person.pid
ORDER BY
	person.pid;