SELECT o.oapp_id,concat(p.pname,p.fname,' ',p.lname) AS ptname, c.name AS clinic_name,
d.name AS doctor_name, o.hn,o.vstdate,o.nextdate,o.nexttime,o.note,o.vn,k.department,
COUNT(v.hn) AS visit_count
FROM oapp o
LEFT OUTER JOIN patient p ON p.hn=o.hn
LEFT OUTER JOIN clinic c ON c.clinic=o.clinic
LEFT OUTER JOIN doctor d ON d.code=o.doctor
LEFT OUTER JOIN kskdepartment k ON k.depcode = o.depcode
LEFT OUTER JOIN ovst v ON v.vstdate=o.nextdate AND v.hn=o.hn
WHERE o.nextdate BETWEEN '2007-06-01' AND '2007-06-30' AND o.clinic='002'
AND o.hn NOT IN (SELECT hn FROM ovst WHERE vstdate BETWEEN '2007-06-01' AND '2007-06-30')
GROUP BY o.oapp_id, p.pname, p.fname, p.lname, c.name, d.name, o.hn, o.vstdate,
o.nextdate, o.nexttime, o.note, o.vn, k.department