select 'sum_total',v.village_moo,v.village_name,a.target,a.result
,concat(round((result
)*100/(target),2),'%' ) as '%-age',
if( concat(round(result
)*100/(target))> 60,'Yes','No') outcome,'cid',
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(timestampdiff(year,p.birthdate,now())between 60 and 200 and
ov.vstdate BETWEEN "2014-10-01" and "2015-09-30" and ov.icd10 = 'Z133',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 ovstdiag ov ON ov.hn = p.patient_hn
where timestampdiff(year,p.birthdate,now())between 60 and 200 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))> 60,'Yes','No') outcome,'cid',
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(timestampdiff(year,p.birthdate,now())between 60 and 200 and
ov.vstdate BETWEEN "2014-10-01" and "2015-09-30" and ov.icd10 = 'Z133',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 ovstdiag ov ON ov.hn = p.patient_hn
where timestampdiff(year,p.birthdate,now())between 60 and 200 and p.house_regist_type_id IN (1,3) and v.village_moo <> 0 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', p.person_id,CONCAT(p.pname,p.fname," ",p.lname) AS p_name,
h.address as h_on,v.village_moo as v_on, Year(Now())-Year(p.birthdate) as age,
if (count(DISTINCT case when p.cid and ov.icd10 ='Z133'
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 ovstdiag ov ON ov.vn = o.vn and ov.vstdate between '2014-10-01' and '2015-09-30'
left JOIN icd101 ic on ic.`code`= ov.icd10
WHERE TIMESTAMPDIFF(year,p.birthdate,now())between 60 and 200
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.cid