รายชื่อเด็กที่ได้รับวัคซีน BCG HBV
SELECT
person.pid AS pid,
concat(ctitle.titlename,person.fname,' ',person.lname)as pname,
person.birth AS pbirth,
GetAgeymd(person.birth,current_date())as age,
person.idcard AS pcid,
person.sex AS sex,
person.hnomoi AS hno,
village.villno AS village_villno,
max(case when visitepi.vaccinecode ='BCG' then GetAgeymd (person.birth,visitepi.dateepi) else '' end) as 'bcg',
max(case when visitepi.vaccinecode ='HBV1' then GetAgeymd (person.birth,visitepi.dateepi) else '' end) as 'hbv1',
max(case when visitepi.vaccinecode ='DHB1' then GetAgeymd (person.birth,visitepi.dateepi) else '' end) as 'dhb1',
max(case when visitepi.vaccinecode ='DHB2' then GetAgeymd (person.birth,visitepi.dateepi) else '' end) as 'dhb2',
max(case when visitepi.vaccinecode ='IPV-P' then GetAgeymd (person.birth,visitepi.dateepi) else '' end) as 'IPV',
max(case when visitepi.vaccinecode ='DHB3' then GetAgeymd (person.birth,visitepi.dateepi) else '' end) as 'dhb3',
max(case when visitepi.vaccinecode ='MMR' then GetAgeymd (person.birth,visitepi.dateepi) else '' end) as 'mmr'
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
inner join visitepi on person.pid = visitepi.pid
WHERE
person.pid not in (SELECT persondeath.pid FROM persondeath) and GetAgeyearnum(person.birth,'2016-10-01')='1'
and person.typelive in ('1','3')
AND nation='99'
group by person.pid
order BY age,village.villno,person.hnomoi*1
=================================================
รายชื่อเด็กที่ได้รับวัคซีน JE MMR2 2 ปี 6 เดือน
SELECT
person.pid AS pid,
concat(ctitle.titlename,person.fname,' ',person.lname)as pname,
person.birth AS pbirth,
GetAgeymd(person.birth,current_date())as age,
person.idcard AS pcid,
person.sex AS sex,
person.hnomoi AS hno,
village.villno AS village_villno,
max(case when visitepi.vaccinecode ='J12' then GetAgeymd (person.birth,visitepi.dateepi) else '' end) as 'J12',
max(case when visitepi.vaccinecode ='MMR2' then GetAgeymd (person.birth,visitepi.dateepi) else '' end) as 'MMR2'
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
inner join visitepi on person.pid = visitepi.pid
WHERE
person.pid not in (SELECT persondeath.pid FROM persondeath) and GetAgeyearnum(person.birth,'2016-10-01')='3'
and person.typelive in ('1','3')
AND nation='99'
group by person.pid
order BY age,village.villno,person.hnomoi*1
=============================================
รายชื่อเด็กที่ได้รับวัคซีน DTP4 OPV4
SELECT
person.pid AS pid,
concat(ctitle.titlename,person.fname,' ',person.lname)as pname,
person.birth AS pbirth,
GetAgeymd(person.birth,current_date())as age,
person.idcard AS pcid,
person.sex AS sex,
person.hnomoi AS hno,
village.villno AS village_villno,
max(case when visitepi.vaccinecode ='DTP4' then GetAgeymd (person.birth,visitepi.dateepi) else '' end) as 'DTP4',
max(case when visitepi.vaccinecode ='OPV4' then GetAgeymd (person.birth,visitepi.dateepi) else '' end) as 'OPV4',
max(case when visitepi.vaccinecode ='J11' then GetAgeymd (person.birth,visitepi.dateepi) else '' end) as 'J11'
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
inner join visitepi on person.pid = visitepi.pid
WHERE
person.pid not in (SELECT persondeath.pid FROM persondeath) and GetAgeyearnum(person.birth,'2016-10-01')='2'
and person.typelive in ('1','3')
AND nation='99'
group by person.pid
order BY age,village.villno,person.hnomoi*1
===================================================
รายชื่อเด็กที่ได้รับวัคซีน DTP5 OPV5
SELECT
person.pid AS pid,
concat(ctitle.titlename,person.fname,' ',person.lname)as pname,
person.birth AS pbirth,
GetAgeymd(person.birth,current_date())as age,
person.idcard AS pcid,
person.sex AS sex,
person.hnomoi AS hno,
village.villno AS village_villno,
max(case when visitepi.vaccinecode ='DTP5' then GetAgeymd (person.birth,visitepi.dateepi) else '' end) as 'DTP5',
max(case when visitepi.vaccinecode ='OPV5' then GetAgeymd (person.birth,visitepi.dateepi) else '' end) as 'OPV5'
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
inner join visitepi on person.pid = visitepi.pid
WHERE
person.pid not in (SELECT persondeath.pid FROM persondeath) and GetAgeyearnum(person.birth,'2016-10-01')='5'
and person.typelive in ('1','3')
AND nation='99'
group by person.pid
order BY age,village.villno,person.hnomoi*1
===============================================
เป้าหมายและผลงานความครอบคลุมการได้รับวัคซีนเด็กอายุตรบ 1 ปี (เริ่ม 1 ตค 59)
SELECT distinct
village.villno as villno ,village.villname as villname,
count(distinct case when GetAgeyearnum(person.birth,'2016-10-01') ='1' then person.pid else null end)as pop1y,
count(distinct case when visitepi.vaccinecode ='BCG' then person.pid else null end) as 'bcg',
count(distinct case when visitepi.vaccinecode ='HBV1' then person.pid else null end) as 'hbv1',
count(distinct case when visitepi.vaccinecode ='DHB1' then person.pid else null end) as 'dhb1',
count(distinct case when visitepi.vaccinecode ='DHB2' then person.pid else null end) as 'dhb2',
count(distinct case when visitepi.vaccinecode ='IPV-P' then person.pid else null end) as 'IPV',
count(distinct case when visitepi.vaccinecode ='DHB3' then person.pid else null end) as 'dhb3',
count(distinct case when visitepi.vaccinecode ='MMR' then person.pid else null end) as 'mmr'
FROM
person
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 visitepi on person.pid = visitepi.pid
WHERE
person.pid not in (SELECT persondeath.pid FROM persondeath)
and GetAgeyearnum(person.birth,'2016-10-01') ='1'
and person.typelive in ('1','3')
group by villno,villno
==============================================
เป้าหมายและผลงานความครอบคลุมการได้รับวัคซีนเด็กอายุตรบ 5 ปี (เริ่ม 1 ตค 59)
SELECT distinct
village.villno as villno ,village.villname as villname,
count(distinct case when GetAgeyearnum(person.birth,'2016-10-01') ='5' then person.pid else null end)as pop5y,
count(distinct case when visitepi.vaccinecode ='DTP5' then person.pid else null end)as 'DTP5',
count(distinct case when visitepi.vaccinecode ='OPV5' then person.pid else null end)as 'OPV5'
FROM
person
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 visitepi on person.pid = visitepi.pid
WHERE
person.pid not in (SELECT persondeath.pid FROM persondeath) and GetAgeyearnum(person.birth,'2016-10-01') ='5'
and person.typelive in ('1','3')
AND nation='99'
group by villno,villno
village.villno as villno ,village.villname as villname,
count(distinct case when GetAgeyearnum(person.birth,'2016-10-01') ='5' then person.pid else null end)as pop5y,
count(distinct case when visitepi.vaccinecode ='DTP5' then person.pid else null end)as 'DTP5',
count(distinct case when visitepi.vaccinecode ='OPV5' then person.pid else null end)as 'OPV5'
FROM
person
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 visitepi on person.pid = visitepi.pid
WHERE
person.pid not in (SELECT persondeath.pid FROM persondeath) and GetAgeyearnum(person.birth,'2016-10-01') ='5'
and person.typelive in ('1','3')
AND nation='99'
group by villno,villno
======================================================
เป้าหมายและผลงานความครอบคลุมการได้รับวัคซีนเด็กอายุตรบ 3 ปี (เริ่ม 1 ตค 59)
SELECT distinct
village.villno as villno ,village.villname as villname,
count(distinct case when GetAgeyearnum(person.birth,'2016-10-01') ='3' then person.pid else null end)as pop3y,
count(distinct case when visitepi.vaccinecode ='J12' then person.pid else null end) as 'J12',
count(distinct case when visitepi.vaccinecode ='MMR2' then person.pid else null end) as 'MMR2'
FROM
person
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 visitepi on person.pid = visitepi.pid
WHERE
person.pid not in (SELECT persondeath.pid FROM persondeath) and GetAgeyearnum(person.birth,'2016-10-01') ='3'
and person.typelive in ('1','3')
AND nation='99'
group by villno,villno
village.villno as villno ,village.villname as villname,
count(distinct case when GetAgeyearnum(person.birth,'2016-10-01') ='3' then person.pid else null end)as pop3y,
count(distinct case when visitepi.vaccinecode ='J12' then person.pid else null end) as 'J12',
count(distinct case when visitepi.vaccinecode ='MMR2' then person.pid else null end) as 'MMR2'
FROM
person
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 visitepi on person.pid = visitepi.pid
WHERE
person.pid not in (SELECT persondeath.pid FROM persondeath) and GetAgeyearnum(person.birth,'2016-10-01') ='3'
and person.typelive in ('1','3')
AND nation='99'
group by villno,villno
===============================================
เป้าหมายและผลงานความครอบคลุมการได้รับวัคซีนเด็กอายุตรบ 2 ปี (เริ่ม 1 ตค 59)
SELECT distinct
village.villno as villno ,village.villname as villname,
count(distinct case when GetAgeyearnum(person.birth,'2016-10-01') ='2' then person.pid else null end)as pop2y,
count(distinct case when visitepi.vaccinecode ='J11' then person.pid else null end) as 'laje1',
count(distinct case when visitepi.vaccinecode ='DTP4' then person.pid else null end) as 'dtp4',
count(distinct case when visitepi.vaccinecode ='OPV4' then person.pid else null end) as 'opv4'
FROM
person
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 visitepi on person.pid = visitepi.pid
village.villno as villno ,village.villname as villname,
count(distinct case when GetAgeyearnum(person.birth,'2016-10-01') ='2' then person.pid else null end)as pop2y,
count(distinct case when visitepi.vaccinecode ='J11' then person.pid else null end) as 'laje1',
count(distinct case when visitepi.vaccinecode ='DTP4' then person.pid else null end) as 'dtp4',
count(distinct case when visitepi.vaccinecode ='OPV4' then person.pid else null end) as 'opv4'
FROM
person
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 visitepi on person.pid = visitepi.pid
WHERE
person.pid not in (SELECT persondeath.pid FROM persondeath) and GetAgeyearnum(person.birth,'2016-10-01') ='2'
and person.typelive in ('1','3')
AND nation='99'
group by villno,villno
person.pid not in (SELECT persondeath.pid FROM persondeath) and GetAgeyearnum(person.birth,'2016-10-01') ='2'
and person.typelive in ('1','3')
AND nation='99'
group by villno,villno
==================================================
select village.villno as villno ,village.villname as villname,
count(house.hcode) as home,
sum(case when person.sex = '1' and person.typelive in ('1','3') then 1 else 0 end) as 'ชาย',
sum(case when person.sex = '2' and person.typelive in ('1','3') then 1 else 0 end) as 'หญิง',
sum(case when GetAgeYearNum(person.birth,'2016-10-01') < 6 then 1 else '' end) as '0-5ปี'
,(SELECT count(distinct visitanc.pid )FROM visitanc
WHERE visitanc.datecheck BETWEEN '2016-10-01'AND '2017-09-30') AS 'ANC'
,sum(case when GetAgeYearNum(person.birth,'2016-10-01') between 5 and 14 then 1 else '' end) as '5-14ปี'
,sum(case when GetAgeYearNum(person.birth,'2016-10-01') between 15 and 21 then 1 else '' end) as '15-21ปี'
,sum(case when GetAgeYearNum(person.birth,'2016-10-01') between 15 and 59 then 1 else '' end) as '15-59ปี'
,sum(case when GetAgeYearNum(person.birth,'2016-10-01') >= 60 then 1 else '' end) as '60ปี'
,sum(case when GetAgeYearNum(person.birth,'2016-10-01') between 15 and 19 and person.sex = '2' then 1 else '' end) as 'หญิง15-19ปี'
,sum(case when GetAgeYearNum(person.birth,'2016-10-01') between 30 and 60 and person.sex = '2' then 1 else '' end) as 'หญิง30-60ปี'
,sum(case when GetAgeYearNum(person.birth,'2016-10-01') between 30 and 70 and person.sex = '2' then 1 else '' end) as 'หญิง30-70ปี'
,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
inner join personunable1type p on personunable.pid = p.pid inner join cpersonincomplete c on p.typecode = c.incompletecode where c.incompletetype = '1') then 1 else '' end) as 'พิการทางการมองเห็น'
,sum(case when concat(person.pcucodeperson,person.pid)in(select concat(personunable.pcucodeperson,personunable.pid )from personunable
inner join personunable1type p on personunable.pid = p.pid inner join cpersonincomplete c on p.typecode = c.incompletecode where c.incompletetype = '2') then 1 else '' end) as 'พิการทางการได้ยิน'
,sum(case when concat(person.pcucodeperson,person.pid)in(select concat(personunable.pcucodeperson,personunable.pid )from personunable
inner join personunable1type p on personunable.pid = p.pid inner join cpersonincomplete c on p.typecode = c.incompletecode where c.incompletetype = '3') then 1 else '' end) as 'พิการทางกาย'
,sum(case when concat(person.pcucodeperson,person.pid)in(select concat(personunable.pcucodeperson,personunable.pid )from personunable
inner join personunable1type p on personunable.pid = p.pid inner join cpersonincomplete c on p.typecode = c.incompletecode where c.incompletetype = '4') then 1 else '' end) as 'พิการทางจิต'
,sum(case when concat(person.pcucodeperson,person.pid)in(select concat(personunable.pcucodeperson,personunable.pid )from personunable
inner join personunable1type p on personunable.pid = p.pid inner join cpersonincomplete c on p.typecode = c.incompletecode where c.incompletetype = '5') then 1 else '' end) as 'พิการทางสติปัญญา'
,sum(case when concat(person.pcucodeperson,person.pid)in(select concat(personunable.pcucodeperson,personunable.pid )from personunable
inner join personunable1type p on personunable.pid = p.pid inner join cpersonincomplete c on p.typecode = c.incompletecode where c.incompletetype = '6') then 1 else '' end) as 'พิการทางการเรียนรู้'
,sum(case when concat(person.pcucodeperson,person.pid)in(select concat(personunable.pcucodeperson,personunable.pid )from personunable
inner join personunable1type p on personunable.pid = p.pid inner join cpersonincomplete c on p.typecode = c.incompletecode where c.incompletetype = '7') 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
==============================================
ตรวจสอบความครอบคลุมการได้รับวัคซีน IPV
SELECT
person.pid AS pid,
concat(ctitle.titlename,person.fname,' ',person.lname)as pname,
person.birth AS pbirth,
GetAgeymd(person.birth,current_date())as age,
person.idcard AS pcid,
person.sex AS sex,
person.hnomoi AS hno,
village.villno AS village_villno,
max(case when visitepi.vaccinecode ='IPV-P' then GetAgeymd (person.birth,visitepi.dateepi) else '' end) as 'IPV'
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 visitepi on person.pid = visitepi.pid
WHERE
person.pid not in (SELECT persondeath.pid FROM persondeath) and person.birth >= '2015-08-01'
and person.typelive in ('1','3') and GetAgemonth(person.birth,current_date())>='4'
group by person.pid
order BY age,village.villno,person.hnomoi*1
============================================
การใช้ยา NCD รายบุคคล (เปลี่ยน วดป ที่ต้องการ ตามช่วงเวลที่ต้องการประมวลผล)
select distinct person.pcucodeperson,
person.fname,person.lname,person.pid ,visit.visitdate,v.appodate ,cdrug.drugname ,substring(cdrug.drugcode24,1,5),
visitdrug.dose,v.appotype
from
person
inner join visit on person.pcucodeperson = visit.pcucodeperson and person.pid = visit.pid
inner join visitdiag on visit.pcucode = visitdiag.pcucode and visit.visitno = visitdiag.visitno
inner join visitdrug on visit.pcucode = visitdrug.pcucode and visit.visitno = visitdrug.visitno
inner join cdrug on visitdrug.drugcode = cdrug.drugcode
inner join personchronic on person.pid =personchronic.pid
inner join visitdiagappoint v on visit.visitno = v.visitno
where v.appodate between '2017-01-21'and '2017-03-20' and cdrug.drugtype = '01'and cdrug.unitsell in ('027','006','009')
and substring(cdrug.drugcode24,1,5) in ('10043','10044','10045','10059','10073','10061','10106','10107','10143','10144','10557','12481','12488','10104','14053','20303')
and v.appotype is not null
order by person.pid
=============================================
โค้ดรายชื่อผู้ป่วย NCD พิการ สูงอายุ จำแนกตามลุ่มการดูแล (ทุกกลุ่ม)
SELECT
person.pid AS pid,
concat(ctitle.titlename,person.fname,' ',person.lname)as pname,
person.idcard AS pcid,
DATE_FORMAT(person.birth,'%d-%m-%Y') AS pbirth,
GetAgeYearNum(person.birth,CURRENT_DATE)as age,
person.hnomoi AS hno,
village.villno AS village_villno,
max(case when person.candobedhomesocial = '1'then "/" else '' end) as old1,
max(case when person.candobedhomesocial = '2'then "/" else '' end) as old2,
max(case when person.candobedhomesocial = '3'then "/" else '' end) as old3,
max(case when cdisease.codechronic = 01 then "/" else '' end) as 'HT',
max(case when cdisease.codechronic = 02 then "/" else '' end) as 'หอบหืด',
max(case when cdisease.codechronic = 03 then "/" else '' end) as 'หัวใจขาดเลือด',
max(case when cdisease.codechronic = 04 then "/" else '' end) as 'มะเร็ง',
max(case when cdisease.codechronic = 05 then "/" else '' end) as 'โลหิตจาง',
max(case when cdisease.codechronic = 06 then "/" else '' end) as 'ซึมเศร้า',
max(case when cdisease.codechronic = 07 then "/" else '' end) as 'หลอดเลือดสมอง',
max(case when cdisease.codechronic = 08 then "/" else '' end) as 'อัมพฤกษ์ อัมพาต',
max(case when cdisease.codechronic = 09 then "/" else '' end) as 'ไตวาย',
max(case when cdisease.codechronic = 10 then "/" else '' end) as 'เบาหวาน',
max(case when cdisease.codechronic = 12 then "/" else '' end) as 'ถุงลมโป่งพอง',
max(case when cdisease.codechronic = 13 then "/" else '' end) as 'โรคหัวใจ',
max(case when cdisease.codechronic = 15 then "/" else '' end) as 'COPD',
max(case when cdisease.codechronic = 16 then "/" else '' end) as 'TB',
max(case when cpersonincomplete.incompletetype = 1 then "/" else '' end) as 'มองเห็น',
max(case when cpersonincomplete.incompletetype = 2 then "/" else '' end) as 'ได้ยิน',
max(case when cpersonincomplete.incompletetype = 3 then "/" else '' end) as 'ทางกาย',
max(case when cpersonincomplete.incompletetype = 4 then "/" else '' end) as 'ทางจิต',
max(case when cpersonincomplete.incompletetype = 5 then "/" else '' end) as 'สติปัญญา',
max(case when cpersonincomplete.incompletetype = 6 then "/" else '' end) as 'การเรียนรู้',
max(case when cpersonincomplete.incompletetype = 7 then "/" else '' end) as 'ออทิสติก',
max(case when concat(person.pcucodeperson,person.pid)in (select concat(personunable.pcucodeperson,personunable.pid )
from personunable )and person.candobedhomesocial = '1' then "/" else '' end) as 'พิการกลุ่ม1',
max(case when concat(person.pcucodeperson,person.pid)in (select concat(personunable.pcucodeperson,personunable.pid )
from personunable )and person.candobedhomesocial = '2' then "/" else '' end) as 'พิการกลุ่ม2',
max(case when concat(person.pcucodeperson,person.pid)in (select concat(personunable.pcucodeperson,personunable.pid )
from personunable )and person.candobedhomesocial = '3' then "/" else '' end) as 'พิการกลุ่ม3',
max(case when v.screenresult is not null and v.datescreen between '2015-10-01' and '2016-09-30' then v.datescreen else '' end) as 'ตรวจตาต้อกระจก',
max(case when v.eyeright3m = '1'and v.datescreen between '2015-10-01' and '2016-09-30'then 'มองเห็น'else '' end) as 'ตาขวา',
max(case when v.eyeleft3m = '1'and v.datescreen between '2015-10-01' and '2016-09-30'then 'มองเห็น'else '' end) as 'ตาซ้าย',
max(case when person.pid in (select visitlabcancer.pid from visitlabcancer inner join visit on visitlabcancer.pid = visit.pid inner join visitdiag on visit.visitno = visitdiag.visitno
and visitlabcancer.typecancer = '1' and visit.visitdate between '2015-10-01' and '2016-09-30' and visitlabcancer.result ='0'
and visitdiag.diagcode = 'Z12.3')
then "/" else '' end)as 'ตรวจเต้านมปกติ',
max(case when person.pid in (select visitlabcancer.pid from visitlabcancer inner join visit on visitlabcancer.pid = visit.pid inner join visitdiag on visit.visitno = visitdiag.visitno
and visitlabcancer.typecancer = '1' and visit.visitdate between '2015-10-01' and '2016-09-30' and visitlabcancer.result in('1','2','5','6','9')
and visitdiag.diagcode = 'Z12.3')
then "/" else '' end)as 'ตรวจเต้านมผิดปกติ',
CONCAT(p1.fname," ",p1.lname)as 'อสม'
FROM
person INNER JOIN ctitle ON person.prename = ctitle.titlecode
INNER JOIN house ON person.pcucodeperson = house.pcucode AND person.hcode = house.hcode
LEFT JOIN person as p1 on house.pidvola=p1.pid and house.pcucodepersonvola=p1.pcucodeperson
INNER JOIN village ON house.pcucode = village.pcucode AND village.villcode = house.villcode
left join personchronic on person.pid = personchronic.pid
left join cdisease on personchronic.chroniccode = cdisease.diseasecode
left join cdiseasechronic on cdisease.codechronic = cdiseasechronic.groupcode
left join visitcataractvolunteer v on person.pid = v.pid
left join personunable1type on person.pid = personunable1type.pid
left join cpersonincomplete on personunable1type.typecode = cpersonincomplete.incompletecode
WHERE
person.typelive IN ('1','3')
and person.pid not in (SELECT persondeath.pid FROM persondeath)
and SUBSTRING(house.villcode,7,2)
group by person.pid
------------------------------------------------------------------------------
โค้ดรายชื่อผู้ป่วย NCD พิการ สูงอายุ จำแนกตามลุ่มการดูแล (สูงอายุ)
=======================================================
--------------------------------------------------------------------------------------
รายชื่อผู้ป่วย 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='/'
=============================================================
ตรวจสอบประชากรได้สิทธิ 74 แต่ไม่ระบุความพิการ
=====================
SELECT person.pid
,concat(ctitle.titlename,' ',person.fname,' ',person.lname) as ชือ
,person.idcard as CID
,concat(substr(person.birth,9,2),'/',substr(person.birth,6,2),'/',substr(person.birth,1,4)+543) as birth
,getAgeYearNum(person.birth,CURDATE()) AS อายุ
,house.hno as บ้านเลขที
,village.villno AS หมู่
,person.rightcode
,rightno
,concat(substr(person.datestart,9,2),'/',substr(person.datestart,6,2),'/',substr(person.datestart,1,4 )+543) as datestart
FROM person
LEFT JOIN ctitle ON person.prename = ctitle.titlecode
INNER JOIN house ON person.hcode=house.hcode and person.pcucodeperson=house.pcucode
INNER JOIN village ON house.villcode=village.villcode and house.pcucode = village.pcucode WHERE person.typelive in (0,1,3) and person.dischargetype='9' and person.rightcode = '74' and person.pid not in (SELECT personunable.pid FROM personunable)
ORDER BY village.villno;
=====================
SELECT person.pid
,concat(ctitle.titlename,' ',person.fname,' ',person.lname) as ชือ
,person.idcard as CID
,concat(substr(person.birth,9,2),'/',substr(person.birth,6,2),'/',substr(person.birth,1,4)+543) as birth
,getAgeYearNum(person.birth,CURDATE()) AS อายุ
,house.hno as บ้านเลขที
,village.villno AS หมู่
,person.rightcode
,rightno
,concat(substr(person.datestart,9,2),'/',substr(person.datestart,6,2),'/',substr(person.datestart,1,4 )+543) as datestart
FROM person
LEFT JOIN ctitle ON person.prename = ctitle.titlecode
INNER JOIN house ON person.hcode=house.hcode and person.pcucodeperson=house.pcucode
INNER JOIN village ON house.villcode=village.villcode and house.pcucode = village.pcucode WHERE person.typelive in (0,1,3) and person.dischargetype='9' and person.rightcode = '74' and person.pid not in (SELECT personunable.pid FROM personunable)
ORDER BY village.villno;
=============================================================
ตรวจสอบท้องไดโนเสา
=================
select
concat(ctitle.titlename,person.fname,' ',person.lname)as pname,
person.idcard as pcid,
person.hnomoi as hno,
village.villno as village_villno,v.pregno,visitancpregnancy.lmp,visitancpregnancy.edc,
ROUND(DATEDIFF(current_date,visitancpregnancy.lmp)/7) as f
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
inner join visitanc v on person.pid = v.pid
inner join visitancpregnancy on v.pid = visitancpregnancy.pid and v.pregno = visitancpregnancy.pregno
where
person.typelive in ('1','3')
and person.pid not in (select persondeath.pid from persondeath) and v.pid not in (select visitancdeliver.pid from visitancdeliver)
and ROUND(DATEDIFF(current_date,visitancpregnancy.lmp)/7)> '40'
group by person.pid
=================
select
concat(ctitle.titlename,person.fname,' ',person.lname)as pname,
person.idcard as pcid,
person.hnomoi as hno,
village.villno as village_villno,v.pregno,visitancpregnancy.lmp,visitancpregnancy.edc,
ROUND(DATEDIFF(current_date,visitancpregnancy.lmp)/7) as f
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
inner join visitanc v on person.pid = v.pid
inner join visitancpregnancy on v.pid = visitancpregnancy.pid and v.pregno = visitancpregnancy.pregno
where
person.typelive in ('1','3')
and person.pid not in (select persondeath.pid from persondeath) and v.pid not in (select visitancdeliver.pid from visitancdeliver)
and ROUND(DATEDIFF(current_date,visitancpregnancy.lmp)/7)> '40'
group by person.pid
=================================================================
code รายชื่อคนที่ยังไม่ได้คัดกรองปี 59 (เปลี่ยน วดป )
=========================
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)
and person.pid not in (select pid from ncd_person_ncd_screen where screen_date between '2015-10-01' and '2016-09-30')
group by person.pid
order by villno, person.pid
=========================
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)
and person.pid not in (select pid from ncd_person_ncd_screen where screen_date between '2015-10-01' and '2016-09-30')
group by person.pid
order by villno, person.pid
=====================================================
ตรวจสอบข้อมูลการฝากครรภ์และการคลอด
1. run script anc_all โหลดไฟล์https://www.dropbox.com/s/6rub18ayh98cdfg/anc_all.sql?dl=0
2. รัน code
======
SELECT person.pid AS pid,person.idcard,
concat(person.fname,' ',person.lname)as pname,
person.birth AS pbirth,
GetAgeymd(person.birth,current_date())as age,
person.sex AS sex,
person.hnomoi AS hno
,village.villno AS 'หมู่',a.typearea,a.`ครรภ์ที่`,a.`วันเกิด`,a.`สถานที่เกิด`
,concat(a.`ครั้งที่ 1 (wks)`,'/',a.`วันที่ตรวจ`,'/',a.`หน่วยที่บันทึก1`) as 'ครั้งที่1(wks/วันที่/pcucode)'
,concat(a.`ครั้งที่ 2 (wks)`,'/',a.`วันที่ตรวจ1`,'/',a.`หน่วยที่บันทึก2`) as 'ครั้งที่2(wks/วันที่/pcucode)'
,concat(a.`ครั้งที่ 3 (wks)`,'/',a.`วันที่ตรวจ2`,'/',a.`หน่วยที่บันทึก3`) as 'ครั้งที่3(wks/วันที่/pcucode)'
,concat(a.`ครั้งที่ 4 (wks)`,'/',a.`วันที่ตรวจ3`,'/',a.`หน่วยที่บันทึก4`) as 'ครั้งที่4(wks/วันที่/pcucode)'
,concat(a.`ครั้งที่ 5 (wks)`,'/',a.`วันที่ตรวจ4`,'/',a.`หน่วยที่บันทึก5`) as 'ครั้งที่5(wks/วันที่/pcucode)'
FROM anc_all a
inner join person on a.cid =person.idcard
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
3. จะได้รายชื่อหญิงตั้งครรภ์ และหญิงคลอด โดยอิงจาก idcard ที่มีในฐาน jhcis ให้นำข้อมูลไปตรวจสอบการฝากครรภ์ (ข้อมูลการฝากครรภ์ที่ว่าง) และนำมาบันทึกในฐานข้อมูลทุกราย
1. run script anc_all โหลดไฟล์https://www.dropbox.com/s/6rub18ayh98cdfg/anc_all.sql?dl=0
2. รัน code
======
SELECT person.pid AS pid,person.idcard,
concat(person.fname,' ',person.lname)as pname,
person.birth AS pbirth,
GetAgeymd(person.birth,current_date())as age,
person.sex AS sex,
person.hnomoi AS hno
,village.villno AS 'หมู่',a.typearea,a.`ครรภ์ที่`,a.`วันเกิด`,a.`สถานที่เกิด`
,concat(a.`ครั้งที่ 1 (wks)`,'/',a.`วันที่ตรวจ`,'/',a.`หน่วยที่บันทึก1`) as 'ครั้งที่1(wks/วันที่/pcucode)'
,concat(a.`ครั้งที่ 2 (wks)`,'/',a.`วันที่ตรวจ1`,'/',a.`หน่วยที่บันทึก2`) as 'ครั้งที่2(wks/วันที่/pcucode)'
,concat(a.`ครั้งที่ 3 (wks)`,'/',a.`วันที่ตรวจ2`,'/',a.`หน่วยที่บันทึก3`) as 'ครั้งที่3(wks/วันที่/pcucode)'
,concat(a.`ครั้งที่ 4 (wks)`,'/',a.`วันที่ตรวจ3`,'/',a.`หน่วยที่บันทึก4`) as 'ครั้งที่4(wks/วันที่/pcucode)'
,concat(a.`ครั้งที่ 5 (wks)`,'/',a.`วันที่ตรวจ4`,'/',a.`หน่วยที่บันทึก5`) as 'ครั้งที่5(wks/วันที่/pcucode)'
FROM anc_all a
inner join person on a.cid =person.idcard
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
3. จะได้รายชื่อหญิงตั้งครรภ์ และหญิงคลอด โดยอิงจาก idcard ที่มีในฐาน jhcis ให้นำข้อมูลไปตรวจสอบการฝากครรภ์ (ข้อมูลการฝากครรภ์ที่ว่าง) และนำมาบันทึกในฐานข้อมูลทุกราย
=================================================
รายชื่อหญิงอายุ 30-70 ปี ที่ยังไม่ได้รับการตรวจเต้านม
=============================
select person.pid,
concat(ctitle.titlename,' ',person.fname,' ',person.lname) as pname,
person.idcard ,
person.birth,
getAgeYearNum(person.birth,CURDATE()) as age,
concat(person.hnomoi,' ','ม.',person.mumoi)as addr
from person
left join ctitle on person.prename = ctitle.titlecode
inner join house on person.hcode = house.hcode and person.pcucodeperson = house.pcucode
inner join village on house.villcode= village.villcode
where GetAgeYearNum(person.birth,'2015-10-01') between '30' and '70'and person.typelive in ('1','3')
and villno <> '00' and person.sex = '2'
and person.pid not in (select pid from visitlabcancer vc inner join visitdiag on vc.visitno = visitdiag.visitno where vc.typecancer = '1' and vc.datecheck between '2015-10-01' and '2016-09-30'
and visitdiag.diagcode = 'Z12.3')
and person.pid not in (select persondeath.pid from persondeath)
group by person.pid
=============================
select person.pid,
concat(ctitle.titlename,' ',person.fname,' ',person.lname) as pname,
person.idcard ,
person.birth,
getAgeYearNum(person.birth,CURDATE()) as age,
concat(person.hnomoi,' ','ม.',person.mumoi)as addr
from person
left join ctitle on person.prename = ctitle.titlecode
inner join house on person.hcode = house.hcode and person.pcucodeperson = house.pcucode
inner join village on house.villcode= village.villcode
where GetAgeYearNum(person.birth,'2015-10-01') between '30' and '70'and person.typelive in ('1','3')
and villno <> '00' and person.sex = '2'
and person.pid not in (select pid from visitlabcancer vc inner join visitdiag on vc.visitno = visitdiag.visitno where vc.typecancer = '1' and vc.datecheck between '2015-10-01' and '2016-09-30'
and visitdiag.diagcode = 'Z12.3')
and person.pid not in (select persondeath.pid from persondeath)
group by person.pid
======================================
code รายชื่อผู้ป่วยเรื้อรังที่ยังไม่ได้ yearly ปี 59
แต่ขอให้ตรวจสอบด้วย บางรายที่เจาะไปแล้วอาจจะเป็นการ recheck lab เพียงบางตัว
==================================
select
concat(ctitle.titlename,person.fname,' ',person.lname)as pname,
person.idcard as pidcard,
GetAgeYearNum(person.birth,current_date())as age,
concat(person.hnomoi,' ','หมู่',' ',person.mumoi) as hmu,
max(case when personchronic.chroniccode = 'I10' then "/" else null end) as HT,
max(case when personchronic.chroniccode between 'E10' and 'E15' then "/" else null end) as DM
from person
inner join ctitle on person.prename = ctitle.titlecode
inner join personchronic on person.pid=personchronic.pid
inner join cdisease on personchronic.chroniccode=cdisease.diseasecode
inner join house on person.hcode = house.hcode and person.pcucodeperson = house.pcucode
inner join village on house.villcode = village.villcode and house.pcucode = village.pcucode
WHERE ((person.dischargetype is null) OR (person.dischargetype != '1'))
and person.pid not in (select visitlabchcyhembmsse.pid from visitlabchcyhembmsse where visitlabchcyhembmsse.datecheck between '2015-10-01' and '2016-09-30')
and SUBSTRING(house.villcode,7,2)<> '00' and (personchronic.chroniccode = 'I10' or personchronic.chroniccode between 'E10' and 'E15' )
and personchronic.cup = person.pcucodeperson
GROUP BY person.pid
order by village.villno
แต่ขอให้ตรวจสอบด้วย บางรายที่เจาะไปแล้วอาจจะเป็นการ recheck lab เพียงบางตัว
==================================
select
concat(ctitle.titlename,person.fname,' ',person.lname)as pname,
person.idcard as pidcard,
GetAgeYearNum(person.birth,current_date())as age,
concat(person.hnomoi,' ','หมู่',' ',person.mumoi) as hmu,
max(case when personchronic.chroniccode = 'I10' then "/" else null end) as HT,
max(case when personchronic.chroniccode between 'E10' and 'E15' then "/" else null end) as DM
from person
inner join ctitle on person.prename = ctitle.titlecode
inner join personchronic on person.pid=personchronic.pid
inner join cdisease on personchronic.chroniccode=cdisease.diseasecode
inner join house on person.hcode = house.hcode and person.pcucodeperson = house.pcucode
inner join village on house.villcode = village.villcode and house.pcucode = village.pcucode
WHERE ((person.dischargetype is null) OR (person.dischargetype != '1'))
and person.pid not in (select visitlabchcyhembmsse.pid from visitlabchcyhembmsse where visitlabchcyhembmsse.datecheck between '2015-10-01' and '2016-09-30')
and SUBSTRING(house.villcode,7,2)<> '00' and (personchronic.chroniccode = 'I10' or personchronic.chroniccode between 'E10' and 'E15' )
and personchronic.cup = person.pcucodeperson
GROUP BY person.pid
order by village.villno
=========================================
รายชื่อเป้าหมายคัดกรอง NCD ปีงบ 60
เงื่อนไข
1. อายุ 35 ปีขึ้นไป ณวันที่ 1 ตุลาคม 59
2. ตัดผู้ป่วยสองโรค
================================
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,'2016-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',
concat(v.fname," ",v.lname) as 'อสม'
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
left join person as v ON house.pidvola = v.pid AND house.pcucodepersonvola = v.pcucodeperson
where person.typelive in ('1','3') and person.pid not in (select persondeath.pid from persondeath)
and GetAgeYearNum(person.birth,'2016-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
===================================================
การบันทึก คัดกรองDSPM ในกลุ่มอายุ 9m 18m 30m 42m หากบันทึกไม่ตรงช่วงอายุ HDC จะไม่นับผลงาน วันนี้มีตัวช่วยแล้วCode รายชื่อกลุ่มอายุ 9 - 45m พร้อมระบุ ช่วงวันที่ๆ ควรนัดคัดกรอง DSPM หากรายชื่อนั้นๆ อายุ 9m 18m 30m 42m แค่บันทึกให้อยู่ในช่วงที่ระบุ
รายชื่อเป้าหมายคัดกรอง NCD ปีงบ 60
เงื่อนไข
1. อายุ 35 ปีขึ้นไป ณวันที่ 1 ตุลาคม 59
2. ตัดผู้ป่วยสองโรค
================================
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,'2016-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',
concat(v.fname," ",v.lname) as 'อสม'
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
left join person as v ON house.pidvola = v.pid AND house.pcucodepersonvola = v.pcucodeperson
where person.typelive in ('1','3') and person.pid not in (select persondeath.pid from persondeath)
and GetAgeYearNum(person.birth,'2016-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
===================================================
การบันทึก คัดกรองDSPM ในกลุ่มอายุ 9m 18m 30m 42m หากบันทึกไม่ตรงช่วงอายุ HDC จะไม่นับผลงาน วันนี้มีตัวช่วยแล้วCode รายชื่อกลุ่มอายุ 9 - 45m พร้อมระบุ ช่วงวันที่ๆ ควรนัดคัดกรอง DSPM หากรายชื่อนั้นๆ อายุ 9m 18m 30m 42m แค่บันทึกให้อยู่ในช่วงที่ระบุ
=============================
selectperson.pid as pid,
concat(ctitle.titlename,person.fname,' ',person.lname)as pname,
person.birth as pbirth,
GetAgeymd(person.birth,current_date())as age_now,
person.idcard as pcid,
person.sex as sex,
person.hnomoi as hno,
village.villno as villno,
concat(CONCAT(substr(DATE_ADD(person.birth, INTERVAL +9 month) ,9,2),"-",
substr(DATE_ADD(person.birth, INTERVAL +9 month) ,6,2)
,"-",substr(DATE_ADD(person.birth, INTERVAL +9 month) ,1,4)+543) ,"---",
CONCAT(substr(DATE_ADD(person.birth, INTERVAL + 10 month) -1 ,7,2),"-",
substr(DATE_ADD(person.birth, INTERVAL + 10 month) -1 ,5,2),
"-",substr(DATE_ADD(person.birth, INTERVAL + 10 month) -1 ,1,4)+543)) '9m',
concat(CONCAT(substr(DATE_ADD(person.birth, INTERVAL +18 month) ,9,2),"-",
substr(DATE_ADD(person.birth, INTERVAL +18 month) ,6,2)
,"-",substr(DATE_ADD(person.birth, INTERVAL +18 month) ,1,4)+543) ,"---",
CONCAT(substr(DATE_ADD(person.birth, INTERVAL + 19 month) -1 ,7,2),"-",
substr(DATE_ADD(person.birth, INTERVAL + 19 month) -1 ,5,2),
"-",substr(DATE_ADD(person.birth, INTERVAL + 19 month) -1 ,1,4)+543)) '18m',
substr(DATE_ADD(person.birth, INTERVAL +18 month) ,6,2)
,"-",substr(DATE_ADD(person.birth, INTERVAL +18 month) ,1,4)+543) ,"---",
CONCAT(substr(DATE_ADD(person.birth, INTERVAL + 19 month) -1 ,7,2),"-",
substr(DATE_ADD(person.birth, INTERVAL + 19 month) -1 ,5,2),
"-",substr(DATE_ADD(person.birth, INTERVAL + 19 month) -1 ,1,4)+543)) '18m',
concat(CONCAT(substr(DATE_ADD(person.birth, INTERVAL +30 month) ,9,2),"-",
substr(DATE_ADD(person.birth, INTERVAL +30 month) ,6,2)
,"-",substr(DATE_ADD(person.birth, INTERVAL +30 month) ,1,4)+543) ,"---",
CONCAT(substr(DATE_ADD(person.birth, INTERVAL +31 month) -1 ,7,2),"-",
substr(DATE_ADD(person.birth, INTERVAL + 31 month) -1 ,5,2),
"-",substr(DATE_ADD(person.birth, INTERVAL + 31 month) -1 ,1,4)+543)) '30m',
substr(DATE_ADD(person.birth, INTERVAL +30 month) ,6,2)
,"-",substr(DATE_ADD(person.birth, INTERVAL +30 month) ,1,4)+543) ,"---",
CONCAT(substr(DATE_ADD(person.birth, INTERVAL +31 month) -1 ,7,2),"-",
substr(DATE_ADD(person.birth, INTERVAL + 31 month) -1 ,5,2),
"-",substr(DATE_ADD(person.birth, INTERVAL + 31 month) -1 ,1,4)+543)) '30m',
concat(CONCAT(substr(DATE_ADD(person.birth, INTERVAL +42 month) ,9,2),"-",
substr(DATE_ADD(person.birth, INTERVAL +42 month) ,6,2)
,"-",substr(DATE_ADD(person.birth, INTERVAL +42 month) ,1,4)+543) ,"---",
CONCAT(substr(DATE_ADD(person.birth, INTERVAL + 43 month) -1 ,7,2),"-",
substr(DATE_ADD(person.birth, INTERVAL + 43 month) -1 ,5,2),
"-",substr(DATE_ADD(person.birth, INTERVAL + 43 month) -1 ,1,4)+543)) '42m'
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.pid not in (select persondeath.pid from persondeath)
and person.typelive in ('1','3') and SUBSTRING(house.villcode,7,2)<> '00'
and GetAgemonth(person.birth,current_date())<= '43'
group by person.pid
order by pbirth asc
อัปเดท 270759
===========================================================
1. สำรวจการได้รับวัคซีน
2. สำรวจการอยู่อาศัย
3. แก้ไขแล้ว ส่ง 43 แฟ้ม และติ๊ก epi all มาด้วย
======================
select
person.pid as pid,
concat(ctitle.titlename,person.fname,' ',person.lname)as pname,
person.birth as pbirth,
GetAgeymd(person.birth,current_date())as age,
person.idcard as pcid,
person.sex as sex,
person.hnomoi as hno,
village.villno as village_villno,
max(case when visitepi.vaccinecode ='BCG' then GetAgeymd (person.birth,visitepi.dateepi) else '' end) as 'bcg',
max(case when visitepi.vaccinecode ='HBV1' then GetAgeymd (person.birth,visitepi.dateepi) else '' end) as 'hbv1',
max(case when visitepi.vaccinecode ='DHB1' then GetAgeymd (person.birth,visitepi.dateepi) else '' end) as 'dhb1',
max(case when visitepi.vaccinecode ='OPV1' then GetAgeymd (person.birth,visitepi.dateepi) else '' end) as 'OPV1',
max(case when visitepi.vaccinecode ='DHB2' then GetAgeymd (person.birth,visitepi.dateepi) else '' end) as 'dhb2',
max(case when visitepi.vaccinecode ='OPV2' then GetAgeymd (person.birth,visitepi.dateepi) else '' end) as 'OPV2',
max(case when visitepi.vaccinecode ='DHB3' then GetAgeymd (person.birth,visitepi.dateepi) else '' end) as 'dhb3',
max(case when visitepi.vaccinecode ='OPV3' then GetAgeymd (person.birth,visitepi.dateepi) else '' end) as 'OPV3',
max(case when visitepi.vaccinecode ='MMR' then GetAgeymd (person.birth,visitepi.dateepi) else '' end) as 'mmr'
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 visitepi on person.pid = visitepi.pid
where
person.pid not in (select persondeath.pid from persondeath) and GetAgeyearnum(person.birth,'2015-10-01''1'
and person.typelive in ('1','3')
and nation='99'
group by person.pid
order by age,village.villno,person.hnomoi*1
อัปเดท 270759
=======================================================
สำรวจ ประชากร 1+3 เพื่อเป็นเป้าหมายในการทำงาน ปี 60
=====================================
select person.pcucodeperson,
person.pid as pid,
ctitle.titlecode,person.fname,person.lname,
person.birth as pbirth,
GetAgeymd(person.birth,current_date())as age,
person.idcard as pcid,
person.sex as sex,
person.hnomoi as hno,
village.villno as village_villno,
person.nation,person.typelive,
v.communityname as 'ชุมชน',
concat(ps.fname,' ',ps.lname)as 'อสม.'
from
person inner join ctitle on person.prename = ctitle.titlecode
inner join house on person.pcucodeperson = house.pcucode and person.hcode = house.hcode
left join person as ps on house.pidvola=ps.pid and house.pcucodepersonvola=ps.pcucodeperson
inner join village on house.pcucode = village.pcucode and village.villcode = house.villcode
left join villagecommunity v on house.villcode = v.villCode and house.pcucode = v.pcucode and house.communityno = v.communityno
where
person.pid not in (select persondeath.pid from persondeath)
and person.typelive in ('1','3')
group by person.pid
order by age,village.villno,person.hnomoi*1
=====================================
select person.pcucodeperson,
person.pid as pid,
ctitle.titlecode,person.fname,person.lname,
person.birth as pbirth,
GetAgeymd(person.birth,current_date())as age,
person.idcard as pcid,
person.sex as sex,
person.hnomoi as hno,
village.villno as village_villno,
person.nation,person.typelive,
v.communityname as 'ชุมชน',
concat(ps.fname,' ',ps.lname)as 'อสม.'
from
person inner join ctitle on person.prename = ctitle.titlecode
inner join house on person.pcucodeperson = house.pcucode and person.hcode = house.hcode
left join person as ps on house.pidvola=ps.pid and house.pcucodepersonvola=ps.pcucodeperson
inner join village on house.pcucode = village.pcucode and village.villcode = house.villcode
left join villagecommunity v on house.villcode = v.villCode and house.pcucode = v.pcucode and house.communityno = v.communityno
where
person.pid not in (select persondeath.pid from persondeath)
and person.typelive in ('1','3')
group by person.pid
order by age,village.villno,person.hnomoi*1
===================================================
ไม่มีความคิดเห็น:
แสดงความคิดเห็น