ตรวจสอบการฝากครรถ์ 5 ครั้งคุณภาพ
SELECT p.person_id,p.cid,CONCAT(p.pname,p.fname," ",p.lname) name_mother,
h.address,v.village_moo as moo ,Year(Now())-Year(p.birthdate) age,a.preg_no,
CONCAT (substr(a.lmp,9,2),"-",substr(a.lmp,6,2),"-",substr(a.lmp,1,4)+543) lmp,
CONCAT (substr(a.edc,9,2),"-",substr(a.edc,6,2),"-",substr(a.edc,1,4)+543) edc,
if (count(DISTINCT case when p.cid and ps.pa_week BETWEEN "1" and "12" or ao.precare_no =1
then p.house_id = h.house_id else null end)
in ('1','2','3','4'),'1',null) '<12w_1',
if (count(DISTINCT case when p.cid and ps.pa_week BETWEEN "16" and "20" or ao.precare_no =2
then p.house_id = h.house_id else null end)
in ('1','2','3','4'),'1',null) '16-20w_2',
if (count(DISTINCT case when p.cid and ps.pa_week BETWEEN "24" and "28" or ao.precare_no =3
then p.house_id = h.house_id else null end)
in ('1','2','3','4'),'1',null) '24-28w_3',
if (count(DISTINCT case when p.cid and ps.pa_week BETWEEN "30" and "34" or ao.precare_no =4
then p.house_id = h.house_id else null end)
in ('1','2','3','4'),'1',null) '30-34w_4',
if (count(DISTINCT case when p.cid and ps.pa_week BETWEEN "36" and "40" or ao.precare_no =5
then p.house_id = h.house_id else null end)
in ('1','2','3','4'),'1',null) '36-40w_5',
concat(round(if(count(DISTINCT case when p.cid and ps.pa_week BETWEEN "1" and "12" or ao.precare_no =1
then p.house_id = h.house_id else null end)
in ('1','2','3','4'),'1','0'))
+ if (count(DISTINCT case when p.cid and ps.pa_week BETWEEN "16" and "20" or ao.precare_no =2
then p.house_id = h.house_id else null end)
in ('1','2','3','4'),'1','0')
+ if (count(DISTINCT case when p.cid and ps.pa_week BETWEEN "24" and "28" or ao.precare_no =3
then p.house_id = h.house_id else null end)
in ('1','2','3','4'),'1','0')
+ if (count(DISTINCT case when p.cid and ps.pa_week BETWEEN "30" and "34" or ao.precare_no =4
then p.house_id = h.house_id else null end)
in ('1','2','3','4'),'1','0')
+ if (count(DISTINCT case when p.cid and ps.pa_week BETWEEN "36" and "40" or ao.precare_no =5
then p.house_id = h.house_id else null end)
in ('1','2','3','4'),'1','0') )total,
if( concat(round(if(count(DISTINCT case when p.cid and ps.pa_week BETWEEN "1" and "12" or ao.precare_no =1
then p.house_id = h.house_id else null end)
in ('1','2','3','4'),'1','0'))) = 1,'Yes','No') result_anc_12w,
if( concat(round(if(count(DISTINCT case when p.cid and ps.pa_week BETWEEN "1" and "12" or ao.precare_no =1
then p.house_id = h.house_id else null end)
in ('1','2','3','4'),'1','0'))
+ if (count(DISTINCT case when p.cid and ps.pa_week BETWEEN "16" and "20" or ao.precare_no =2
then p.house_id = h.house_id else null end)
in ('1','2','3','4'),'1','0')
+ if (count(DISTINCT case when p.cid and ps.pa_week BETWEEN "24" and "28" or ao.precare_no =3
then p.house_id = h.house_id else null end)
in ('1','2','3','4'),'1','0')
+ if (count(DISTINCT case when p.cid and ps.pa_week BETWEEN "30" and "34" or ao.precare_no =4
then p.house_id = h.house_id else null end)
in ('1','2','3','4'),'1','0')
+ if (count(DISTINCT case when p.cid and ps.pa_week BETWEEN "36" and "40" or ao.precare_no =5
then p.house_id = h.house_id else null end)
in ('1','2','3','4'),'1','0') ) = 5,'Yes','No') result_anc_5,
CONCAT (substr(a.labor_date,9,2),"-",substr(a.labor_date,6,2),"-",substr(a.labor_date,1,4)+543) bir_baby,
DATEDIFF(( SELECT ac.care_date FROM person_anc_preg_care ac
LEFT JOIN person_anc aa on ac.person_anc_id = aa.person_anc_id
where aa.person_id = p.person_id and aa.person_anc_id = a.person_anc_id and
ac.preg_care_number = 1 order by ac.care_date desc LIMIT 0,1 ),labor_date) m_1,
( SELECT concat(substr(ac.care_date,9,2),"-",substr(ac.care_date,6,2),"-",substr(ac.care_date,1,4)+543)ac FROM person_anc_preg_care ac
LEFT JOIN person_anc aa on ac.person_anc_id = aa.person_anc_id
where aa.person_id = p.person_id and aa.person_anc_id = a.person_anc_id and
ac.preg_care_number = 1 order by ac.care_date desc LIMIT 0,1 ) mother_care_1,
DATEDIFF(( SELECT ac.care_date FROM person_anc_preg_care ac
LEFT JOIN person_anc aa on ac.person_anc_id = aa.person_anc_id
where aa.person_id = p.person_id and aa.person_anc_id = a.person_anc_id and
ac.preg_care_number = 2 order by ac.care_date desc LIMIT 0,1 ),labor_date) m_2,
(SELECT concat(substr(ac.care_date,9,2),"-",substr(ac.care_date,6,2),"-",substr(ac.care_date,1,4)+543) ac FROM person_anc_preg_care ac
LEFT JOIN person_anc aa on ac.person_anc_id = aa.person_anc_id
where aa.person_id = p.person_id and aa.person_anc_id = a.person_anc_id and
ac.preg_care_number = 2 order by ac.care_date desc LIMIT 0,1 ) mother_care_2,
DATEDIFF(( SELECT ac.care_date FROM person_anc_preg_care ac
LEFT JOIN person_anc aa on ac.person_anc_id = aa.person_anc_id
where aa.person_id = p.person_id and aa.person_anc_id = a.person_anc_id and
ac.preg_care_number = 3 order by ac.care_date desc LIMIT 0,1 ),labor_date) m_3,
(SELECT concat(substr(ac.care_date,9,2),"-",substr(ac.care_date,6,2),"-",substr(ac.care_date,1,4)+543) ac FROM person_anc_preg_care ac
LEFT JOIN person_anc aa on ac.person_anc_id = aa.person_anc_id
where aa.person_id = p.person_id and aa.person_anc_id = a.person_anc_id and
ac.preg_care_number = 3 order by ac.care_date desc LIMIT 0,1 ) mother_care_3,
concat(substr(a.force_complete_date,9,2),"-",substr(a.force_complete_date,6,2),"-",substr(a.force_complete_date,1,4)+543) mother_send_43f,
CONCAT(p2.pname,p2.fname," ",p2.lname) name_baby,
DATEDIFF(( SELECT pc.care_date FROM person_wbc_post_care pc
LEFT JOIN person_wbc pw on pc.person_wbc_id = pw.person_wbc_id
where pw.person_id = p2.person_id and pc.care_number= 1 order by pc.care_date desc LIMIT 0,1),labor_date) b_1,
( SELECT concat(substr( pc.care_date,9,2),"-",substr(pc.care_date,6,2),"-",substr(pc.care_date,1,4)+543)pc FROM person_wbc_post_care pc
LEFT JOIN person_wbc pw on pc.person_wbc_id = pw.person_wbc_id
where pw.person_id = p2.person_id and pc.care_number= 1 order by pc.care_date desc LIMIT 0,1 ) baby_care_1,
DATEDIFF(( SELECT pc.care_date FROM person_wbc_post_care pc
LEFT JOIN person_wbc pw on pc.person_wbc_id = pw.person_wbc_id
where pw.person_id = p2.person_id and pc.care_number= 2 order by pc.care_date desc LIMIT 0,1),labor_date) b_2,
( SELECT concat(substr( pc.care_date,9,2),"-",substr(pc.care_date,6,2),"-",substr(pc.care_date,1,4)+543)pc FROM person_wbc_post_care pc
LEFT JOIN person_wbc pw on pc.person_wbc_id = pw.person_wbc_id
where pw.person_id = p2.person_id and pc.care_number= 2 order by pc.care_date desc LIMIT 0,1 ) baby_care_2,
DATEDIFF(( SELECT pc.care_date FROM person_wbc_post_care pc
LEFT JOIN person_wbc pw on pc.person_wbc_id = pw.person_wbc_id
where pw.person_id = p2.person_id and pc.care_number= 3 order by pc.care_date desc LIMIT 0,1),labor_date) b_3,
( SELECT concat(substr( pc.care_date,9,2),"-",substr(pc.care_date,6,2),"-",substr(pc.care_date,1,4)+543)pc FROM person_wbc_post_care pc
LEFT JOIN person_wbc pw on pc.person_wbc_id = pw.person_wbc_id
where pw.person_id = p2.person_id and pc.care_number= 3 order by pc.care_date desc LIMIT 0,1 ) baby_care_3,
concat(substr(pw.force_complete_date,9,2),"-",substr(pw.force_complete_date,6,2),"-",substr(pw.force_complete_date,1,4)+543) baby_send_43f
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
LEFT JOIN person_anc a ON a.person_id = p.person_id
left JOIN person_anc_preg_care ac ON ac.person_anc_id = a.person_anc_id
LEFT JOIN person_anc_service ps ON ps.person_anc_id = a.person_anc_id
left JOIN person_anc_other_precare ao on ao.person_anc_id = a.person_anc_id
left OUTER JOIN person p2 ON p2.birthdate = a.labor_date and p2.mother_person_id = p.person_id
LEFT OUTER JOIN person_wbc pw on pw.person_id = p2.person_id
LEFT OUTER JOIN person_wbc_post_care pc ON pc.person_wbc_id = pw.person_wbc_id
WHERE v.village_moo <> 0 AND p.death = "N" and a.edc > 20140930
GROUP BY a.person_anc_id
ORDER BY a.lmp asc
ไม่มีความคิดเห็น:
แสดงความคิดเห็น