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

select pv.vaccine_name,pl.vaccine_date,concat(p.pname,p.fname,’ ‘,p.lname)as name,concat(p.age_y,’-‘,p.age_m,’-‘,p.age_d)as age,p.house_regist_type_id,o.vstdate,o.hn,o.icode,(di.name)as namedrug
from opitemrece o
left join person p on p.patient_hn=o.hn
left join person_vaccine_list pl on pl.person_id=p.person_id and pl.vaccine_date=o.vstdate
left join person_vaccine pv on pv.person_vaccine_id=pl.person_vaccine_id
left join drugitems di on di.icode=o.icode
where o.vstdate between ‘2014-01-01’ and ‘2014-08-31′ and (o.icode=’1570007′) and pl.person_vaccine_id in (’14’)
and p.house_regist_type_id in (‘1′,’3’)

select pr.health_med_provider_id,pr.health_med_provider_full_name,concat(p.pname,p.fname,’ ‘,p.lname)as name,a.labor_date,
count(pr.health_med_provider_id and s.service_date>’5′)as nummm,s.hn,s.vn,s.service_date,
s.in_hospcode_service,d.icd10,i.health_med_operation_item_name,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
left join health_med_provider pr on pr.health_med_provider_id=h.health_med_provider_id
left join health_med_operation_item i on i.health_med_operation_item_id=h.health_med_operation_item_id

where h.health_med_operation_item_id in (’33’) and s.service_date >’2014-03-08′
group by pr.health_med_provider_id,s.service_date
order by nummm desc

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

select p.patient_hn,p.cid,concat(p.pname,p.fname,’ ‘,p.lname)as name,p.age_y,concat(h.address,’ M.’,vl.village_moo,’ B. ‘,vl.village_name)as address,
vs.school_name,sc.village_school_class_name,(case when sv.student_vaccine_id in (‘5’) then vsv.vaccine_date else ‘NULL’ end)as dTs4,
v.*
from village_student v
left join village_school vs on vs.village_school_id=v.village_school_id
left join village_school_class sc on sc.village_school_class_id=v.village_school_class_id
left join person p on p.person_id=v.person_id
left join house h on h.house_id=p.house_id
left join village vl on vl.village_id=p.village_id
left join village_student_vaccine vsv on vsv.village_student_id=v.village_student_id
left join village_student_vaccine_list vsvl on vsvl.village_student_vaccine_id=vsv.village_student_vaccine_id
left join student_vaccine sv on sv.student_vaccine_id=vsvl.student_vaccine_id
where sc.village_school_class_id in (‘9’)
order by vs.school_name

select vs.school_name,sc.village_school_class_name,v.*
from village_student v
left join village_school vs on vs.village_school_id=v.village_school_id
left join village_school_class sc on sc.village_school_class_id=v.village_school_class_id
where sc.village_school_class_id in (‘9’)