select'sum_total', v.village_moo,v.village_name,a.target,a.result
,concat(round((result
)*100/(target),2),'%' ) as '%',
if( concat(round(result
)*100/(target))> 95,'Yes','No') outcome,null,
DATE_FORMAT(NOW(),'%d-%m-%Y %H:%i:%s') AS com_lastupdate
from village v
left join (select p.village_id
,count(distinct p.cid) target
,count(distinct if( p.birthdate BETWEEN '2008-06-01' and '2012-01-31' and
ov.vstdate BETWEEN "2015-05-01" and "2015-09-30" AND pv.export_vaccine_code = 073,p.cid,null)) result
from person p
left JOIN village v on v.village_id = p.village_id and v.village_moo <> 0
left JOIN ovst ov ON ov.hn = p.patient_hn
left JOIN ovst_vaccine ovs ON ovs.vn = ov.vn
left JOIN person_vaccine pv ON pv.person_vaccine_id = ovs.person_vaccine_id
where p.birthdate BETWEEN '2008-06-01' and '2012-01-31' and p.house_regist_type_id IN (1,3) and
p.person_discharge_id = 9 AND p.death ='N'and p.nationality =99 group by p.village_id ) a on a.village_id = v.village_id where v.village_moo <> 0
union select '',' ','total',a.target,a.result
,concat(round((result
)*100/(target),2),'%' ) as '%',
if( concat(round(result
)*100/(target))> 95,'Yes','No') outcome,'',
DATE_FORMAT(NOW(),'%d-%m-%Y %H:%i:%s') AS com_lastupdate
from village v
left join (select p.village_id
,count(distinct p.cid) target
,count(distinct if( p.birthdate BETWEEN '2008-06-01' and '2012-01-31' and
ov.vstdate BETWEEN "2015-05-01" and "2015-09-30" AND pv.export_vaccine_code = 073,p.cid,null)) result
from person p
left JOIN village v on v.village_id = p.village_id and v.village_moo <> 0
left JOIN ovst ov ON ov.hn = p.patient_hn
left JOIN ovst_vaccine ovs ON ovs.vn = ov.vn
left JOIN person_vaccine pv ON pv.person_vaccine_id = ovs.person_vaccine_id
where p.birthdate BETWEEN '2008-06-01' and '2012-01-31' and p.house_regist_type_id IN (1,3) and
p.person_discharge_id = 9 AND p.death ='N'and p.nationality =99 ) a on a.village_id = v.village_id where v.village_moo <> 0
UNION SELECT 'person','HN','p_name','address','moo','age','outcome','cid','com_lastupdate'
UNION SELECT 'person', p.person_id,CONCAT(p.pname,p.fname," ",p.lname) AS p_name,
h.address as h_on,v.village_moo as v_on, 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,
if (count(DISTINCT case when p.cid and pv.export_vaccine_code = 073 and o.vstdate BETWEEN "2015-05-01" and "2015-09-30"
then p.patient_hn = o.hn else null end)
in ('1','2','3','4'),'Yes','No') out_put
,p.cid,DATE_FORMAT(NOW(),'%d-%m-%Y %H:%i:%s') AS day_proccss
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 ovst o ON p.patient_hn = o.hn
left JOIN ovst_vaccine ovs ON ovs.vn = o.vn
left JOIN person_vaccine pv ON pv.person_vaccine_id = ovs.person_vaccine_id
WHERE p.birthdate between '2008-06-01' and '2012-01-31' and p.house_regist_type_id IN (1,3) AND p.person_discharge_id = 9 AND
v.village_moo <> 0 and p.death = 'N' and p.nationality = 099
GROUP BY p.person_id