โค้ดSQL

====================================

รัน code ตรวจสอบ ว่าใครที่เราบันทึกวิธีการตรวจผิด ใครที่พบว่าเป็นกลุ่มเสี่ยง แต่เราเอามาบันทึกก่อน ***

SELECT n.pid,n.screen_date,n.bstest,n.bsl FROM ncd_person_ncd_screen n
where n.screen_date between '2015-10-01' and '2016-09-30' and (bstest <> '3' or n.bsl between '100' and '125')

====================================

รายชื่อกลุ่มเป้าหมายคัดกรองพัฒนาการเด็กปฐมวัยช่วงอายุ 9,18,30,และ 42 เดือน

*** แก้ไขช่วงวันที่ในแต่ละเดือนที่รัน code โดยกำหนดเป็น 'yyyy-mm-dd' *** ตัวหนังสือสีแดง

select
person.pid as pid,
concat(ctitle.titlename,person.fname,' ',person.lname)as pname,
person.birth as pbirth,
GetAgeymd(person.birth,'2015-12-01')as age,
person.idcard as idcard,
person.hnomoi as hno,
village.villno as villno,
case when GetAgemonth(person.birth,'2015-12-01')= '9' then "/" else '' end as '9เดือน',
case when GetAgemonth(person.birth,'2015-12-01')= '18' then "/" else '' end as '18เดือน',
case when GetAgemonth(person.birth,'2015-12-01')= '30' then "/" else '' end as '30เดือน',
case when GetAgemonth(person.birth,'2015-12-01')= '42' then "/" else '' end as '42เดือน'
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 person.pid not in (select persondeath.pid from persondeath)and substring(house.villcode,7,2)<>'00'
and GetAgemonth(person.birth,'2015-12-01') in ('9','18','30','42')
order by villno

==================================

report ผลการคัดกรอง NCD 59
เงื่อนไข 
1. หากป่วยความดันผลการคัดกรองHT จะว่าง
2. หากป่วยเบาหวานผลการคัดกรอง DM จะว่าง แต่ถ้าคุณบันทึกค่าน้ำตาลในผู้ป่วยเบาหวาน ผลการคัดกรองจะออกมาเสมอ
3. ผลการคัดกรองDM จะออกก็ต่อเมื่อ เลือกวิธีการตรวจ เป็น 3. DTX(อดอาหาร)
4. กลุ่มเป้าหมาย อายุ 35 ปีขึ้นไปทุกคน ยกเว้นกลุ่มป่วยสองโรค

select
person.pid as pid,
concat(ctitle.titlename,person.fname,' ',person.lname)as pname,
date_format(person.birth,'%d/%m/%Y') as pbirth,
GetAgeYearNum(person.birth,'2015-10-01')as age,
person.idcard as idcard,
person.hnomoi as hno,
village.villno as villno,
case when (pcht.chroniccode is not null and pcdm.chroniccode is null )then '1' else '' end as 'ป่วยht',
case when (pcdm.chroniccode is not null and pcht.chroniccode is null )then '1' else '' end as 'ป่วยdm',
n.screen_date,n.height,n.weight,n.waist,n.hbp_s1,n.hbp_d1,n.bsl,n.bmi,
case when n.bsl <'100' and n.bstest = '3'then 'ปกติ' when n.bsl between '100' and '125' and n.bstest = '3'then 'เสี่ยง' when n.bsl >='126' and n.bstest = '3'then 'สงสัยว่าป่วย' else '' end as 'ผลคัดDM',
case when (n.hbp_s1 < '120' or n.hbp_d1 <'80')and person.pid not in (select person.pid from person left join personchronic pcht on person.pid = pcht.pid and person.pcucodeperson = pcht.pcucodeperson and trim(pcht.chroniccode) = 'I10'
left join personchronic pcdm on person.pid = pcdm.pid and person.pcucodeperson = pcdm.pcucodeperson and trim(pcdm.chroniccode) between 'E10' and 'E15.9'
left join personchronic pc on person.pid = pc.pid and person.pcucodeperson = pc.pcucodeperson where pcht.chroniccode is not null and pcdm.chroniccode is null) then 'ปกติ'
when ((n.hbp_s1 between '120' and '139') or (n.hbp_d1 between '80' and '89')) and person.pid not in (select person.pid from person left join personchronic pcht on person.pid = pcht.pid and person.pcucodeperson = pcht.pcucodeperson and trim(pcht.chroniccode) = 'I10'
left join personchronic pcdm on person.pid = pcdm.pid and person.pcucodeperson = pcdm.pcucodeperson and trim(pcdm.chroniccode) between 'E10' and 'E15.9'
left join personchronic pc on person.pid = pc.pid and person.pcucodeperson = pc.pcucodeperson where pcht.chroniccode is not null and pcdm.chroniccode is null)then 'เสี่ยง'
when ((n.hbp_s1 between '140' and '179') or (n.hbp_d1 between '90' and '109'))and person.pid not in (select person.pid from person left join personchronic pcht on person.pid = pcht.pid and person.pcucodeperson = pcht.pcucodeperson and trim(pcht.chroniccode) = 'I10'
left join personchronic pcdm on person.pid = pcdm.pid and person.pcucodeperson = pcdm.pcucodeperson and trim(pcdm.chroniccode) between 'E10' and 'E15.9'
left join personchronic pc on person.pid = pc.pid and person.pcucodeperson = pc.pcucodeperson where pcht.chroniccode is not null and pcdm.chroniccode is null) then 'สงสัยว่าป่วย'
when (n.hbp_s1 >= '180' or n.hbp_d1 >='110')and person.pid not in (select person.pid from person left join personchronic pcht on person.pid = pcht.pid and person.pcucodeperson = pcht.pcucodeperson and trim(pcht.chroniccode) = 'I10'
left join personchronic pcdm on person.pid = pcdm.pid and person.pcucodeperson = pcdm.pcucodeperson and trim(pcdm.chroniccode) between 'E10' and 'E15.9'
left join personchronic pc on person.pid = pc.pid and person.pcucodeperson = pc.pcucodeperson where pcht.chroniccode is not null and pcdm.chroniccode is null) then 'ฉุกเฉิน' else '' end as 'ผลคัดHT'
from person inner join ctitle on person.prename = ctitle.titlecode
left join personchronic pcht on person.pid = pcht.pid and person.pcucodeperson = pcht.pcucodeperson and trim(pcht.chroniccode) = 'I10'
left join personchronic pcdm on person.pid = pcdm.pid and person.pcucodeperson = pcdm.pcucodeperson and trim(pcdm.chroniccode) between 'E10' and 'E15.9'
left join personchronic pc on person.pid = pc.pid and person.pcucodeperson = pc.pcucodeperson
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
inner join ncd_person_ncd_screen n on person.pid = n.pid
where person.typelive in ('1','3') and person.pid not in (select persondeath.pid from persondeath)
and GetAgeYearNum(person.birth,'2015-10-01')>= '35' and substring(house.villcode,7,2)<>'00'
and person.pid not in (select person.pid from person left join personchronic pcht on person.pid = pcht.pid and person.pcucodeperson = pcht.pcucodeperson and trim(pcht.chroniccode) = 'I10'
left join personchronic pcdm on person.pid = pcdm.pid and person.pcucodeperson = pcdm.pcucodeperson and trim(pcdm.chroniccode) between 'E10' and 'E15.9'
left join personchronic pc on person.pid = pc.pid and person.pcucodeperson = pc.pcucodeperson where pcdm.chroniccode is not null and pcht.chroniccode is not null)
and n.screen_date between '2015-10-01' and '2016-09-30'
group by person.pid
order by villno, person.pid

====================================

ตรวจสอบการให้ ICD10 ที่มี dxtype = 01 มากกว่า 1 รหัส

SELECT visit.pid,v.visitno,COUNT(v.visitno) AS 'จำนวนซ้ำ',group_concat(v.diagcode) as 'icdที่ให้รหัส 01'
FROM visitdiag v inner join visit on v.visitno = visit.visitno
where v.dxtype = '01' and visit.visitdate between '2015-10-01' and '2016-09-30'
GROUP BY v.visitno
HAVING (COUNT(v.visitno) > 1)

====================================

มาดูว่าใส่วิธีการตรวจเลือดผิดหรือไม่ และบันทึกคนที่มีค่า dtx 100-125 หรือไม่

SELECT * FROM ncd_person_ncd_screen n

where n.screen_date between '2015-10-01' and '2016-09-30' and (bstest <> '3' or n.bsl between '100' and '125')

================================

รายชื่อเป้าหมาย ncdscreen ปีงบ 2559 
เงื่อนไข
1. อายุ 35 ปีขึ้นไป ณวันที่ 1 ตุลาคม 2558
2. เฉพาะ typearea 1+3
3. ระบุโรคประจำตัวให้ จะได้เลือกคัดกรองได้ถูกประเภท ตัดป่วยสองโรค

================================

select
person.pid as pid,
concat(ctitle.titlename,person.fname,' ',person.lname)as pname,
date_format(person.birth,'%d/%m/%Y') as pbirth,
GetAgeYearNum(person.birth,'2015-10-01')as age,
person.idcard as idcard,
person.hnomoi as hno,
village.villno as villno,
case when (pcht.chroniccode is not null and pcdm.chroniccode is null )then '1' else '' end as 'ป่วยht',
case when (pcdm.chroniccode is not null and pcht.chroniccode is null )then '1' else '' end as 'ป่วยdm'
from person inner join ctitle on person.prename = ctitle.titlecode
left join personchronic pcht on person.pid = pcht.pid and person.pcucodeperson = pcht.pcucodeperson and trim(pcht.chroniccode) = 'I10'
left join personchronic pcdm on person.pid = pcdm.pid and person.pcucodeperson = pcdm.pcucodeperson and trim(pcdm.chroniccode) between 'E10' and 'E15.9'
left join personchronic pc on person.pid = pc.pid and person.pcucodeperson = pc.pcucodeperson
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 person.pid not in (select persondeath.pid from persondeath)
and GetAgeYearNum(person.birth,'2015-10-01')>= '35' and substring(house.villcode,7,2)<>'00'
and person.pid not in (select person.pid from person left join personchronic pcht on person.pid = pcht.pid and person.pcucodeperson = pcht.pcucodeperson and trim(pcht.chroniccode) = 'I10'
left join personchronic pcdm on person.pid = pcdm.pid and person.pcucodeperson = pcdm.pcucodeperson and trim(pcdm.chroniccode) between 'E10' and 'E15.9'
left join personchronic pc on person.pid = pc.pid and person.pcucodeperson = pc.pcucodeperson where pcdm.chroniccode is not null and pcht.chroniccode is not null)
group by person.pid
order by villno, person.pid

ตรวจสอบให้รหัสวัคซีน MMR 2 ขวบครึ่งผิด
================================
SELECT person.pid,
concat(ctitle.titlename,person.fname,' ',person.lname)as pname,
person.birth as birth,
getageymd(person.birth,CURDATE()) as age,
person.hnomoi,person.mumoi,
max(CASE WHEN person.typelive in ('1','3') AND person.birth between '2011-10-01' and '2012-07-15' and visitepi.vaccinecode = 'MMR'THEN visitepi.dateepi ELSE '' END) AS 'MMR',
max(CASE WHEN person.typelive in ('1','3') AND person.birth between '2011-10-01' and '2012-07-15' and visitepi.vaccinecode = 'MMR2'THEN visitepi.dateepi ELSE '' END) AS 'MMR2'
FROM person INNER JOIN visitepi ON person.pcucodeperson = visitepi.pcucodeperson AND person.pid = visitepi.pid
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.dischargetype is null or person.dischargetype = 9) and SUBSTRING(house.villcode,7,2) !='00'
and person.typelive in ('1','3') AND person.birth between '2011-10-01' and '2012-07-15'
and visitepi.dateepi between '2014-09-01' and '2015-09-30'
group by person.pid
order by house.villcode

ข้อมูลการใช้ยาของ รพ.สต.

===========================

select count(distinct visit.visitno)as 'จำนวนคนรับยา',
cdrug.drugname, sum(visitdrug.unit) 'total',
sum(visitdrug.unit *visitdrug.costprice) as 'รวมบาท'
from visitdrug inner join visit on visitdrug.visitno = visit.visitno
inner join cdrug on visitdrug.drugcode = cdrug.drugcode
where visit.visitdate between '2013-10-01' and '2014-09-30' and cdrug.drugtype <> '02'
group by cdrug.drugname order by total desc

รายชื่อกลุ่มเป้าหมายไม่ได้รับการคัดกรองเต้านม

======================================

select p.pid,
concat(ctitle.titlename,p.fname,' ',p.lname)as pname,
p.birth as birth,
GetAgeYearNum(p.birth,'2014-10-01') as 'อายุ',
p.idcard as idcard,
p.hnomoi,
SUBSTRING(house.villcode,7,2) as moo
from
person p
inner join ctitle on p.prename = ctitle.titlecode
inner join house on p.pcucodeperson = house.pcucode and p.hcode = house.hcode
where p.sex = '2' and CONCAT(p.pid,p.pcucodeperson) NOT IN (select CONCAT(persondeath.pid,persondeath.pcucodeperson)from persondeath)
and p.pid NOT IN (select visit.pid from visit inner join visitdiag on visit.visitno = visitdiag.visitno where visit.visitdate between '2014-10-01' and '2015-09-30' and visitdiag.diagcode = 'Z12.3')
and SUBSTRING(house.villcode,7,2) <> '00' and p.typelive in ('1','3') and GetAgeYearNum(p.birth,'2014-10-01') between '30' and '70'
group by p.pid
order by house.villcode

code ดึงจำนวนผู้สูงอายุ ผู้พิการ ผู้ป่วย NCD แยกตามสภาพปัญหา รายหมู่
=================================
select *from(select village.villname,
sum(case when concat(person.pcucodeperson,person.pid)in (select concat(personunable.pcucodeperson,personunable.pid )
from personunable ) then '1'else '' end) as 'พิการ',
sum(case when concat(person.pcucodeperson,person.pid)in (select concat(personunable.pcucodeperson,personunable.pid )
from personunable )and person.candobedhomesocial = '1' then '1' else '' end) as 'พิการกลุ่ม1',
sum(case when concat(person.pcucodeperson,person.pid)in (select concat(personunable.pcucodeperson,personunable.pid )
from personunable )and person.candobedhomesocial = '2' then '1' else '' end) as 'พิการกลุ่ม2',
sum(case when concat(person.pcucodeperson,person.pid)in (select concat(personunable.pcucodeperson,personunable.pid )
from personunable )and person.candobedhomesocial = '3' then '1' else '' end) as 'พิการกลุ่ม3',
sum(case when concat(person.pcucodeperson,person.pid) in (select concat(personchronic.pcucodeperson,personchronic.pid )
from personchronic where personchronic.typedischart='03' ) then '1' else '' end) as 'NCD',
sum(case when concat(person.pcucodeperson,person.pid) in (select concat(personchronic.pcucodeperson,personchronic.pid )
from personchronic where personchronic.typedischart='03' )and person.candobedhomesocial = '1' then '1' else '' end) as 'NCDกลุ่ม1',
sum(case when concat(person.pcucodeperson,person.pid) in (select concat(personchronic.pcucodeperson,personchronic.pid )
from personchronic where personchronic.typedischart='03' )and person.candobedhomesocial = '2' then '1' else '' end) as 'NCDกลุ่ม2',
sum(case when concat(person.pcucodeperson,person.pid) in (select concat(personchronic.pcucodeperson,personchronic.pid )
from personchronic where personchronic.typedischart='03' )and person.candobedhomesocial = '3' then '1' else '' end) as 'NCDกลุ่ม3',
sum(case when getAgeYearNum(person.birth,'2014-10-01')>= 60 then '1'else '' end) as 'Old',
sum(case when getAgeYearNum(person.birth,'2014-10-01')>= 60 and person.candobedhomesocial = '1' then '1' else '' end) as 'Oldกลุ่ม1',
sum(case when getAgeYearNum(person.birth,'2014-10-01')>= 60 and person.candobedhomesocial = '2' then '1' else '' end) as 'Oldกลุ่ม2',
sum(case when getAgeYearNum(person.birth,'2014-10-01')>= 60 and person.candobedhomesocial = '3' then '1' else '' end) as 'Oldกลุ่ม3'
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
left join user on house.usernamedoc = user.username
where person.typelive IN ('1','3')and person.pid not in (select persondeath.pid from persondeath)and substring(house.villcode,7,2)<>'00'
group by village.villcode
)as orderp

code ดึงรายชื่อผู้สูงอายุ ผู้พิการ ผู้ป่วย NCD ตามสภาพปัญหา 
===========================================
select *from(select person.idcard,
concat(ctitle.titlename,person.fname,' ',person.lname)as 'ชื่อ-สกุล',
GetAgeYearNum(person.birth,current_date)as 'อายุ',
house.hno as 'บ้านเลขที่',
right(house.villcode,2) as 'หมู่ที่',
case when concat(person.pcucodeperson,person.pid)in (select concat(personunable.pcucodeperson,personunable.pid )
from personunable ) then '/'else '' end as 'Disabl',
case when concat(person.pcucodeperson,person.pid) in (select concat(personchronic.pcucodeperson,personchronic.pid )
from personchronic where personchronic.typedischart='03' ) then '/' else '' end as 'NCD',
case when getAgeYearNum(person.birth,'2014-10-01')>= 60 then '/'else '' end as 'Old',
case when person.candobedhomesocial = '1' then '/' else '' end as 'กลุ่ม1',
case when person.candobedhomesocial = '2' then '/' else '' end as 'กลุ่ม2',
case when person.candobedhomesocial = '3' then '/' else '' end as 'กลุ่ม3'
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 person.pid not in (select persondeath.pid from persondeath)and substring(house.villcode,7,2)<>'00'
group by person.pid
order by house.villcode) as orderp
where orderp.Disabl ='/' or orderp.NCD='/' or orderp.old='/'

สรุปจำนวนการคัดกรองแยกกลุ่มอายุ ให้เปลี่ยนวันเริ่มต้นและสิ้นสุดการตัดรายงาน ตัวหนังสือสีแดง

===================================================

SELECT

CASE WHEN  hbp_s1  <'120' and hbp_d1 <'80' and bsl < '100' THEN 'ปกติ'

WHEN  hbp_s1  <'120' and hbp_d1 <'80' and bsl >= '100' THEN 'เสี่ยงDM'

WHEN  (hbp_s1 >= '120' or hbp_d1 >= '80')  and  bsl < '100' THEN 'เสี่ยงHT'

WHEN  (hbp_s1 >= '120' or hbp_d1 >= '80') and  bsl >= '100' THEN 'เสี่ยงDM&HT' ELSE '' END AS levelrisk,

CASE WHEN  age_year between '35' and '59' THEN '35-59ปี'

WHEN  age_year>='60' THEN '>60ปี' ELSE '' END AS gage,

count(distinct ncd_person_ncd_screen.pid) as ผลงาน

FROM

ncd_person_ncd_screen INNER JOIN person ON ncd_person_ncd_screen.pid = person.pid

INNER JOIN ncd_person  ON ncd_person_ncd_screen.pcucode = ncd_person.pcucode AND ncd_person_ncd_screen.pid = ncd_person.pid

WHERE age_year >= '35' and ncd_person_ncd_screen.screen_date between '2014-10-01' and '2015-01-31' and concat(ncd_person_ncd_screen.pid,ncd_person_ncd_screen.pcucode) not in

(select concat(ncd_person_ncd_hist.pid, ncd_person_ncd_hist.pcucode)from  ncd_person_ncd_hist) and SUBSTRING(ncd_person.village,7,2) <>'00'

group by levelrisk,gage

รายชื่อคัดกรองพร้อมผลการคัดกรอง ให้เปลี่ยนวันเริ่มต้นและสิ้นสุดการตัดรายงาน ตัวหนังสือสีแดง

=========================================================================

select

person.pid as pid,

concat(ctitle.titlename,person.fname,'    ',person.lname)as pname,

age_year,

person.idcard as pcid,

person.sex as sex,

person.hnomoi as hno,

person.mumoi as mu,

hbp_s1,hbp_d1,bsl,

DATE_FORMAT(screen_date,'%Y-%m-%d') as screen_date,

max(case when hbp_s1  <'120' and hbp_d1 <'80' and bsl < '100' then "/" else '' end) as "ปกติ",

max(case when hbp_s1  <'120' and hbp_d1 <'80' and bsl >= '100' then "/" else '' end) as "เสี่ยงDM",

max(case when (hbp_s1 >= '120' or hbp_d1 >= '80')  and  bsl < '100' then "/"  else '' end) as "เสี่ยงHT",

max(case when (hbp_s1 >= '120' or hbp_d1 >= '80') and  bsl >= '100' then "/"  else '' end) as "เสี่ยงDM&HT"

from     ncd_person_ncd_screen inner join person on ncd_person_ncd_screen.pid = person.pid

     inner join ctitle on person.prename = ctitle.titlecode

     inner join ncd_person  on ncd_person_ncd_screen.pcucode = ncd_person.pcucode and ncd_person_ncd_screen.pid = ncd_person.pid

where

     age_year >= '35' and ncd_person_ncd_screen.screen_date between '2014-10-01' and '2015-02-02' and concat(ncd_person_ncd_screen.pid,ncd_person_ncd_screen.pcucode) not in

     (select concat(ncd_person_ncd_hist.pid, ncd_person_ncd_hist.pcucode)

      from  ncd_person_ncd_hist) and SUBSTRING(ncd_person.village,7,2) <>'00'

group by person.pid

order by ncd_person.village

รายชื่อเด็ก 0-60 เดือน ชั่งน้ำหนัก

SELECT person.pid ,ctitle.titlename ,person.fname ,person.lname ,person.hnomoi ,person.mumoi ,getAgeYearNum(person.birth,CURDATE()) AS นับอายุเป็นปี ,getagemonth(person.birth,CURDATE()) AS นับอายุเป็นเดือน FROM person LEFT JOIN persondeath ON persondeath.pcucodeperson = person.pcucodeperson AND persondeath.pid = person.pid INNER JOIN house ON person.pcucodeperson = house.pcucode AND person.hcode = house.hcode INNER JOIN ctitle ON ctitle.titlecode = person.prename WHERE persondeath.pid IS NULL AND getagemonth(person.birth,CURDATE()) <= 60 AND person.typelive IN ('0','1','3') ORDER BY house.villcode,person.hnomoi*1;

ผลงานการคัดกรองปี 58 แยกรายหมู่

select village.villno as หมู่,village.villname as ชื่อหมู่บ้าน, count(distinct person.pid) as เป้า , count(distinct sc.pid) as ผลงาน, concat(round(count(distinct sc.pid)*100/count(distinct person.pid),2),' %') as เปอรเซ็น ,count(distinct case when getAgeYearNum(birth,'20141001') between 35 and 59 then person.pid else null end) as "เป้า35-59" ,count(distinct case when getAgeYearNum(birth,'20141001') between 35 and 59 and sc.pid is not null then person.pid else null end) as ผลงาน ,concat(round(count(distinct case when getAgeYearNum(birth,'20141001') between 35 and 59 and sc.pid is not null then person.pid else null end)*100 / count(distinct case when getAgeYearNum(birth,'20141001') between 35 and 59 then person.pid else null end),2),' %') as "%35-59" ,count(distinct case when getAgeYearNum(birth,'20141001') >= 60 then person.pid else null end) as "เป้า>= 60" ,count(distinct case when getAgeYearNum(birth,'20141001') >= 60 and sc.pid is not null then person.pid else null end) as ผลงาน ,concat(round(count(distinct case when getAgeYearNum(birth,'20141001') >= 60 and sc.pid is not null then person.pid else null end)*100 / count(distinct case when getAgeYearNum(birth,'20141001') >= 60 then person.pid else null end),2),' %') as "%>= 60" from person left join ncdpersonscreenall sc on person.pid = sc.pid and person.pcucodeperson = sc.pcucode and sc.screen_date between '20141001' and '20150930' inner join house on person.hcode = house.hcode and person.pcucodeperson = house.pcucode inner join village on house.villcode= village.villcode and villno <> '00' where getAgeYearNum(birth,'20141001')>= '35' and person.typelive in('0','1','3') and person.dischargetype <> 1 and person.pid not in (select pid from personchronic where (personchronic.chroniccode='I10' or personchronic.chroniccode between 'E10' and 'E15')) group by villno,villname union select '' as หมู่,'รวม' as ชื่อหมู่บ้าน,count(distinct person.pid) as เป้า ,count(distinct sc.pid) as ผลงาน,concat(round(count(distinct sc.pid)*100/count(distinct person.pid),2),' %') as เปอรเซ็น ,count(distinct case when getAgeYearNum(birth,'20141001') between 35 and 59 then person.pid else null end) as "เป้า35-59" ,count(distinct case when getAgeYearNum(birth,'20141001') between 35 and 59 and sc.pid is not null then person.pid else null end) as ผลงาน ,concat(round(count(distinct case when getAgeYearNum(birth,'20141001') between 35 and 59 and sc.pid is not null then person.pid else null end)*100 / count(distinct case when getAgeYearNum(birth,'20141001') between 35 and 59 then person.pid else null end),2),' %') as "%35-59" ,count(distinct case when getAgeYearNum(birth,'20141001') >= 60 then person.pid else null end) as "เป้า>= 60" ,count(distinct case when getAgeYearNum(birth,'20141001') >= 60 and sc.pid is not null then person.pid else null end) as ผลงาน ,concat(round(count(distinct case when getAgeYearNum(birth,'20141001') >= 60 and sc.pid is not null then person.pid else null end)*100 / count(distinct case when getAgeYearNum(birth,'20141001') >= 60 then person.pid else null end),2),' %') as "%>= 60" from person left join ncdpersonscreenall sc on person.pid = sc.pid and person.pcucodeperson = sc.pcucode and sc.screen_date between '20141001' and '20150930' inner join house on person.hcode = house.hcode and person.pcucodeperson = house.pcucode inner join village on house.villcode= village.villcode and villno <> '00' where getAgeYearNum(birth,'20141001')>= 35 and person.typelive in('1','3') and person.dischargetype <> 1 and person.pid not in (select pid from personchronic where (personchronic.chroniccode='I10' or personchronic.chroniccode between 'E10' and 'E15')) group by หมู่, ชื่อหมู่บ้าน;

กลุ่มเป้าหมายการคัดกรองต้อกระจก มากกว่า 60 ปี

SELECT
person.pid AS pid,
concat(ctitle.titlename,person.fname,' ',person.lname)as pname,
person.birth AS pbirth,
GetAgeYearNum(person.birth,CURRENT_DATE)as age,
person.idcard AS pcid,
person.sex AS sex,
person.hnomoi AS hno,
village.villno AS village_villno,
village.villname AS village_villname
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 person.pid not in (SELECT persondeath.pid FROM persondeath)
and GetAgeYearNum(person.birth,'2014-10-01')>= '60'
and SUBSTRING(house.villcode,7,2)<>'00'
order BY village.villno,person.hnomoi*1;

โค้ดรายชื่อผู้ป่วยความดัน เบาหวานที่มารับบริการพร้อมค่าความดันและน้ำตาลในเลือด

select
concat(ctitle.titlename,person.fname,' ',person.lname)as 'ชื่อ-สกุล',
GetAgeYearNum(person.birth,CURRENT_DATE)as 'อายุ',
concat(person.hnomoi,' ','หมู่',person.mumoi) as 'ที่อยู่',
case when (pcht.chroniccode is not null and pcdm.chroniccode is null )then '1' else '' end as ht
,case when (pcdm.chroniccode is not null and pcht.chroniccode is null ) then '1' else '' end as dm
,case when pcdm.chroniccode is not null and pcht.chroniccode is not null then '1' else '' end as htdm,
max(case when month(visit.visitdate)=10 then visit.pressure else '' end) as 'ต.ค.',
max(case when month(visit.visitdate)=11 then visit.pressure else '' end) as 'พ.ย.',
max(case when month(visit.visitdate)=12 then visit.pressure else '' end) as 'ธ.ค.',
max(case when month(visit.visitdate)=1 then visit.pressure else '' end) as 'ม.ค.',
max(case when month(visit.visitdate)=2 then visit.pressure else '' end) as 'ก.พ.',
max(case when month(visit.visitdate)=3 then visit.pressure else '' end) as 'มี.ค.',
max(case when month(visit.visitdate)=4 then visit.pressure else '' end) as 'เม.ย.',
max(case when month(visit.visitdate)=5 then visit.pressure else '' end) as 'พ.ค.',
max(case when month(visit.visitdate)=6 then visit.pressure else '' end) as 'มิ.ย.',
max(case when month(visit.visitdate)=7 then visit.pressure else '' end) as 'ก.ค.',
max(case when month(visit.visitdate)=8 then visit.pressure else '' end) as 'ส.ค.',
max(case when month(visit.visitdate)=9 then visit.pressure else '' end) as 'ก.ย.'
from person
inner join ctitle on person.prename = ctitle.titlecode
inner join house on person.hcode = house.hcode and person.pcucodeperson = house.pcucode
left join visit ON person.pcucodeperson = visit.pcucodeperson and person.pid = visit.pid
left join visitdiag on visit.visitno = visitdiag.visitno
inner join village on house.villcode = village.villcode and house.pcucode = village.pcucode
LEFT JOIN personchronic pcht on person.pid = pcht.pid and person.pcucodeperson = pcht.pcucodeperson and trim(pcht.chroniccode) = 'I10'
LEFT JOIN personchronic pcdm on person.pid = pcdm.pid and person.pcucodeperson = pcdm.pcucodeperson and trim(pcdm.chroniccode) between 'E10' and 'E15.9'
LEFT JOIN personchronic pc on person.pid = pc.pid and person.pcucodeperson = pc.pcucodeperson
WHERE visit.visitdate between '2014-10-01' and '2015-09-30'
and SUBSTRING(house.villcode,7,2)<> '00' and (visitdiag.diagcode between 'E10' and 'E15.9' or (visitdiag.diagcode ='I10'))
and pc.cup = pc.pcucodeperson
GROUP BY pc.pid
order BY village.villno

โค้ด ประชากรจำแนกตามกลุ่มวัย

select village.villno as villno ,village.villname as villname
,count(person.pid) as 'total'
,sum(case when GetAgeYearNum(person.birth,'2014-10-01') < 5 then 1 else '' end) as '0-5ปี'
,sum(case when GetAgeYearNum(person.birth,'2014-10-01') between 5 and 14 then 1 else '' end) as '5-14ปี'
,sum(case when GetAgeYearNum(person.birth,'2014-10-01') between 15 and 21 then 1 else '' end) as '15-21ปี'
,sum(case when GetAgeYearNum(person.birth,'2014-10-01') between 15 and 59 then 1 else '' end) as '15-59ปี'
,sum(case when GetAgeYearNum(person.birth,'2014-10-01') >= 60 then 1 else '' end) as '60ปี'
,sum(case when concat(person.pcucodeperson,person.pid)in(select concat(personunable.pcucodeperson,personunable.pid )from personunable ) then 1 else '' end) as 'พิการ'
from person inner join house on person.pcucodeperson = house.pcucode and person.hcode = house.hcode
inner join village on house.villcode= village.villcode
where concat(person.pid,person.pcucodeperson)not in (select concat(persondeath.pid,persondeath.pcucodeperson)
from persondeath where deaddate<=curdate() or deaddate is not null)
and SUBSTRING(house.villcode,7,2)<>'00' and person.typelive in ('1','3')
group by villno,villname

โค้ดรายชื่อประชากรอายุ 20-50 ปี เป้าหมายการฉีดวัคซีน dt (เกิดตั้งแต่ 1 ม.ค.08-31 ธ.ค.38)

SELECT person.pid AS pid, concat(ctitle.titlename,person.fname,' ',person.lname)as pname, person.birth AS pbirth, GetAgeYearNum(person.birth,CURRENT_DATE)as age, person.idcard AS pcid, person.sex AS sex, person.hnomoi AS hno, person.mumoi AS mu, village.villno AS village_villno, village.villname AS village_villname 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 person.pid not in (SELECT persondeath.pid FROM persondeath) and person.birth between '1965-01-01' and '1995-12-31' and SUBSTRING(house.villcode,7,2)<>'00' AND nation='99' order BY village.villno,person.hnomoi*1;

โค้ดรายชื่อผู้ป่วยNCD ระบุสถานที่รักษา

select p.pid ,concat(ctitle.titlename ,' ',p.fname,' ',p.lname) AS 'name' ,YEAR(FROM_DAYS(DATEDIFF(CURDATE(),p.birth))) as 'age' ,p.idcard as CID ,house.hno as 'address' ,village.villno AS 'moo' ,GROUP_CONCAT(pc.chroniccode) as chroniccode ,case when (pcht.chroniccode is not null and pcdm.chroniccode is null )then '1' else '' end as ht ,case when (pcdm.chroniccode is not null and pcht.chroniccode is null ) then '1' else '' end as dm ,case when pcdm.chroniccode is not null and pcht.chroniccode is not null then '1' else '' end as htdm ,DATE_FORMAT(pcdm.datefirstdiag,'%Y-%m-%d') as 'dx_first_DM' ,DATE_FORMAT(pcht.datefirstdiag,'%Y-%m-%d') as 'dx_first_HT' ,pc.cup as 'hospital' ,pc.hospfirstdiag as 'first_dx_hosp' 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 house.pcucode = village.pcucode LEFT JOIN ctitle ON p.prename = ctitle.titlecode LEFT JOIN personchronic pcht on p.pid = pcht.pid and p.pcucodeperson = pcht.pcucodeperson and trim(pcht.chroniccode) = 'I10' LEFT JOIN personchronic pcdm on p.pid = pcdm.pid and p.pcucodeperson = pcdm.pcucodeperson and trim(pcdm.chroniccode) between 'E10' and 'E15.9' LEFT JOIN personchronic pc on p.pid = pc.pid and p.pcucodeperson = pc.pcucodeperson WHERE (pcdm.chroniccode between 'E10' and 'E15.9' or pcht.chroniccode ='I10') and right(village.villcode,2)!= '00' and p.typelive in (1,3) and pc.typedischart = '03' AND concat(p.pid,p.pcucodeperson)NOT IN (select concat(persondeath.pid,persondeath.pcucodeperson) from persondeath where deaddate <= CURDATE() or deaddate is null) GROUP BY p.pid,ht,dm ORDER BY village.villcode*1

โค้ดเป้าหมายงานทันตกรรม

SELECT SUM(CASE WHEN GetAgeYearNum(person.birth,'2014-10-01') BETWEEN 0 AND 2 THEN 1 ELSE null END) AS m02, SUM(CASE WHEN GetAgeYearNum(person.birth,'2014-10-01') BETWEEN 3 AND 5 THEN 1 ELSE null END) AS m35, SUM(CASE WHEN GetAgeYearNum(person.birth,'2014-10-01') BETWEEN 6 AND 14 THEN 1 ELSE null END) AS m614, SUM(CASE WHEN GetAgeYearNum(person.birth,'2014-10-01') >= 60 THEN 1 ELSE null END) AS m60 FROM person Left Outer Join house ON person.hcode = house.hcode AND house.pcucode = person.pcucodeperson Left Outer Join village ON house.villcode = village.villcode AND village.pcucode = house.pcucode Left Outer Join cright ON person.rightcode = cright.rightcode WHERE SUBSTRING(house.villcode,7,2)<>'00' and person.typelive in ('1','3') and CONCAT(person.pid,person.pcucodeperson) NOT IN (select CONCAT(persondeath.pid,persondeath.pcucodeperson)from persondeath)

ตรวจสอบคนที่คัดกรอง NCD ซ้ำ

select count(n.pid),pid
from ncd_person_ncd_screen n
where screen_date between '2014-10-01' and '2015-09-30'
group by n.pid
having count(n.pid) > 1

เป้าหมายการรณรงค์ฉีดวัคซีน Dt 58 

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

เป้าหมายการคัดกรองปี 58 ตัดกลุ่มป่วย 

select
(select
count(p.pid)
from
person p
inner join ctitle on p.prename = ctitle.titlecode
inner join house on p.pcucodeperson = house.pcucode and p.hcode = house.hcode
where CONCAT(p.pid,p.pcucodeperson) NOT IN (select CONCAT(persondeath.pid,persondeath.pcucodeperson)from persondeath)
and SUBSTRING(house.villcode,7,2) <> '00' and p.typelive in ('1','3') and GetAgeYearNum(p.birth,'2014-10-01')>='35'
and p.pid not in (select pid from personchronic where (personchronic.chroniccode='I10' or personchronic.chroniccode between 'E10' and 'E15.9'))) as 'ตัดกลุ่มป่วย',
(select
count(p.pid)
from
person p
inner join ctitle on p.prename = ctitle.titlecode
inner join house on p.pcucodeperson = house.pcucode and p.hcode = house.hcode
where CONCAT(p.pid,p.pcucodeperson) NOT IN (select CONCAT(persondeath.pid,persondeath.pcucodeperson)from persondeath)
and SUBSTRING(house.villcode,7,2) <> '00' and p.typelive in ('1','3') and GetAgeYearNum(p.birth,'2014-10-01')>='35') as 'ไม่ตัดกลุ่มป่วย'

รายชื่อกลุ่มป่วยแยกรายหมู่ (ตัวหนังสือสีแดงให้เปลี่ยนเป็น วดป.ที่ต้องการเรียกรายงาน)

select

concat(ctitle.titlename,ps.fname,' ',ps.lname)as pname,

ps.birth as birth,

ps.sex as sex,

ps.idcard as idcard,

ps.hnomoi,

SUBSTRING(house.villcode,7,2) as moo,

max(v.visitdate) as vdate,

v.pressure as vpressure,

substr(v.pressure,1,instr(v.pressure,"/")-1) as bps,

substr(v.pressure,instr(v.pressure,"/")+1,length(v.pressure)-instr(v.pressure,"/")) as bpd,

max(case when pc.chroniccode = 'I10' then 'HT' else ' ' end) as HT,

max(case when pc.chroniccode between 'E10' and 'E15.9' then 'DM' else ' ' end) as DM,

if(substr(v.pressure,1,instr(v.pressure,"/")-1) <140 or substr(v.pressure,instr(v.pressure,"/")+1,length(v.pressure)-instr(v.pressure,"/")) <90,'Green',

if(substr(v.pressure,1,instr(v.pressure,"/")-1) <160 or substr(v.pressure,instr(v.pressure,"/")+1,length(v.pressure)-instr(v.pressure,"/")) <100,'Yellow',

if(substr(v.pressure,1,instr(v.pressure,"/")-1) <180 or substr(v.pressure,instr(v.pressure,"/")+1,length(v.pressure)-instr(v.pressure,"/")) <110,'Orange ','Red'))) as 'ระดับความดัน',

s.sugarnumdigit as FBS,

max(case when s.sugarnumdigit < 126 then 'Green' when s.sugarnumdigit between 126 and 154 then 'Yellow' when s.sugarnumdigit between 155 and 182 then 'Orange'when s.sugarnumdigit >= 183 then 'Red' else ' ' end) as 'ระดับเบาหวาน',

max(case when visitlabchcyhembmsse.labcode = 'CH99' then visitlabchcyhembmsse.labresultdigit else ' ' end) as 'HbA1c',

max(case when visitlabchcyhembmsse.labcode = 'CH99' and visitlabchcyhembmsse.labresultdigit < 7 then 'Yellow' when visitlabchcyhembmsse.labcode = 'CH99' and visitlabchcyhembmsse.labresultdigit < 8 then 'Orange' when visitlabchcyhembmsse.labcode = 'CH99' and visitlabchcyhembmsse.labresultdigit >= 8 then 'Red' else ' ' end) as 'ระดับHbA1c'

from

person ps inner join visit v on ps.pcucodeperson = v.pcucodeperson and ps.pid = v.pid

inner join ctitle on ps.prename = ctitle.titlecode

inner join house on ps.pcucodeperson = house.pcucode and ps.hcode = house.hcode

Left join visitlabsugarblood s on v.pcucode = s.pcucode

and v.visitno = s.visitno

inner join personchronic pc on ps.pcucodeperson = pc.pcucodeperson

and ps.`pid` = pc.pid

Left join visitlabchcyhembmsse on v.pcucode = visitlabchcyhembmsse.pcucode

and v.visitno = visitlabchcyhembmsse.visitno

where v.visitdate between '2013-10-01' and '2014-09-30'

and (pc.chroniccode between 'E10' and 'E15.9' or pc.chroniccode = 'I10') and v.pressure is not null

and CONCAT(ps.pid,ps.pcucodeperson) NOT IN (select CONCAT(persondeath.pid,persondeath.pcucodeperson)from persondeath)

and pc.cup = pc.pcucodeperson and SUBSTRING(house.villcode,7,2) <> '00'

group by ps.pid


รายงานการตายทั้งหมดในเขตรับผิดชอบ

select
concat(ctitle.titlename,p.fname,' ',p.lname)as pname,
p.birth as birth,
GetAgeYearNum(p.birth,current_date) as 'อายุ',
p.idcard as idcard,
p.hnomoi,
SUBSTRING(house.villcode,7,2) as moo,
persondeath.deadcause,cdisease.diseasenamethai,persondeath.deaddate
from
person p
inner join ctitle on p.prename = ctitle.titlecode
inner join house on p.pcucodeperson = house.pcucode and p.hcode = house.hcode
inner join persondeath on p.pcucodeperson = persondeath.pcucodeperson and p.pid = persondeath.pid
inner join cdisease on persondeath.deadcause = cdisease.diseasecode
where  SUBSTRING(house.villcode,7,2) <> '00' #and p.typelive in ('1','3')
group by p.pid
order by house.villcode


รายงาน 504 แยกปี  (ตัวหนังสือสีแดงให้เปลี่ยน วดป.ที่ต้องการเรียกรายงาน)

select cdisease504.group504code,cdisease504.group504name,
count(visitdiag.diagcode) as d,
sum(case when (visit.visitdate)between '2008-10-01' and '2009-09-30' then 1 else null end) as '2552',
sum(case when (visit.visitdate)between '2009-10-01' and '2010-09-30' then 1 else null end) as '2553',
sum(case when (visit.visitdate)between '2010-10-01' and '2011-09-30' then 1 else null end) as '2554',
sum(case when (visit.visitdate)between '2011-10-01' and '2012-09-30' then 1 else null end) as '2555',
sum(case when (visit.visitdate)between '2012-10-01' and '2013-09-30' then 1 else null end) as '2556',
sum(case when (visit.visitdate)between '2013-10-01' and '2014-09-30' then 1 else null end) as '2557'
from visitdiag
inner join cdisease on visitdiag.diagcode = cdisease.diseasecode
inner join cdisease504 on cdisease.code504 = cdisease504.group504code
inner join visit on visitdiag.visitno = visit.visitno
where visit.visitdate between '2008-10-01' and '2014-09-30'
group by cdisease504.group504name
order by cdisease504.group504code

รายชื่อคนที่ไม่ได้รับการคัดกรอง 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

ตรวจสอบรายชื่อเด็ก อายุครบ 1 ปี ที่บันทึกการฉีดวัคซีนไม่ครบ

select EPI.fname,EPI.lname,EPI.birth,EPI.age,EPI.BCG,EPI.DHB1,EPI.OPV1,EPI.DHB2,EPI.OPV2,EPI.DHB3,EPI.OPV3,EPI.MMR
,case when (EPI.DHB3 and EPI.OPV3 and EPI.MMR )!="0" then 'com'else'notcom'end as cover
,EPI.pcucodeperson#,COUNT(DISTINCT concat(EPI.pcucodeperson,EPI.pid))as total
FROM
(SELECT person.pcucodeperson,person.pid,person.birth,person.fname,person.lname,getagemonth(person.birth,CURDATE() )as age
,max(case when cdrug.files18epi='010'then visitepi.dateepi else " 0 " end)as BCG
,max(case when cdrug.files18epi in ('031','091')then visitepi.dateepi else " 0 " end)as DHB1
,max(case when cdrug.files18epi in('032','092')then visitepi.dateepi else " 0 " end)as DHB2
,max(case when cdrug.files18epi in('033','093')then visitepi.dateepi else " 0 " end)as DHB3
,max(case when cdrug.files18epi='034'then visitepi.dateepi else " 0 " end)as DTP4
,max(case when cdrug.files18epi='035'then visitepi.dateepi else " 0 " end)as DTP5
,max(case when cdrug.files18epi='081'then visitepi.dateepi else " 0 " end)as OPV1
,max(case when cdrug.files18epi='082'then visitepi.dateepi else " 0 " end)as OPV2
,max(case when cdrug.files18epi='083'then visitepi.dateepi else " 0 " end)as OPV3
,max(case when cdrug.files18epi='084'then visitepi.dateepi else " 0 " end)as OPV4
,max(case when cdrug.files18epi='085'then visitepi.dateepi else " 0 " end)as OPV5
,max(case when cdrug.files18epi='061'then visitepi.dateepi else " 0 " end)as MMR
,max(case when cdrug.files18epi='051'then visitepi.dateepi else " 0 " end)as JE1
,max(case when cdrug.files18epi='052'then visitepi.dateepi else " 0 " end)as JE2
,max(case when cdrug.files18epi='053'then visitepi.dateepi else " 0 " end)as JE3

FROM person
INNER JOIN house on person.hcode=house.hcode AND person.pcucodeperson=house.pcucodeperson
LEFT JOIN visitepi on person.pid=visitepi.pid AND person.pcucodeperson=visitepi.pcucodeperson
LEFT JOIN cdrug on visitepi.vaccinecode=cdrug.drugcode
WHERE person.typelive in ('1','3')
and getagemonth(person.birth,CURDATE() ) between 12 and 23

GROUP BY CONCAT(person.pcucodeperson,person.pid))as EPI 
WHERE EPI.MMR = 0 or EPI.DHB3= 0 or EPI.OPV3= 0
GROUP BY EPI.pid
ORDER BY EPI.birth

ตรวจสอบรายชื่อเด็ก อายุครบ 2 ปี ที่บันทึกการฉีดวัคซีนไม่ครบ

select EPI.fname,EPI.lname,EPI.birth,EPI.age,EPI.DTP4,EPI.OPV4,EPI.JE1,EPI.JE2
,case when (EPI.DTP4 and EPI.OPV4 and EPI.JE2 )!="0" then 'com'else'notcom'end as cover
#when EPI.DHB3 ="-" or EPI.OPV3 ="-" or EPI.MMR ="-"then 'notcom' else "" end as cover
,EPI.pcucodeperson#,COUNT(DISTINCT concat(EPI.pcucodeperson,EPI.pid))as total
FROM
(SELECT person.pcucodeperson,person.pid,person.birth,person.fname,person.lname,getagemonth(person.birth,CURDATE() )as age
,max(case when cdrug.files18epi='010'then visitepi.dateepi else " 0 " end)as BCG
,max(case when cdrug.files18epi in ('031','091')then visitepi.dateepi else " 0 " end)as DHB1
,max(case when cdrug.files18epi in('032','092')then visitepi.dateepi else " 0 " end)as DHB2
,max(case when cdrug.files18epi in('033','093')then visitepi.dateepi else " 0 " end)as DHB3
,max(case when cdrug.files18epi='034'then visitepi.dateepi else " 0 " end)as DTP4
,max(case when cdrug.files18epi='035'then visitepi.dateepi else " 0 " end)as DTP5
,max(case when cdrug.files18epi='081'then visitepi.dateepi else " 0 " end)as OPV1
,max(case when cdrug.files18epi='082'then visitepi.dateepi else " 0 " end)as OPV2
,max(case when cdrug.files18epi='083'then visitepi.dateepi else " 0 " end)as OPV3
,max(case when cdrug.files18epi='084'then visitepi.dateepi else " 0 " end)as OPV4
,max(case when cdrug.files18epi='085'then visitepi.dateepi else " 0 " end)as OPV5
,max(case when cdrug.files18epi='061'then visitepi.dateepi else " 0 " end)as MMR
,max(case when cdrug.files18epi='051'then visitepi.dateepi else " 0 " end)as JE1
,max(case when cdrug.files18epi='052'then visitepi.dateepi else " 0 " end)as JE2
,max(case when cdrug.files18epi='053'then visitepi.dateepi else " 0 " end)as JE3

FROM person
INNER JOIN house on person.hcode=house.hcode AND person.pcucodeperson=house.pcucodeperson
LEFT JOIN visitepi on person.pid=visitepi.pid AND person.pcucodeperson=visitepi.pcucodeperson
LEFT JOIN cdrug on visitepi.vaccinecode=cdrug.drugcode
WHERE person.typelive in ('1','3')
and getagemonth(person.birth,CURDATE() ) between 24 and 35

GROUP BY CONCAT(person.pcucodeperson,person.pid))as EPI 
WHERE EPI.DTP4 = 0 or EPI.OPV4= 0 or EPI.JE2= 0
GROUP BY EPI.pid
ORDER BY EPI.birth

ตรวจสอบรายชื่อเด็ก อายุครบ 3 ปี ที่บันทึกการฉีดวัคซีนไม่ครบ


select EPI.fname,EPI.lname,EPI.birth,EPI.age,EPI.JE3
,case when EPI.JE3 != 0 then 'com'else'notcom'end as cover
,EPI.pcucodeperson
FROM
(SELECT person.pcucodeperson,person.pid,person.birth,person.fname,person.lname,getagemonth(person.birth,CURDATE() )as age
,max(case when cdrug.files18epi='010'then visitepi.dateepi else " 0 " end)as BCG
,max(case when cdrug.files18epi in ('031','091')then visitepi.dateepi else " 0 " end)as DHB1
,max(case when cdrug.files18epi in('032','092')then visitepi.dateepi else " 0 " end)as DHB2
,max(case when cdrug.files18epi in('033','093')then visitepi.dateepi else " 0 " end)as DHB3
,max(case when cdrug.files18epi='034'then visitepi.dateepi else " 0 " end)as DTP4
,max(case when cdrug.files18epi='035'then visitepi.dateepi else " 0 " end)as DTP5
,max(case when cdrug.files18epi='081'then visitepi.dateepi else " 0 " end)as OPV1
,max(case when cdrug.files18epi='082'then visitepi.dateepi else " 0 " end)as OPV2
,max(case when cdrug.files18epi='083'then visitepi.dateepi else " 0 " end)as OPV3
,max(case when cdrug.files18epi='084'then visitepi.dateepi else " 0 " end)as OPV4
,max(case when cdrug.files18epi='085'then visitepi.dateepi else " 0 " end)as OPV5
,max(case when cdrug.files18epi='061'then visitepi.dateepi else " 0 " end)as MMR
,max(case when cdrug.files18epi='051'then visitepi.dateepi else " 0 " end)as JE1
,max(case when cdrug.files18epi='052'then visitepi.dateepi else " 0 " end)as JE2
,max(case when cdrug.files18epi='053'then visitepi.dateepi else " 0 " end)as JE3

FROM person
INNER JOIN house on person.hcode=house.hcode AND person.pcucodeperson=house.pcucodeperson
LEFT JOIN visitepi on person.pid=visitepi.pid AND person.pcucodeperson=visitepi.pcucodeperson
LEFT JOIN cdrug on visitepi.vaccinecode=cdrug.drugcode
WHERE person.typelive in ('1','3')
and getagemonth(person.birth,CURDATE() ) between 36 and 47
GROUP BY CONCAT(person.pcucodeperson,person.pid))as EPI 
WHERE EPI.JE3= 0
GROUP BY EPI.pid
ORDER BY EPI.birth


ตรวจสอบรายชื่อเด็ก อายุครบ 4 ปี ที่บันทึกการฉีดวัคซีนไม่ครบ

select EPI.fname,EPI.lname,EPI.birth,EPI.age,EPI.DTP5,EPI.OPV5
,case when (EPI.DTP5 and EPI.OPV5) != 0 then 'com'else'notcom'end as cover
,EPI.pcucodeperson
FROM
(SELECT person.pcucodeperson,person.pid,person.birth,person.fname,person.lname,getagemonth(person.birth,CURDATE() )as age
,max(case when cdrug.files18epi='010'then visitepi.dateepi else " 0 " end)as BCG
,max(case when cdrug.files18epi in ('031','091')then visitepi.dateepi else " 0 " end)as DHB1
,max(case when cdrug.files18epi in('032','092')then visitepi.dateepi else " 0 " end)as DHB2
,max(case when cdrug.files18epi in('033','093')then visitepi.dateepi else " 0 " end)as DHB3
,max(case when cdrug.files18epi='034'then visitepi.dateepi else " 0 " end)as DTP4
,max(case when cdrug.files18epi='035'then visitepi.dateepi else " 0 " end)as DTP5
,max(case when cdrug.files18epi='081'then visitepi.dateepi else " 0 " end)as OPV1
,max(case when cdrug.files18epi='082'then visitepi.dateepi else " 0 " end)as OPV2
,max(case when cdrug.files18epi='083'then visitepi.dateepi else " 0 " end)as OPV3
,max(case when cdrug.files18epi='084'then visitepi.dateepi else " 0 " end)as OPV4
,max(case when cdrug.files18epi='085'then visitepi.dateepi else " 0 " end)as OPV5
,max(case when cdrug.files18epi='061'then visitepi.dateepi else " 0 " end)as MMR
,max(case when cdrug.files18epi='051'then visitepi.dateepi else " 0 " end)as JE1
,max(case when cdrug.files18epi='052'then visitepi.dateepi else " 0 " end)as JE2
,max(case when cdrug.files18epi='053'then visitepi.dateepi else " 0 " end)as JE3

FROM person
INNER JOIN house on person.hcode=house.hcode AND person.pcucodeperson=house.pcucodeperson
LEFT JOIN visitepi on person.pid=visitepi.pid AND person.pcucodeperson=visitepi.pcucodeperson
LEFT JOIN cdrug on visitepi.vaccinecode=cdrug.drugcode
WHERE person.typelive in ('1','3')
and getagemonth(person.birth,CURDATE() ) between 48 and 60
GROUP BY CONCAT(person.pcucodeperson,person.pid))as EPI 
WHERE EPI.DTP5= 0 or EPI.OPV5= 0
GROUP BY EPI.pid
ORDER BY EPI.birth;


ตรวจสอบการฝากครรภ์ครบ 5 ครั้งตามเกณฑ์


SELECT *
FROM(SELECT 
ctitle.titlename as 'คำนำหน้าชื่อ'
,person.fname as 'ชื่อ'
,person.lname as 'นามสกุล'
,getAgeYearNum(person.birth,visitanc.datecheck)as 'อายุ'
#,visitanc.ancno as 'ครรภ์ที่'
,visitancdeliver.pregno as 'G'
,house.hno as 'บ้านเลขที่',right(villcode,2)as 'หมู่ที่'
,visitancdeliver.datedeliver as 'วันที่คลอด'
,MAX(case when visitanc.ancno='1'then visitanc.datecheck ELSE" " end )as 'ancครั้งที่1'
,MAX(case when visitanc.ancno='2'then visitanc.datecheck ELSE" " end )as 'ancครั้งที่2'
,MAX(case when visitanc.ancno='3'then visitanc.datecheck ELSE" " end )as 'ancครั้งที่3'
,MAX(case when visitanc.ancno='4'then visitanc.datecheck ELSE" " end )as 'ancครั้งที่4'
,MAX(case when visitanc.ancno='5'then visitanc.datecheck ELSE" " end )as 'ancครั้งที่5'
FROM visitanc
INNER JOIN person on visitanc.pcucodeperson=person.pcucodeperson and visitanc.pid=person.pid
INNER JOIN house ON person.pcucodeperson=house.pcucodeperson AND person.hcode=house.hcode
INNER JOIN ctitle ON person.prename = ctitle.titlecode
INNER JOIN visitancdeliver on visitanc.pcucodeperson=visitancdeliver.pcucodeperson and visitanc.pid=visitancdeliver.pid
WHERE right(villcode,2)!='00'
and person.typelive in('1','3')
AND visitancdeliver.datedeliver BETWEEN '2013-04-01' and '2014-03-31'
GROUP BY person.pcucodeperson,person.pid,visitancdeliver.pregno
ORDER BY visitanc.datecheck) as anc_Q;


โค้ดอื่นๆ
และอื่นๆ