SELECT
	visit.pcucode,
	substr(visit.visitdate, 6, 2) AS เดือน,
	person.pid AS hn,
	visit.visitno AS vn,
	ctitle.titlename,
	person.fname,
	person.lname,
	substr(visit.dateupdate, 1, 10) AS วันที่คีย์,
	date_format(visit.visitdate, '%Y-%m-%d') AS วันรับบริการ,
	timestampdiff(
		DAY,
		visit.visitdate,
		visit.dateupdate
	) AS "คียย้อนหลัง(วัน)",
	visit.username AS ผู้คีย์,
	group_concat(visitdiag.diagcode) AS diag
FROM
	visit
INNER JOIN person ON visit.pid = person.pid
AND visit.pcucode = person.pcucodeperson
LEFT JOIN ctitle ON person.prename = ctitle.titlecode
INNER JOIN visitdiag ON visit.visitno = visitdiag.visitno
AND visit.pcucode = visitdiag.pcucode
WHERE
	visitdate BETWEEN '2013-10-01'
AND '2013-12-31'
AND timestampdiff(
	DAY,
	visit.visitdate,
	visit.dateupdate
) > 30
GROUP BY
	pcucode,
	เดือน,
	hn,
	vn
ORDER BY
	วันรับบริการ;