โรงพยาบาลจอมพระ

select concat(p.pname,p.fname,’ ‘,p.lname)as name,a.labor_date,(case when s.hn > 1 then 1 else s.hn end)as test,s.hn,s.vn,s.service_date,s.in_hospcode_service,d.icd10,h.*
from health_med_service_operation h
left join health_med_service s on s.health_med_service_id=h.health_med_service_id
left join health_med_service_diagnosis d on d.health_med_service_id=h.health_med_service_id
left join person p on p.patient_hn=s.hn
left join person_anc a on a.person_id=p.person_id

where h.health_med_operation_item_id in (‘8′) and s.service_date>’2014-06-30’
/*group by s.hn */
order by s.hn