HOSxP ตรวจสอบการได้รับวัคซีน
select p.person_id,CONCAT(p.pname,p.fname," ",p.lname) as ptname,
concat( timestampdiff(year,birthdate,NOW() ) ," y ",
timestampdiff(month,birthdate,NOW() )-(timestampdiff(year,birthdate,NOW() )*12) ," m ",
timestampdiff(day,date_add(birthdate,interval (timestampdiff(month,birthdate,NOW() )) month),NOW() ) ," d ") as age,
h.address h_no,v.village_moo v_no ,p.birthdate,p.house_regist_type_id type,
(select GROUP_CONCAT( DISTINCT pv.export_vaccine_code,' - ','(',pv.vaccine_code,')') from person_vaccine pv
LEFT JOIN person_vaccine_elsewhere pve on pve.person_vaccine_id = pv.person_vaccine_id
where p.person_id = pve.person_id
GROUP BY p.cid ) as 'all_vaccine_other',
(select GROUP_CONCAT( DISTINCT wv.export_vaccine_code,' - ','(',wv.wbc_vaccine_code,')')
from wbc_vaccine wv
left JOIN person_wbc_vaccine_detail pwvs ON wv.wbc_vaccine_id = pwvs.wbc_vaccine_id
left JOIN person_wbc_service pws ON pws.person_wbc_service_id = pwvs.person_wbc_service_id
left JOIN person_wbc pw ON pw.person_wbc_id = pws.person_wbc_id
where p.person_id = pw.person_id
GROUP BY p.cid ) as 'all_vaccine_in_0-1y' ,
(select GROUP_CONCAT( DISTINCT ev.export_vaccine_code,' - ','(',ev.vaccine_code,')')
FROM epi_vaccine ev
left JOIN person_epi_vaccine_list pel ON ev.epi_vaccine_id = pel.epi_vaccine_id
left JOIN person_epi_vaccine pev ON pel.person_epi_vaccine_id = pev.person_epi_vaccine_id
left JOIN person_epi pe ON pev.person_epi_id = pe.person_epi_id
where p.person_id = pe.person_id
GROUP BY p.cid ) as 'all_vaccine_in_1-5y'
FROM person p
left JOIN house h ON p.house_id = h.house_id
left JOIN village v ON v.village_id = h.village_id
where TIMESTAMPDIFF(MONTH,p.birthdate,now())between 0 and 60 and p.death = 'N' AND p.person_discharge_id =9
and v.village_moo <> "0" AND p.nationality = 99 and p.house_regist_type_id IN (1,3)
group by p.person_id
order by p.birthdate DESC
ไม่มีความคิดเห็น:
แสดงความคิดเห็น