SELECT village.villname,right(house.villcode,2)as moo,COUNT(DISTINCT person.pid)as '60up'
,COUNT(DISTINCT(CASE WHEN person.candobedhomesocial IS NOT NULL THEN person.pid ELSE NULL END )) AS 'CheckADL'
,ROUND((COUNT(DISTINCT(CASE WHEN person.candobedhomesocial IS NOT NULL THEN person.pid ELSE NULL END ))*100)/COUNT(DISTINCT person.pid),2)as '%'
,COUNT(DISTINCT personunable.pid ) AS Disable
,COUNT(DISTINCT (case when visitdiag.diagcode in('Z01.0','Z01.01','Z13.5')then person.pid else null end )) AS ScreenEye
,COUNT(DISTINCT (case when visitdiag.diagcode = 'Z11.1'then person.pid else null end )) AS ScreenTb
,COUNT(DISTINCT (case when visitdiag.diagcode = 'Z13.3'then person.pid else null end )) AS ScreenDpress
,COUNT(DISTINCT (case when visitscreenspecialdisease.codescreen ='c22'then person.pid else null end )) AS ScreenKnee
,COUNT(DISTINCT (case when visitscreenspecialdisease.codescreen ='c03'then person.pid else null end )) AS ScreenBrain
from person
INNER JOIN house on person.pcucodeperson=house.pcucode and person.hcode=house.hcode
INNER JOIN village ON house.pcucode = village.pcucode AND house.villcode = village.villcode
LEFT JOIN personunable on person.pcucodeperson=personunable.pcucodeperson and person.pid=personunable.pid
LEFT JOIN (SELECT visit.pcucode,visit.pid,visit.visitno from visit where visitdate between '20131001'and '20140930')as vs on person.pid = vs.pid
LEFT JOIN visitdiag ON vs.pcucode = visitdiag.pcucode AND vs.visitno = visitdiag.visitno
left JOIN visitscreenspecialdisease ON vs.pcucode = visitscreenspecialdisease.pcucode AND vs.visitno = visitscreenspecialdisease.visitno
where right(house.villcode,2)!='00'
and CONCAT(person.pcucodeperson,person.pid) not in (SELECT concat(persondeath.pcucodeperson,persondeath.pid)from persondeath)
and person.typelive IN ('1','3')
and YEAR( FROM_DAYS( DATEDIFF(CURDATE(),person.birth ) ) ) >= 60
GROUP BY right(house.villcode,2)
ORDER BY right(house.villcode,2);