จำนวนเป้าหมายฉีดวัคซีน dTC พร้อมผลงานและค่าร้อยละ

select village.villno as villno ,village.villname as villname,
count(distinct p.pid) as pop,
count(distinct case when visitepi.vaccinecode = 'Dtc' then visitepi.pid else null end) as ผลงาน,
concat(round(count(distinct case when getAgeYearNum(p.birth,current_date)between '20' and '50' and visitepi.vaccinecode = 'Dtc' then p.pid else null end)*100 / count(distinct case when getAgeYearNum(p.birth,current_date)between '20' and '50' then p.pid else null end),2),' %') as 'ร้อยละ'
from person p
inner join house on p.hcode = house.hcode and p.pcucodeperson = house.pcucode
inner join village on house.villcode= village.villcode and villno <> '00'
left join visitepi on p.pcucodeperson = visitepi.pcucodeperson and p.pid = visitepi.pid
and dateepi between '2014-10-01'and '2015-09-30'
where getAgeYearNum(p.birth,current_date)between '20' and '50' and p.typelive in ('1','3')
group by villno,villname

Cr.ลุงหนวด หมออนามัย

รายชื่อคนที่ไม่ได้รับการคัดกรอง ncd 58 (ไม่มีกลุ่มป่วย)

select concat(ctitle.titlename,person.fname,' ',person.lname)as pname,
person.birth as pbirth,
GetAgeYearNum(person.birth,'2014-10-01')as age,
person.idcard as pcid,
person.hnomoi as hno,
person.mumoi as mu
from person inner join ctitle on person.prename = ctitle.titlecode
inner join house on person.pcucodeperson = house.pcucode and person.hcode = house.hcode
inner join village on house.pcucode = village.pcucode and village.villcode = house.villcode
where person.typelive in ('1','3') and GetAgeYearNum(person.birth,'2014-10-01') >= '35'
and SUBSTRING(house.villcode,7,2)<>'00' and person.pid not in (select ncd_person_ncd_screen.pid
FROM ncd_person_ncd_screen where ncd_person_ncd_screen.screen_date between '2014-10-01' and '2015-09-30' )
and CONCAT(person.pid,person.pcucodeperson) not in (select concat(persondeath.pid,persondeath.pcucodeperson)
from persondeath where persondeath.deaddate <= CURRENT_DATE)and concat(person.pid,person.pcucodeperson)
not in (select concat(personchronic.pid,personchronic.pcucodeperson)from personchronic where personchronic.chroniccode like'E1%' or(personchronic.chroniccode like'I1%'))
order by village.villno