ข้อมูลหญิงวัยเจริญพันธุ์อายุ 15 - 49 ปี ที่อยู่กินกับสามี
===================================================
SELECT convert(concat('หมู่ ',village.villno,':',village.villname) using utf8 ) as หมู่บ้าน,
SUM(case when person.sex = 2 and person.marystatus = 2 THEN 1 ELSE 0 end) as 'หญิงอายุ 15 - 49 ปี ที่มีคู่'
FROM
house
INNER JOIN person ON person.pcucodeperson = house.pcucode AND person.hcode = house.hcode
LEFT JOIN women ON women.pid = person.pid AND women.pcucodeperson = person.pcucodeperson
INNER JOIN village ON house.pcucode = village.pcucode AND house.villcode = village.villcode
WHERE
RIGHT(village.villcode,2) <> 00 AND
person.typelive IN (1, 3) AND
person.dischargetype = 9 AND
person.sex = 2 AND
person.marystatus = 2 AND
getAgeYearNum(birth,'20141001')BETWEEN '15' and '49'
GROUP BY
village.villname
UNION
SELECT
'รวม',
SUM(case when person.sex = 2 and person.marystatus = 2 THEN 1 ELSE 0 end) as 'หญิงอายุ 15 - 49 ปี ที่มีคู่'
FROM
house
INNER JOIN person ON person.pcucodeperson = house.pcucode AND person.hcode = house.hcode
LEFT JOIN women ON women.pid = person.pid AND women.pcucodeperson = person.pcucodeperson
INNER JOIN village ON house.pcucode = village.pcucode AND house.villcode = village.villcode
WHERE
RIGHT(village.villcode,2) <> 00 AND
person.typelive IN (1, 3) AND
person.dischargetype = 9 AND
person.sex = 2 AND
person.marystatus = 2 AND
getAgeYearNum(birth,'20141001')BETWEEN '15' and '49'
เครดิต Son Sky JHCIS_Pranburi
*****************************************************************
ประชากรจำแนกตามกลุ่มวัย
=============================
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
เครดิต Bee Bie สสอ.เมืองลำปาง
*************************************************************************
รายชื่อ ปชช. อายุ 35 ปี ขึ้นไป ที่ยังไม่ได้รับการคัดกรองเบาหวาน - ความดันโลหิตสูง
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,
person.pid
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
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,
person.pid
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
*******************************************************************************************
ผลงานการคัดกรองโรคเรื้อรังความดันโลหิตสูง เบาหวาน
SELECT person.pid AS HN
,CONCAT_WS('-',substring(idcard,1,1),substring(idcard,2,4),substring(idcard,6,5),substring(idcard,11,2),substring(idcard,13,1))AS เลขบัตรประชาชน
,concat(ctitle.titlename,' ',person.fname,' ',person.lname) as ชื่อสุกล
,CONCAT(DATE_FORMAT(person.birth,'%d-%m-'),DATE_FORMAT(person.birth,'%Y')+543) AS วันเดือนปีเกิด
,YEAR( FROM_DAYS( DATEDIFF( NOW( ) ,person.birth ) ) ) AS อายุ
,person.hnomoi AS บ้านเลขที่
,person.mumoi AS หมู่
,IFNULL(CONCAT(DATE_FORMAT(screen58.screen_date,'%d-%m-'),DATE_FORMAT(screen58.screen_date,'%Y')+543),'') AS ผลงาน
FROM person
INNER JOIN ctitle ON person.prename = ctitle.titlecode
LEFT JOIN persondeath ON person.pcucodeperson = persondeath.pcucodeperson AND person.pid = persondeath.pid
LEFT JOIN house ON house.pcucode = person.pcucodeperson AND house.hcode = person.hcode
LEFT JOIN village ON village.pcucode = house.pcucode AND village.villcode = house.villcode
LEFT JOIN (SELECT ncd_person_ncd_screen.pcucode
,ncd_person_ncd_screen.pid
,ncd_person_ncd_screen.screen_date
FROM ncd_person_ncd_screen
WHERE ncd_person_ncd_screen.screen_date BETWEEN '2014-10-01' AND '2015-09-30') AS screen58
ON person.pcucodeperson = screen58.pcucode AND person.pid = screen58.pid
WHERE persondeath.pid IS NULL
AND (YEAR(person.birth)+543) <= '2522'
AND person.typelive NOT IN ('2','4')
ORDER BY village.villcode,person.hnomoi*1 ASC;
==========================================================
code ตอบตัวชี้วัด QOF เรื่อง ร้อยละของหญิงตั้งครรภ์ได้รับการฝากครรภ์ครบ 5 ครั้งตามเกณฑ์
==================================================================
==================================================================
SELECT p.pid,
concat(p.prename,' ',p.fname,' ',p.lname) as personname,
p.idcard,timestampdiff(year,p.birth,vp.lmp) AS age,
p.hnomoi,p.villno AS หมู่ ,vp.lmp,vp.edc,t.datedeliver AS วันคลอด,t.pregno,
t1.pregage AS ANC1_p_age,t1.datecheck AS ANC1_d_check,
t2.pregage AS ANC2_p_gag,t2.datecheck AS ANC2_d_check,
t3.pregage AS ANC3_p_gag,t3.datecheck AS ANC3_d_check,
t4.pregage AS ANC4_p_gag,t4.datecheck AS ANC4_d_check,
t5.pregage AS ANC5_p_gag,t5.datecheck AS ANC5_d_check
FROM
(SELECT * FROM visitancdeliver v WHERE v.datedeliver BETWEEN '2014-10-01' AND '2014-12-31' ORDER BY v.datedeliver ) t
LEFT JOIN visitancpregnancy vp ON vp.pcucodeperson=t.pcucodeperson AND vp.pid=t.pid AND vp.pregno=t.pregno
INNER JOIN (SELECT ps.*,village.villno FROM person ps
INNER JOIN house ON ps.pcucodeperson = house.pcucode AND ps.hcode = house.hcode
INNER JOIN village ON house.pcucode = village.pcucode AND house.villcode = village.villcode where right(house.villcode,2)!='00') p ON p.pid=t.pid AND p.pcucodeperson=t.pcucodeperson
LEFT JOIN (SELECT * FROM visitanc WHERE pregage<=12 GROUP BY pid,pregno) t1 ON t1.pid=t.pid AND t1.pregno=t.pregno
LEFT JOIN (SELECT * FROM visitanc WHERE pregage BETWEEN 16 AND 20 GROUP BY pid,pregno) t2 ON t2.pid=t.pid AND t2.pregno=t.pregno
LEFT JOIN (SELECT * FROM visitanc WHERE pregage BETWEEN 24 AND 28 GROUP BY pid,pregno) t3 ON t3.pid=t.pid AND t3.pregno=t.pregno
LEFT JOIN (SELECT * FROM visitanc WHERE pregage BETWEEN 30 AND 34 GROUP BY pid,pregno) t4 ON t4.pid=t.pid AND t4.pregno=t.pregno
LEFT JOIN (SELECT * FROM visitanc WHERE pregage BETWEEN 36 AND 40 GROUP BY pid,pregno) t5 ON t5.pid=t.pid AND t5.pregno=t.pregno
concat(p.prename,' ',p.fname,' ',p.lname) as personname,
p.idcard,timestampdiff(year,p.birth,vp.lmp) AS age,
p.hnomoi,p.villno AS หมู่ ,vp.lmp,vp.edc,t.datedeliver AS วันคลอด,t.pregno,
t1.pregage AS ANC1_p_age,t1.datecheck AS ANC1_d_check,
t2.pregage AS ANC2_p_gag,t2.datecheck AS ANC2_d_check,
t3.pregage AS ANC3_p_gag,t3.datecheck AS ANC3_d_check,
t4.pregage AS ANC4_p_gag,t4.datecheck AS ANC4_d_check,
t5.pregage AS ANC5_p_gag,t5.datecheck AS ANC5_d_check
FROM
(SELECT * FROM visitancdeliver v WHERE v.datedeliver BETWEEN '2014-10-01' AND '2014-12-31' ORDER BY v.datedeliver ) t
LEFT JOIN visitancpregnancy vp ON vp.pcucodeperson=t.pcucodeperson AND vp.pid=t.pid AND vp.pregno=t.pregno
INNER JOIN (SELECT ps.*,village.villno FROM person ps
INNER JOIN house ON ps.pcucodeperson = house.pcucode AND ps.hcode = house.hcode
INNER JOIN village ON house.pcucode = village.pcucode AND house.villcode = village.villcode where right(house.villcode,2)!='00') p ON p.pid=t.pid AND p.pcucodeperson=t.pcucodeperson
LEFT JOIN (SELECT * FROM visitanc WHERE pregage<=12 GROUP BY pid,pregno) t1 ON t1.pid=t.pid AND t1.pregno=t.pregno
LEFT JOIN (SELECT * FROM visitanc WHERE pregage BETWEEN 16 AND 20 GROUP BY pid,pregno) t2 ON t2.pid=t.pid AND t2.pregno=t.pregno
LEFT JOIN (SELECT * FROM visitanc WHERE pregage BETWEEN 24 AND 28 GROUP BY pid,pregno) t3 ON t3.pid=t.pid AND t3.pregno=t.pregno
LEFT JOIN (SELECT * FROM visitanc WHERE pregage BETWEEN 30 AND 34 GROUP BY pid,pregno) t4 ON t4.pid=t.pid AND t4.pregno=t.pregno
LEFT JOIN (SELECT * FROM visitanc WHERE pregage BETWEEN 36 AND 40 GROUP BY pid,pregno) t5 ON t5.pid=t.pid AND t5.pregno=t.pregno
********************************************************************
จำนวนคัดกรองและการตีค่าความเสี่ยง verbal
===========================================================
************************************************************************************
Code จำนวนคัดกรองแยก verbal แยกกลุ่มสี แยกเพศ แยกอายุ
============================================================
============================================================
Code จำนวนคัดกรอง แยกตาม verbal 6 ข้อ แยกกลุ่มสี แยกเพศ แยกอายุ
มีเงื่อนไขคือ ผู้ที่อายุ 15 ปีขึ้นไปที่มีชีวิตทุกคน (ถ้าคุณเอากลุ่มป่วยมาบันทึกก็จะนับไปด้วย)ที่ได้รับการคัดกรอง NCD ตั้งแต่วันที่ 1 ตุลาคม 2556 ถึงวันที่ 30 กันยายน 2557
----------------------------------------------------------------------------------------------------------------------
SELECT CASE WHEN person.sex = '1' THEN 'ชาย'WHEN person.sex = '2' THEN 'หญิง'ELSE'ไม่ระบุ'END AS groupsex,
CASE WHEN age_year between '15' and '34' THEN '15-34 'WHEN age_year between '35' and '59' THEN '35-39'WHEN age_year >='60' THEN '>60'ELSE'ไม่ระบุ'END AS grouppop,
count(ncd_person_ncd_screen.pid) as 'จำนวนคัดกรอง' ,
sum(CASE WHEN screen_q1 = '1' THEN '1' ELSE null END) AS 'ญาติเบาหวาน',
sum(CASE WHEN screen_q2 = '1' THEN '1' ELSE null END) AS 'รอบเอว+BMIเกิน',
sum(CASE WHEN screen_q3 = '1' THEN '1' ELSE null END) AS 'BP>140/90',
sum(CASE WHEN screen_q4 = '1' THEN '1' ELSE null END) AS 'ประวัติน้ำตาลสูง',
sum(CASE WHEN screen_q5 = '1' THEN '1' ELSE null END) AS 'Tri>250',sum(CASE WHEN screen_q6 = '1' THEN '1' ELSE null END) AS 'เบาหวานขณะตั้งครรภ์',
sum(CASE WHEN blackarmpit = '1' THEN '1' ELSE null END) AS 'รอยพับดำ',
sum(CASE WHEN htfamily = '1' THEN '1' ELSE null END) AS 'ญาติBPสูง',
sum(CASE WHEN ((hbp_s1 <'120' and hbp_d1 <'80')OR (hbp_s2 <'120' and hbp_d2 <'80')) THEN '1' ELSE null END) AS 'HTขาว',
sum(CASE WHEN ((hbp_s2 between '120' and '139') or (hbp_d2 between '80' and '89')) THEN '1' ELSE null END) AS 'HTเขียว',sum(CASE WHEN bsl < '100' THEN '1' ELSE null END) AS 'DMขาว',
sum(CASE WHEN bsl between '100' and '125' THEN '1' ELSE null END) AS 'DMเขียว'
FROM
ncd_person_ncd_screen INNER JOIN ncd_person ON ncd_person_ncd_screen.pcucode = ncd_person.pcucode AND ncd_person_ncd_screen.pid = ncd_person.pid
INNER JOIN person ON ncd_person_ncd_screen.pcucode = person.pcucodeperson AND ncd_person_ncd_screen.pid = person.pid
WHERE
ncd_person_ncd_screen.screen_date between '2013-10-01' and '2014-09-30'
and SUBSTRING(ncd_person.village,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)
group by grouppop ,groupsex
CASE WHEN age_year between '15' and '34' THEN '15-34 'WHEN age_year between '35' and '59' THEN '35-39'WHEN age_year >='60' THEN '>60'ELSE'ไม่ระบุ'END AS grouppop,
count(ncd_person_ncd_screen.pid) as 'จำนวนคัดกรอง' ,
sum(CASE WHEN screen_q1 = '1' THEN '1' ELSE null END) AS 'ญาติเบาหวาน',
sum(CASE WHEN screen_q2 = '1' THEN '1' ELSE null END) AS 'รอบเอว+BMIเกิน',
sum(CASE WHEN screen_q3 = '1' THEN '1' ELSE null END) AS 'BP>140/90',
sum(CASE WHEN screen_q4 = '1' THEN '1' ELSE null END) AS 'ประวัติน้ำตาลสูง',
sum(CASE WHEN screen_q5 = '1' THEN '1' ELSE null END) AS 'Tri>250',sum(CASE WHEN screen_q6 = '1' THEN '1' ELSE null END) AS 'เบาหวานขณะตั้งครรภ์',
sum(CASE WHEN blackarmpit = '1' THEN '1' ELSE null END) AS 'รอยพับดำ',
sum(CASE WHEN htfamily = '1' THEN '1' ELSE null END) AS 'ญาติBPสูง',
sum(CASE WHEN ((hbp_s1 <'120' and hbp_d1 <'80')OR (hbp_s2 <'120' and hbp_d2 <'80')) THEN '1' ELSE null END) AS 'HTขาว',
sum(CASE WHEN ((hbp_s2 between '120' and '139') or (hbp_d2 between '80' and '89')) THEN '1' ELSE null END) AS 'HTเขียว',sum(CASE WHEN bsl < '100' THEN '1' ELSE null END) AS 'DMขาว',
sum(CASE WHEN bsl between '100' and '125' THEN '1' ELSE null END) AS 'DMเขียว'
FROM
ncd_person_ncd_screen INNER JOIN ncd_person ON ncd_person_ncd_screen.pcucode = ncd_person.pcucode AND ncd_person_ncd_screen.pid = ncd_person.pid
INNER JOIN person ON ncd_person_ncd_screen.pcucode = person.pcucodeperson AND ncd_person_ncd_screen.pid = person.pid
WHERE
ncd_person_ncd_screen.screen_date between '2013-10-01' and '2014-09-30'
and SUBSTRING(ncd_person.village,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)
group by grouppop ,groupsex
Code เป้าหมายคัดกรองแยกเพศ แยกอายุ
=======================================================
=======================================================
*******************************************************************************
Code จำนวนคัดกรอง แยกกลุ่มสี อายุ 15 - 34 ปี
==============================================================
==============================================================
SELECT
(SELECT
SUM(CASE WHEN person.typelive in ('1','3') AND GetAgeYearNum(person.birth,'2013-10-01') between '15' and '34' THEN 1 ELSE '' END)
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
WHERE (person.dischargetype is null or person.dischargetype = 9) and SUBSTRING(house.villcode,7,2) !='00'
and concat(person.pid,person.pcucodeperson) not in (select concat(personchronic.pid, personchronic.pcucodeperson) from personchronicwhere personchronic.chroniccode between 'I10' and 'I15.9' and personchronic.chroniccode between 'E10' and 'E15')) as 'Pop 15 ปี',
count(ncd_person_ncd_screen.pid) as 'จำนวนคัดกรอง' ,
sum(CASE WHEN (hbp_s1 <'120' and hbp_d1 <'80')OR (hbp_s2 <'120' and hbp_d2 <'80') THEN '1' ELSE null END) AS 'HTขาว',
sum(CASE WHEN (hbp_s2 between '120' and '139') or (hbp_d2 between '80' and '89') THEN '1' ELSE null END) AS 'HTเขียว',
sum(CASE WHEN bsl < '100' THEN '1' ELSE null END) AS 'DMขาว',
sum(CASE WHEN bsl between '100' and '125' THEN '1' ELSE null END) AS 'DMเขียว'
FROM
ncd_person_ncd_screen INNER JOIN ncd_person ON ncd_person_ncd_screen.pcucode = ncd_person.pcucode AND ncd_person_ncd_screen.pid = ncd_person.pid
INNER JOIN person ON ncd_person_ncd_screen.pcucode = person.pcucodeperson AND ncd_person_ncd_screen.pid = person.pid
WHERE
age_year between '15' and '34' and ncd_person_ncd_screen.screen_date between '2013-10-01' and '2014-09-30'
and SUBSTRING(ncd_person.village,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);
SELECT
(SELECT
SUM(CASE WHEN person.typelive in ('1','3') AND GetAgeYearNum(person.birth,'2013-10-01') between '35' and '59' THEN 1 ELSE '' END)
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
WHERE (person.dischargetype is null or person.dischargetype = 9) and SUBSTRING(house.villcode,7,2) !='00'
and concat(person.pid,person.pcucodeperson) not in (select concat(personchronic.pid, personchronic.pcucodeperson) from personchronic
where personchronic.chroniccode between 'I10' and 'I15.9' and personchronic.chroniccode between 'E10' and 'E15')) as 'Pop 15 ปี',
count(ncd_person_ncd_screen.pid) as 'จำนวนคัดกรอง' ,
sum(CASE WHEN (hbp_s1 <'120' and hbp_d1 <'80')OR (hbp_s2 <'120' and hbp_d2 <'80') THEN '1' ELSE null END) AS 'HTขาว',
sum(CASE WHEN (hbp_s2 between '120' and '139') or (hbp_d2 between '80' and '89') THEN '1' ELSE null END) AS 'HTเขียว',
sum(CASE WHEN bsl < '100' THEN '1' ELSE null END) AS 'DMขาว',
sum(CASE WHEN bsl between '100' and '125' THEN '1' ELSE null END) AS 'DMเขียว'
FROM
ncd_person_ncd_screen INNER JOIN ncd_person ON ncd_person_ncd_screen.pcucode = ncd_person.pcucode AND ncd_person_ncd_screen.pid = ncd_person.pid
INNER JOIN person ON ncd_person_ncd_screen.pcucode = person.pcucodeperson AND ncd_person_ncd_screen.pid = person.pid
WHERE
age_year between '35' and '59' and ncd_person_ncd_screen.screen_date between '2013-10-01' and '2014-09-30'
and SUBSTRING(ncd_person.village,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);
SELECT
(SELECT
SUM(CASE WHEN person.typelive in ('1','3') AND GetAgeYearNum(person.birth,'2013-10-01') >= '60' THEN 1 ELSE '' END)
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
WHERE (person.dischargetype is null or person.dischargetype = 9) and SUBSTRING(house.villcode,7,2) !='00'
and concat(person.pid,person.pcucodeperson) not in (select concat(personchronic.pid, personchronic.pcucodeperson) from personchronic
where personchronic.chroniccode between 'I10' and 'I15.9' and personchronic.chroniccode between 'E10' and 'E15')) as 'Pop 15 ปี',
count(ncd_person_ncd_screen.pid) as 'จำนวนคัดกรอง' ,
sum(CASE WHEN (hbp_s1 <'120' and hbp_d1 <'80')OR (hbp_s2 <'120' and hbp_d2 <'80') THEN '1' ELSE null END) AS 'HTขาว',
sum(CASE WHEN (hbp_s2 between '120' and '139') or (hbp_d2 between '80' and '89') THEN '1' ELSE null END) AS 'HTเขียว',
sum(CASE WHEN bsl < '100' THEN '1' ELSE null END) AS 'DMขาว',
sum(CASE WHEN bsl between '100' and '125' THEN '1' ELSE null END) AS 'DMเขียว'
FROM
ncd_person_ncd_screen INNER JOIN ncd_person ON ncd_person_ncd_screen.pcucode = ncd_person.pcucode AND ncd_person_ncd_screen.pid = ncd_person.pid
INNER JOIN person ON ncd_person_ncd_screen.pcucode = person.pcucodeperson AND ncd_person_ncd_screen.pid = person.pid
WHERE
age_year >= '60' and ncd_person_ncd_screen.screen_date between '2013-10-01' and '2014-09-30'
and SUBSTRING(ncd_person.village,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);
(SELECT
SUM(CASE WHEN person.typelive in ('1','3') AND GetAgeYearNum(person.birth,'2013-10-01') between '15' and '34' THEN 1 ELSE '' END)
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
WHERE (person.dischargetype is null or person.dischargetype = 9) and SUBSTRING(house.villcode,7,2) !='00'
and concat(person.pid,person.pcucodeperson) not in (select concat(personchronic.pid, personchronic.pcucodeperson) from personchronicwhere personchronic.chroniccode between 'I10' and 'I15.9' and personchronic.chroniccode between 'E10' and 'E15')) as 'Pop 15 ปี',
count(ncd_person_ncd_screen.pid) as 'จำนวนคัดกรอง' ,
sum(CASE WHEN (hbp_s1 <'120' and hbp_d1 <'80')OR (hbp_s2 <'120' and hbp_d2 <'80') THEN '1' ELSE null END) AS 'HTขาว',
sum(CASE WHEN (hbp_s2 between '120' and '139') or (hbp_d2 between '80' and '89') THEN '1' ELSE null END) AS 'HTเขียว',
sum(CASE WHEN bsl < '100' THEN '1' ELSE null END) AS 'DMขาว',
sum(CASE WHEN bsl between '100' and '125' THEN '1' ELSE null END) AS 'DMเขียว'
FROM
ncd_person_ncd_screen INNER JOIN ncd_person ON ncd_person_ncd_screen.pcucode = ncd_person.pcucode AND ncd_person_ncd_screen.pid = ncd_person.pid
INNER JOIN person ON ncd_person_ncd_screen.pcucode = person.pcucodeperson AND ncd_person_ncd_screen.pid = person.pid
WHERE
age_year between '15' and '34' and ncd_person_ncd_screen.screen_date between '2013-10-01' and '2014-09-30'
and SUBSTRING(ncd_person.village,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);
---------------------------------------------------------------------------------------------------------------
อายุ 35 - 59 ปีSELECT
(SELECT
SUM(CASE WHEN person.typelive in ('1','3') AND GetAgeYearNum(person.birth,'2013-10-01') between '35' and '59' THEN 1 ELSE '' END)
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
WHERE (person.dischargetype is null or person.dischargetype = 9) and SUBSTRING(house.villcode,7,2) !='00'
and concat(person.pid,person.pcucodeperson) not in (select concat(personchronic.pid, personchronic.pcucodeperson) from personchronic
where personchronic.chroniccode between 'I10' and 'I15.9' and personchronic.chroniccode between 'E10' and 'E15')) as 'Pop 15 ปี',
count(ncd_person_ncd_screen.pid) as 'จำนวนคัดกรอง' ,
sum(CASE WHEN (hbp_s1 <'120' and hbp_d1 <'80')OR (hbp_s2 <'120' and hbp_d2 <'80') THEN '1' ELSE null END) AS 'HTขาว',
sum(CASE WHEN (hbp_s2 between '120' and '139') or (hbp_d2 between '80' and '89') THEN '1' ELSE null END) AS 'HTเขียว',
sum(CASE WHEN bsl < '100' THEN '1' ELSE null END) AS 'DMขาว',
sum(CASE WHEN bsl between '100' and '125' THEN '1' ELSE null END) AS 'DMเขียว'
FROM
ncd_person_ncd_screen INNER JOIN ncd_person ON ncd_person_ncd_screen.pcucode = ncd_person.pcucode AND ncd_person_ncd_screen.pid = ncd_person.pid
INNER JOIN person ON ncd_person_ncd_screen.pcucode = person.pcucodeperson AND ncd_person_ncd_screen.pid = person.pid
WHERE
age_year between '35' and '59' and ncd_person_ncd_screen.screen_date between '2013-10-01' and '2014-09-30'
and SUBSTRING(ncd_person.village,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);
----------------------------------------------------------------------------------------------------------------
อายุ 60 ปีขึ้นไปSELECT
(SELECT
SUM(CASE WHEN person.typelive in ('1','3') AND GetAgeYearNum(person.birth,'2013-10-01') >= '60' THEN 1 ELSE '' END)
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
WHERE (person.dischargetype is null or person.dischargetype = 9) and SUBSTRING(house.villcode,7,2) !='00'
and concat(person.pid,person.pcucodeperson) not in (select concat(personchronic.pid, personchronic.pcucodeperson) from personchronic
where personchronic.chroniccode between 'I10' and 'I15.9' and personchronic.chroniccode between 'E10' and 'E15')) as 'Pop 15 ปี',
count(ncd_person_ncd_screen.pid) as 'จำนวนคัดกรอง' ,
sum(CASE WHEN (hbp_s1 <'120' and hbp_d1 <'80')OR (hbp_s2 <'120' and hbp_d2 <'80') THEN '1' ELSE null END) AS 'HTขาว',
sum(CASE WHEN (hbp_s2 between '120' and '139') or (hbp_d2 between '80' and '89') THEN '1' ELSE null END) AS 'HTเขียว',
sum(CASE WHEN bsl < '100' THEN '1' ELSE null END) AS 'DMขาว',
sum(CASE WHEN bsl between '100' and '125' THEN '1' ELSE null END) AS 'DMเขียว'
FROM
ncd_person_ncd_screen INNER JOIN ncd_person ON ncd_person_ncd_screen.pcucode = ncd_person.pcucode AND ncd_person_ncd_screen.pid = ncd_person.pid
INNER JOIN person ON ncd_person_ncd_screen.pcucode = person.pcucodeperson AND ncd_person_ncd_screen.pid = person.pid
WHERE
age_year >= '60' and ncd_person_ncd_screen.screen_date between '2013-10-01' and '2014-09-30'
and SUBSTRING(ncd_person.village,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);
Code 7 Colour กลุ่มคัดกรอง
Code รายชื่อกลุ่มคัดกรอง แยกตามกลุ่มสี เปลี่ยนวันที่ต้องการ [ between 'วันที่เริ่มต้น' and 'วันที่สิ้นสุด' ]
=============================================================
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_s2,hbp_d1,hbp_d2,
DATE_FORMAT(screen_date,'%Y-%m-%d') as screen_date,
max(CASE WHEN (hbp_s1 <'120' and hbp_d1 <'80')OR (hbp_s2 <'120' and hbp_d2 <'80') THEN "/" ELSE null END) AS 'NBp',
max(CASE WHEN (hbp_s2 between '120' and '139') or (hbp_d2 between '80' and '89') THEN "/" ELSE null END) AS 'HBp', max(CASE WHEN (hbp_s2 >= '140' or hbp_d2 >= '90') THEN "/" ELSE null END) AS 'RoHT',
bsl,
max(CASE WHEN bsl < '100' THEN "/" ELSE null END) AS 'NDM',
max(CASE WHEN bsl between '100' and '125' THEN "/" ELSE null END) AS 'HDM',
max(CASE WHEN bsl >= '126' THEN "/" ELSE null END) AS 'RoDM'
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 >= '15' and ncd_person_ncd_screen.screen_date between '2013-10-01' and '2014-09-30' 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;
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_s2,hbp_d1,hbp_d2,
DATE_FORMAT(screen_date,'%Y-%m-%d') as screen_date,
max(CASE WHEN (hbp_s1 <'120' and hbp_d1 <'80')OR (hbp_s2 <'120' and hbp_d2 <'80') THEN "/" ELSE null END) AS 'NBp',
max(CASE WHEN (hbp_s2 between '120' and '139') or (hbp_d2 between '80' and '89') THEN "/" ELSE null END) AS 'HBp', max(CASE WHEN (hbp_s2 >= '140' or hbp_d2 >= '90') THEN "/" ELSE null END) AS 'RoHT',
bsl,
max(CASE WHEN bsl < '100' THEN "/" ELSE null END) AS 'NDM',
max(CASE WHEN bsl between '100' and '125' THEN "/" ELSE null END) AS 'HDM',
max(CASE WHEN bsl >= '126' THEN "/" ELSE null END) AS 'RoDM'
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 >= '15' and ncd_person_ncd_screen.screen_date between '2013-10-01' and '2014-09-30' 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;
********************************************************************************
Code 7 colour กลุ่มป่วย
=========================================================
select
concat(ctitle.titlename,ps.fname,' ',ps.lname)as pname,
ps.birth as birth,
ps.sex as sex,
ps.idcard as idcard,
ps.hnomoi,
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' 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 '2013-10-30'
and (pc.chroniccode between 'E10' and 'E15' 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 = '06139' and SUBSTRING(house.villcode,7,2) = '09'
group by ps.pid;
concat(ctitle.titlename,ps.fname,' ',ps.lname)as pname,
ps.birth as birth,
ps.sex as sex,
ps.idcard as idcard,
ps.hnomoi,
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' 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 '2013-10-30'
and (pc.chroniccode between 'E10' and 'E15' 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 = '06139' and SUBSTRING(house.villcode,7,2) = '09'
group by ps.pid;
---------------------------------------------------------------------------------------------------
Non-NCD (รายชื่อคนที่ยังไม่ได้คัดกรอง)
=========================================================
SELECT n.pid,p.pid, c.titlename ,p.fname, p.lname, p.sex, p.idcard,n.age_year, p.hnomoi, p.mumoi,GetAgeYearNum(p.birth,'2013-12-19') as 'ayear', n.height, n.weight, n.waist, n.hbp_s1, n.hbp_d1, n.screen_date, n.bmi, n.d_update
FROM person p
inner join ctitle c on p.prename=c.titlecode
INNER JOIN house h ON p.pcucodeperson = h.pcucode AND p.hcode = h.hcode
INNER JOIN village v ON h.pcucode = v.pcucode AND v.villcode = h.villcode
left join ncd_person_ncd_screen n on p.pid=n.pid
where
FROM person p
inner join ctitle c on p.prename=c.titlecode
INNER JOIN house h ON p.pcucodeperson = h.pcucode AND p.hcode = h.hcode
INNER JOIN village v ON h.pcucode = v.pcucode AND v.villcode = h.villcode
left join ncd_person_ncd_screen n on p.pid=n.pid
where
SUBSTRING(h.villcode,7,2) !='00' and p.typelive in ('1','3') AND GetAgeYearNum(p.birth,'2013-07-01') >= '15' AND n.pid is null or n.screen_date < '2013-07-01';
*****************************************************************************************
Code พฤติกรรมสุขภาพ จากการคัดกรอง ncdscreen
=================================================================
select case when age_year between '15' and '34' then 'อายุ15_34'
when age_year between '35' and '59' then 'อายุ35_59'
when age_year >= '60' then 'อายุ>60'else null end as 'Grpage',
sum(case when alcohol = 1 then 1 else 0 end) as 'ไม่ดื่ม',
sum(case when alcohol = 2 then 1 else 0 end) as 'ดื่มนานๆครั้ง',
sum(case when alcohol = 3 then 1 else 0 end) as 'ดื่มเป็นครั้งคราว',
sum(case when alcohol = 4 then 1 else 0 end) as 'ดื่มเป็นประจำ',
sum(case when alcohol = 9 then 1 else 0 end) as 'ไม่ทราบ',
sum(case when smoke = 1 then 1 else 0 end) as 'ไม่สูบ',
sum(case when smoke = 2 then 1 else 0 end) as 'สูบนานๆครั้ง',
sum(case when smoke = 3 then 1 else 0 end) as 'สูบเป็นครั้งคราว',
sum(case when smoke = 4 then 1 else 0 end) as 'สูบประจำ',
sum(case when smoke = 9 then 1 else 0 end) as 'ไม่ทราบ'
from ncd_person_ncd_screen
where age_year >= '15' and concat(ncd_person_ncd_screen.pid,ncd_person_ncd_screen.pcucode)
not in (select concat(persondeath.pid,persondeath.pcucodeperson) from persondeath
where persondeath.pcucodeperson=ncd_person_ncd_screen.pcucode and (persondeath.deaddate is null or persondeath.deaddate<=curdate())) and screen_date between '2013-10-01' and '2014-09-30'
group by Grpage
when age_year between '35' and '59' then 'อายุ35_59'
when age_year >= '60' then 'อายุ>60'else null end as 'Grpage',
sum(case when alcohol = 1 then 1 else 0 end) as 'ไม่ดื่ม',
sum(case when alcohol = 2 then 1 else 0 end) as 'ดื่มนานๆครั้ง',
sum(case when alcohol = 3 then 1 else 0 end) as 'ดื่มเป็นครั้งคราว',
sum(case when alcohol = 4 then 1 else 0 end) as 'ดื่มเป็นประจำ',
sum(case when alcohol = 9 then 1 else 0 end) as 'ไม่ทราบ',
sum(case when smoke = 1 then 1 else 0 end) as 'ไม่สูบ',
sum(case when smoke = 2 then 1 else 0 end) as 'สูบนานๆครั้ง',
sum(case when smoke = 3 then 1 else 0 end) as 'สูบเป็นครั้งคราว',
sum(case when smoke = 4 then 1 else 0 end) as 'สูบประจำ',
sum(case when smoke = 9 then 1 else 0 end) as 'ไม่ทราบ'
from ncd_person_ncd_screen
where age_year >= '15' and concat(ncd_person_ncd_screen.pid,ncd_person_ncd_screen.pcucode)
not in (select concat(persondeath.pid,persondeath.pcucodeperson) from persondeath
where persondeath.pcucodeperson=ncd_person_ncd_screen.pcucode and (persondeath.deaddate is null or persondeath.deaddate<=curdate())) and screen_date between '2013-10-01' and '2014-09-30'
group by Grpage
*******************************************************************************
จำนวนคัดกรอง 2Q แยกรายเดือน
==========================================================
SELECT case when month(visit.visitdate)=1 then 'มกราคม'
when month(visit.visitdate)=2 then 'กุมภาพันธ์'
when month(visit.visitdate)=3 then 'มีนาคม'
when month(visit.visitdate)=4 then 'เมษายน'
when month(visit.visitdate)=5 then 'พฤษภาคม'
when month(visit.visitdate)=6 then 'มิถุนายน'
when month(visit.visitdate)=7 then 'กรกฎาคม'
when month(visit.visitdate)=8 then 'สิงหาคม'
when month(visit.visitdate)=9 then 'กันยายน'
when month(visit.visitdate)=10 then 'ตุลาคม'
when month(visit.visitdate)=11 then 'พฤศจิกายน'
when month(visit.visitdate)=12 then 'ธันวาคม' else null end as 'GMonth',
year(visit.visitdate) as 'ปี',
count(codescreen = 'c01') as 'total',
sum(case when GetAgeYearNum(person.birth,visit.visitdate) between '15' and '29' then 1 else '' end) as '2q15-29',
sum(case when GetAgeYearNum(person.birth,visit.visitdate) between '15' and '29'and visitscreenspecialdisease.coderesult = '2' then 1 else '' end) as 'ผิดปกติ15-29',
sum(case when GetAgeYearNum(person.birth,visit.visitdate) between '30' and '49' then 1 else '' end) as '2q30-49',
sum(case when GetAgeYearNum(person.birth,visit.visitdate) between '30' and '49'and visitscreenspecialdisease.coderesult = '2' then 1 else '' end) as 'ผิดปกติ30-49',
sum(case when GetAgeYearNum(person.birth,visit.visitdate) between '50' and '59' then 1 else '' end) as '2q50-59',
sum(case when GetAgeYearNum(person.birth,visit.visitdate) between '50' and '59'and visitscreenspecialdisease.coderesult = '2' then 1 else '' end) as 'ผิดปกติ50-59'
FROM visit INNER JOIN visitscreenspecialdisease ON visit.pcucode = visitscreenspecialdisease.pcucode AND visit.visitno = visitscreenspecialdisease.visitno
INNER JOIN person ON visit.pcucode = person.pcucodeperson AND visit.pid = person.pid
where visit.visitdate between '2012-10-01' and '2014-09-30'
and concat(person.pid,person.pcucodeperson)not in (select concat(persondeath.pid,persondeath.pcucodeperson)
from persondeath where deaddate<=visit.visitdate or deaddate is not null)
and codescreen = 'c01' and person.typelive in ('1','3')#and person.sex = '2'
group by GMonth
order by 'ปี'
when month(visit.visitdate)=2 then 'กุมภาพันธ์'
when month(visit.visitdate)=3 then 'มีนาคม'
when month(visit.visitdate)=4 then 'เมษายน'
when month(visit.visitdate)=5 then 'พฤษภาคม'
when month(visit.visitdate)=6 then 'มิถุนายน'
when month(visit.visitdate)=7 then 'กรกฎาคม'
when month(visit.visitdate)=8 then 'สิงหาคม'
when month(visit.visitdate)=9 then 'กันยายน'
when month(visit.visitdate)=10 then 'ตุลาคม'
when month(visit.visitdate)=11 then 'พฤศจิกายน'
when month(visit.visitdate)=12 then 'ธันวาคม' else null end as 'GMonth',
year(visit.visitdate) as 'ปี',
count(codescreen = 'c01') as 'total',
sum(case when GetAgeYearNum(person.birth,visit.visitdate) between '15' and '29' then 1 else '' end) as '2q15-29',
sum(case when GetAgeYearNum(person.birth,visit.visitdate) between '15' and '29'and visitscreenspecialdisease.coderesult = '2' then 1 else '' end) as 'ผิดปกติ15-29',
sum(case when GetAgeYearNum(person.birth,visit.visitdate) between '30' and '49' then 1 else '' end) as '2q30-49',
sum(case when GetAgeYearNum(person.birth,visit.visitdate) between '30' and '49'and visitscreenspecialdisease.coderesult = '2' then 1 else '' end) as 'ผิดปกติ30-49',
sum(case when GetAgeYearNum(person.birth,visit.visitdate) between '50' and '59' then 1 else '' end) as '2q50-59',
sum(case when GetAgeYearNum(person.birth,visit.visitdate) between '50' and '59'and visitscreenspecialdisease.coderesult = '2' then 1 else '' end) as 'ผิดปกติ50-59'
FROM visit INNER JOIN visitscreenspecialdisease ON visit.pcucode = visitscreenspecialdisease.pcucode AND visit.visitno = visitscreenspecialdisease.visitno
INNER JOIN person ON visit.pcucode = person.pcucodeperson AND visit.pid = person.pid
where visit.visitdate between '2012-10-01' and '2014-09-30'
and concat(person.pid,person.pcucodeperson)not in (select concat(persondeath.pid,persondeath.pcucodeperson)
from persondeath where deaddate<=visit.visitdate or deaddate is not null)
and codescreen = 'c01' and person.typelive in ('1','3')#and person.sex = '2'
group by GMonth
order by 'ปี'
*********************************************************************************
จำนวนผู้ป่วยโรคเรื้อรัง จำแนกตามเพศและกลุ่มอายุ
==================================================================
select cdiseasechronic.groupname as grname,
case when person.sex = '1' then 'ชาย'when person.sex = '2' then 'หญิง'else'ไม่ระบุ'end as grsex,
sum(case when GetAgeYearNum(person.birth,'2013-10-01') <15 and person.typelive in ('1','3') then 1 else '' end) as '<15ปี',
sum(case when GetAgeYearNum(person.birth,'2013-10-01') between 15 and 34 and person.typelive in ('1','3') then 1 else '' end) as '15-34ปี',
sum(case when GetAgeYearNum(person.birth,'2013-10-01') between 35 and 59 and person.typelive in ('1','3') then 1 else '' end) as '35-59ปี',
sum(case when GetAgeYearNum(person.birth,'2013-10-01') >= 60 and person.typelive in ('1','3') then 1 else '' end) as '>60ปี'
from
personchronic inner join person on personchronic.pcucodeperson = person.pcucodeperson
and personchronic.pid = person.pid
inner join cdisease on personchronic.chroniccode = cdisease.diseasecode
inner join cdiseasechronic on cdisease.codechronic = cdiseasechronic.groupcode
where concat(person.pid,person.pcucodeperson)not in (select concat(persondeath.pid,persondeath.pcucodeperson)
from persondeath where deaddate<=curdate() or deaddate is not null)
group by grname,grsex
case when person.sex = '1' then 'ชาย'when person.sex = '2' then 'หญิง'else'ไม่ระบุ'end as grsex,
sum(case when GetAgeYearNum(person.birth,'2013-10-01') <15 and person.typelive in ('1','3') then 1 else '' end) as '<15ปี',
sum(case when GetAgeYearNum(person.birth,'2013-10-01') between 15 and 34 and person.typelive in ('1','3') then 1 else '' end) as '15-34ปี',
sum(case when GetAgeYearNum(person.birth,'2013-10-01') between 35 and 59 and person.typelive in ('1','3') then 1 else '' end) as '35-59ปี',
sum(case when GetAgeYearNum(person.birth,'2013-10-01') >= 60 and person.typelive in ('1','3') then 1 else '' end) as '>60ปี'
from
personchronic inner join person on personchronic.pcucodeperson = person.pcucodeperson
and personchronic.pid = person.pid
inner join cdisease on personchronic.chroniccode = cdisease.diseasecode
inner join cdiseasechronic on cdisease.codechronic = cdiseasechronic.groupcode
where concat(person.pid,person.pcucodeperson)not in (select concat(persondeath.pid,persondeath.pcucodeperson)
from persondeath where deaddate<=curdate() or deaddate is not null)
group by grname,grsex
### ถ้าต้องการแค่โรค HT DM ให้เพิ่ม Code and cdiseasechronic.groupcode in ('01','10')
ต่อจาก or deaddate is not null) บรรทัดรองสุดท้าย ###
ต่อจาก or deaddate is not null) บรรทัดรองสุดท้าย ###
******************************************************************************
จำนวนประชากร 1+3 และ กลุ่มอายุตาม KPI57 แยกรายหมู่
===================================================================
select house.villcode as villcode
,count(person.pid) as 'total'
,sum(case when person.typelive='1' then 1 else 0 end) as '1'
,sum(case when person.typelive='2' then 1 else 0 end) as '2'
,sum(case when person.typelive='3' then 1 else 0 end) as '3'
,sum(case when person.typelive='4' then 1 else 0 end) as '4'
,sum(case when person.typelive in ('1','3') then 1 else 0 end) as '1+3'
,sum(case when GetAgeYearNum(person.birth,'2013-10-01') < 5 and person.typelive in ('1','3') THEN 1 ELSE '' END) AS '0-5ปี'
,sum(case when GetAgeYearNum(person.birth,'2013-10-01') between 5 and 14 and person.typelive in ('1','3') THEN 1 ELSE '' END) AS '5-14ปี'
,sum(case when GetAgeYearNum(person.birth,'2013-10-01') between 15 and 21 and person.typelive in ('1','3') THEN 1 ELSE '' END) AS '15-21ปี'
,sum(case when GetAgeYearNum(person.birth,'2013-10-01') between 22 and 59 and person.typelive in ('1','3') THEN 1 ELSE '' END) AS '22-59ปี'
,sum(case when GetAgeYearNum(person.birth,'2013-10-01') >= 60 and person.typelive in ('1','3') THEN 1 ELSE '' END) AS '60ปี'
from person inner join house on person.pcucodeperson = house.pcucode and person.hcode = house.hcode
where concat(person.pid,person.pcucodeperson)not in (select concat(persondeath.pid,persondeath.pcucodeperson)
from persondeath where deaddate<=curdate() or deaddate is not null)
group by villcode
,count(person.pid) as 'total'
,sum(case when person.typelive='1' then 1 else 0 end) as '1'
,sum(case when person.typelive='2' then 1 else 0 end) as '2'
,sum(case when person.typelive='3' then 1 else 0 end) as '3'
,sum(case when person.typelive='4' then 1 else 0 end) as '4'
,sum(case when person.typelive in ('1','3') then 1 else 0 end) as '1+3'
,sum(case when GetAgeYearNum(person.birth,'2013-10-01') < 5 and person.typelive in ('1','3') THEN 1 ELSE '' END) AS '0-5ปี'
,sum(case when GetAgeYearNum(person.birth,'2013-10-01') between 5 and 14 and person.typelive in ('1','3') THEN 1 ELSE '' END) AS '5-14ปี'
,sum(case when GetAgeYearNum(person.birth,'2013-10-01') between 15 and 21 and person.typelive in ('1','3') THEN 1 ELSE '' END) AS '15-21ปี'
,sum(case when GetAgeYearNum(person.birth,'2013-10-01') between 22 and 59 and person.typelive in ('1','3') THEN 1 ELSE '' END) AS '22-59ปี'
,sum(case when GetAgeYearNum(person.birth,'2013-10-01') >= 60 and person.typelive in ('1','3') THEN 1 ELSE '' END) AS '60ปี'
from person inner join house on person.pcucodeperson = house.pcucode and person.hcode = house.hcode
where concat(person.pid,person.pcucodeperson)not in (select concat(persondeath.pid,persondeath.pcucodeperson)
from persondeath where deaddate<=curdate() or deaddate is not null)
group by villcode
****************************************************************************
จำนวนผู้พิการแยกตามลักษณะความพิการ
===================================================================
SELECT cpersonincomplete.incompletename as 'ลักษณะความพิการ',
count(person.pid) as 'พิการ'
FROM
person INNER JOIN personunable ON person.pcucodeperson = personunable.pcucodeperson
AND person.pid = personunable.pid
INNER JOIN personunable1type ON personunable.pcucodeperson = personunable1type.pcucodeperson
AND personunable.pid = personunable1type.pid
INNER JOIN cpersonincomplete ON personunable1type.typecode = cpersonincomplete.incompletecode
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
AND person.pid = personunable.pid
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 incompletename
count(person.pid) as 'พิการ'
FROM
person INNER JOIN personunable ON person.pcucodeperson = personunable.pcucodeperson
AND person.pid = personunable.pid
INNER JOIN personunable1type ON personunable.pcucodeperson = personunable1type.pcucodeperson
AND personunable.pid = personunable1type.pid
INNER JOIN cpersonincomplete ON personunable1type.typecode = cpersonincomplete.incompletecode
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
AND person.pid = personunable.pid
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 incompletename
************************************************************************
คัดกรองมะเร็งเต้านมและมะเร็งปากมดลูก
===============================================================
SELECT
case when month(visitlabcancer.datecheck)=1 then 'มกราคม'
when month(visitlabcancer.datecheck)=2 then 'กุมภาพันธ์'
when month(visitlabcancer.datecheck)=3 then 'มีนาคม'
when month(visitlabcancer.datecheck)=4 then 'เมษายน'
when month(visitlabcancer.datecheck)=5 then 'พฤษภาคม'
when month(visitlabcancer.datecheck)=6 then 'มิถุนายน'
when month(visitlabcancer.datecheck)=7 then 'กรกฎาคม'
when month(visitlabcancer.datecheck)=8 then 'สิงหาคม'
when month(visitlabcancer.datecheck)=9 then 'กันยายน'
when month(visitlabcancer.datecheck)=10 then 'ตุลาคม'
when month(visitlabcancer.datecheck)=11 then 'พฤศจิกายน'
when month(visitlabcancer.datecheck)=12 then 'ธันวาคม' else null end as 'GroupMonth',
year(visitlabcancer.datecheck) as 'ปี',
sum(case when month(visitlabcancer.datecheck) and visitlabcancer.typecancer= '1' then 1 else null end) as 'คัดกรองเต้านม',
sum(case when month(visitlabcancer.datecheck) and visitlabcancer.typecancer= '1'and visitlabcancer.result = '1' then 1 else null end) as 'พบผิดปกติ',
sum(case when month(visitlabcancer.datecheck) and visitlabcancer.typecancer= '1'and visitlabcancer.result = '2' then 1 else null end) as 'พบเซลล์มะเร็ง',
sum(case when month(visitlabcancer.datecheck) and visitlabcancer.typecancer= '2' then 1 else null end) as 'Papsmear',
sum(case when month(visitlabcancer.datecheck) and visitlabcancer.typecancer= '2'and visitlabcancer.result = '1' then 1 else null end) as 'ผิดปกติ CAT II',
sum(case when month(visitlabcancer.datecheck) and visitlabcancer.typecancer= '2'and visitlabcancer.result = '2' then 1 else null end) as 'ผิดปกติ CAT III,IV',
sum(case when month(visitlabcancer.datecheck) and visitlabcancer.typecancer= '2'and visitlabcancer.result = '9' then 1 else null end) as 'ผิดปกติไม่ใช่มะเร็ง'
FROM
visitlabcancer INNER JOIN person ON visitlabcancer.pcucodeperson = person.pcucodeperson AND visitlabcancer.pid = person.pid
where getAgeYearNum(birth,visitlabcancer.datecheck) between '30' and '60'and sex = '2' and visitlabcancer.datecheck between '2013-10-01' and '2014-09-30'
and person.typelive in ('1','3') and person.dischargetype != '1'
group by GroupMonth
order by 'ปี' ;
case when month(visitlabcancer.datecheck)=1 then 'มกราคม'
when month(visitlabcancer.datecheck)=2 then 'กุมภาพันธ์'
when month(visitlabcancer.datecheck)=3 then 'มีนาคม'
when month(visitlabcancer.datecheck)=4 then 'เมษายน'
when month(visitlabcancer.datecheck)=5 then 'พฤษภาคม'
when month(visitlabcancer.datecheck)=6 then 'มิถุนายน'
when month(visitlabcancer.datecheck)=7 then 'กรกฎาคม'
when month(visitlabcancer.datecheck)=8 then 'สิงหาคม'
when month(visitlabcancer.datecheck)=9 then 'กันยายน'
when month(visitlabcancer.datecheck)=10 then 'ตุลาคม'
when month(visitlabcancer.datecheck)=11 then 'พฤศจิกายน'
when month(visitlabcancer.datecheck)=12 then 'ธันวาคม' else null end as 'GroupMonth',
year(visitlabcancer.datecheck) as 'ปี',
sum(case when month(visitlabcancer.datecheck) and visitlabcancer.typecancer= '1' then 1 else null end) as 'คัดกรองเต้านม',
sum(case when month(visitlabcancer.datecheck) and visitlabcancer.typecancer= '1'and visitlabcancer.result = '1' then 1 else null end) as 'พบผิดปกติ',
sum(case when month(visitlabcancer.datecheck) and visitlabcancer.typecancer= '1'and visitlabcancer.result = '2' then 1 else null end) as 'พบเซลล์มะเร็ง',
sum(case when month(visitlabcancer.datecheck) and visitlabcancer.typecancer= '2' then 1 else null end) as 'Papsmear',
sum(case when month(visitlabcancer.datecheck) and visitlabcancer.typecancer= '2'and visitlabcancer.result = '1' then 1 else null end) as 'ผิดปกติ CAT II',
sum(case when month(visitlabcancer.datecheck) and visitlabcancer.typecancer= '2'and visitlabcancer.result = '2' then 1 else null end) as 'ผิดปกติ CAT III,IV',
sum(case when month(visitlabcancer.datecheck) and visitlabcancer.typecancer= '2'and visitlabcancer.result = '9' then 1 else null end) as 'ผิดปกติไม่ใช่มะเร็ง'
FROM
visitlabcancer INNER JOIN person ON visitlabcancer.pcucodeperson = person.pcucodeperson AND visitlabcancer.pid = person.pid
where getAgeYearNum(birth,visitlabcancer.datecheck) between '30' and '60'and sex = '2' and visitlabcancer.datecheck between '2013-10-01' and '2014-09-30'
and person.typelive in ('1','3') and person.dischargetype != '1'
group by GroupMonth
order by 'ปี' ;
*************************************************************************************
จำนวนการให้หัตถการ
===========================================================
select drugname,count(distinct visit.pcucode,visit.pid) as 'คน' ,count(visitdrug.unit) as 'ครั้ง'
from
visit left join visitdrug on visit.visitno = visitdrug.visitno and visit.pcucode = visitdrug.pcucode
left join cdrug on visitdrug.drugcode = cdrug.drugcode
where cdrug.drugtype='02' and cdrug.drugtypesub = '3' and visit.visitdate between '2013-07-01' and '2014-10-31'
group by cdrug.drugcode
**********************************************************************
Code จำนวนกลุ่มเป้าหมายตามกลุ่มอายุ
====================================================================
SELECT house.villcode,
SUM(CASE WHEN person.typelive in ('1','3') AND ROUND(DATEDIFF('2013-10-01',person.birth)/30) <= 35.99 THEN 1 ELSE '' END) AS '0-2ปี',
SUM(CASE WHEN person.typelive in ('1','3') AND ROUND(DATEDIFF('2013-10-01',person.birth)/30) <= 47.99 THEN 1 ELSE '' END) AS '0-3ปี',
SUM(CASE WHEN person.typelive in ('1','3') AND ROUND(DATEDIFF('2013-10-01',person.birth)/30) BETWEEN 36 AND 71.99 THEN 1 ELSE '' END) AS '3-5ปี',
SUM(CASE WHEN person.typelive in ('1','3') AND ROUND(DATEDIFF('2013-10-01',person.birth)/30) BETWEEN 72 AND 143.99 THEN 1 ELSE '' END) AS '6-12ปี',
SUM(CASE WHEN person.typelive in ('1','3') AND GetAgeYearNum(person.birth,'2013-10-01') >= 15 THEN 1 ELSE '' END) AS '15ปีขึ้นไป',
SUM(CASE WHEN person.sex='2' AND person.typelive IN ('1','3') AND GetAgeYearNum(person.birth,'2013-10-01') BETWEEN 30 AND 59 THEN 1 ELSE '' END) AS 'หญิง30-60ปี',
SUM(CASE WHEN person.typelive in ('1','3') AND GetAgeYearNum(person.birth,'2013-10-01') >= 60 THEN 1 ELSE '' END) AS '60ปีขึ้นไป',
SUM(CASE WHEN person.typelive in ('1','3') AND GetAgeYearNum(person.birth,'2013-10-01') between 15 and 34 THEN 1 ELSE '' END) AS '15_34',
SUM(CASE WHEN person.typelive in ('1','3') AND GetAgeYearNum(person.birth,'2013-10-01') between 35 and 59 THEN 1 ELSE '' END) AS '35_59'
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
WHERE (person.dischargetype is null or person.dischargetype = 9) and SUBSTRING(house.villcode,7,2) !='00'
GROUP BY house.villcode;
SUM(CASE WHEN person.typelive in ('1','3') AND ROUND(DATEDIFF('2013-10-01',person.birth)/30) <= 35.99 THEN 1 ELSE '' END) AS '0-2ปี',
SUM(CASE WHEN person.typelive in ('1','3') AND ROUND(DATEDIFF('2013-10-01',person.birth)/30) <= 47.99 THEN 1 ELSE '' END) AS '0-3ปี',
SUM(CASE WHEN person.typelive in ('1','3') AND ROUND(DATEDIFF('2013-10-01',person.birth)/30) BETWEEN 36 AND 71.99 THEN 1 ELSE '' END) AS '3-5ปี',
SUM(CASE WHEN person.typelive in ('1','3') AND ROUND(DATEDIFF('2013-10-01',person.birth)/30) BETWEEN 72 AND 143.99 THEN 1 ELSE '' END) AS '6-12ปี',
SUM(CASE WHEN person.typelive in ('1','3') AND GetAgeYearNum(person.birth,'2013-10-01') >= 15 THEN 1 ELSE '' END) AS '15ปีขึ้นไป',
SUM(CASE WHEN person.sex='2' AND person.typelive IN ('1','3') AND GetAgeYearNum(person.birth,'2013-10-01') BETWEEN 30 AND 59 THEN 1 ELSE '' END) AS 'หญิง30-60ปี',
SUM(CASE WHEN person.typelive in ('1','3') AND GetAgeYearNum(person.birth,'2013-10-01') >= 60 THEN 1 ELSE '' END) AS '60ปีขึ้นไป',
SUM(CASE WHEN person.typelive in ('1','3') AND GetAgeYearNum(person.birth,'2013-10-01') between 15 and 34 THEN 1 ELSE '' END) AS '15_34',
SUM(CASE WHEN person.typelive in ('1','3') AND GetAgeYearNum(person.birth,'2013-10-01') between 35 and 59 THEN 1 ELSE '' END) AS '35_59'
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
WHERE (person.dischargetype is null or person.dischargetype = 9) and SUBSTRING(house.villcode,7,2) !='00'
GROUP BY house.villcode;
**************************************************************
Code ประชากรจำแนกตาม Typearea
==================================================================
select
sum(case when person.typelive='1' then 1 else 0 end) as '1=มีชื่อและอาศัยอยู่จริง'
,sum(case when person.typelive='2' then 1 else 0 end) as '2=มีชื่อฯแต่ไม่อยู่จริง'
,sum(case when person.typelive='3' then 1 else 0 end) as '3=ไม่มีชื่อฯแต่อยู่จริง'
,sum(case when person.typelive='4' then 1 else 0 end) as '4=คนนอกเขตมารับบริการ'
from person
inner join house on person.hcode=house.hcode and person.pcucodeperson=house.pcucode
and concat(person.pid,person.pcucodeperson)
not in (select concat(persondeath.pid,persondeath.pcucodeperson)
from persondeath
where deaddate<=curdate() or deaddate is not null)
sum(case when person.typelive='1' then 1 else 0 end) as '1=มีชื่อและอาศัยอยู่จริง'
,sum(case when person.typelive='2' then 1 else 0 end) as '2=มีชื่อฯแต่ไม่อยู่จริง'
,sum(case when person.typelive='3' then 1 else 0 end) as '3=ไม่มีชื่อฯแต่อยู่จริง'
,sum(case when person.typelive='4' then 1 else 0 end) as '4=คนนอกเขตมารับบริการ'
from person
inner join house on person.hcode=house.hcode and person.pcucodeperson=house.pcucode
and concat(person.pid,person.pcucodeperson)
not in (select concat(persondeath.pid,persondeath.pcucodeperson)
from persondeath
where deaddate<=curdate() or deaddate is not null)
************************************************************************************
จำนวนผู้พิการแยกรายหมู่
==============================================================
SELECT village.villcode,villname ,count(person.pid) as 'พิการ'
FROM
person INNER JOIN personunable ON person.pcucodeperson = personunable.pcucodeperson
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
AND person.pid = personunable.pid
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 village.villcode;
FROM
person INNER JOIN personunable ON person.pcucodeperson = personunable.pcucodeperson
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
AND person.pid = personunable.pid
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 village.villcode;
***************************************************************************************
พฤติกรรมสุขภาพ
==================================================================
select
village.villcode,villname
,sum(case when ciga = 0 then 1 else 0 end) as 'ไม่สูบ'
,sum(case when ciga = 1 then 1 else 0 end) as 'สูบนานๆครั้ง'
,sum(case when ciga = 2 then 1 else 0 end) as 'สูบประจำ'
,sum(case when wisky = 0 then 1 else 0 end) as 'ไม่ดื่ม'
,sum(case when wisky =1 then 1 else 0 end) as 'ดื่มนานๆครั้ง'
,sum(case when wisky = 2 then 1 else 0 end) as '1-2ครั้งต่อเดือน'
,sum(case when wisky = 3 then 1 else 0 end) as '1-2ครั้งต่อสัปดาห์'
,sum(case when wisky = 4 then 1 else 0 end) as '3-4ครั้งต่อสัปดาห์'
,sum(case when wisky = 5 then 1 else 0 end) as 'ดื่มทุกวัน'
,sum(case when exercise = 0 then 1 else 0 end) as 'ไม่ออกกำลังกาย'
,sum(case when exercise = 1 then 1 else 0 end) as 'ออก<3วัน'
,sum(case when exercise = 2 then 1 else 0 end) as 'ออก 3-5วัน'
,sum(case when exercise = 3 then 1 else 0 end) as 'ออก>5วัน'
from person left join personbehavior on person.pid = personbehavior.pid and person.pcucodeperson = personbehavior.pcucodeperson
left join house on person.hcode = house.hcode and person.pcucodeperson = house.pcucode
left join village on house.villcode = village.villcode and house.pcucode = village.pcucode
where substring(house.villcode,7,2) !='00'
and GetAgeYearNum(person.birth,curdate()) between '15' and '19'
and concat(person.pid,person.pcucodeperson) not in (select concat(persondeath.pid,persondeath.pcucodeperson) from persondeath where persondeath.pcucodeperson=person.pcucodeperson and (persondeath.deaddate is null or persondeath.deaddate<=curdate()))
group by village.villcode;
village.villcode,villname
,sum(case when ciga = 0 then 1 else 0 end) as 'ไม่สูบ'
,sum(case when ciga = 1 then 1 else 0 end) as 'สูบนานๆครั้ง'
,sum(case when ciga = 2 then 1 else 0 end) as 'สูบประจำ'
,sum(case when wisky = 0 then 1 else 0 end) as 'ไม่ดื่ม'
,sum(case when wisky =1 then 1 else 0 end) as 'ดื่มนานๆครั้ง'
,sum(case when wisky = 2 then 1 else 0 end) as '1-2ครั้งต่อเดือน'
,sum(case when wisky = 3 then 1 else 0 end) as '1-2ครั้งต่อสัปดาห์'
,sum(case when wisky = 4 then 1 else 0 end) as '3-4ครั้งต่อสัปดาห์'
,sum(case when wisky = 5 then 1 else 0 end) as 'ดื่มทุกวัน'
,sum(case when exercise = 0 then 1 else 0 end) as 'ไม่ออกกำลังกาย'
,sum(case when exercise = 1 then 1 else 0 end) as 'ออก<3วัน'
,sum(case when exercise = 2 then 1 else 0 end) as 'ออก 3-5วัน'
,sum(case when exercise = 3 then 1 else 0 end) as 'ออก>5วัน'
from person left join personbehavior on person.pid = personbehavior.pid and person.pcucodeperson = personbehavior.pcucodeperson
left join house on person.hcode = house.hcode and person.pcucodeperson = house.pcucode
left join village on house.villcode = village.villcode and house.pcucode = village.pcucode
where substring(house.villcode,7,2) !='00'
and GetAgeYearNum(person.birth,curdate()) between '15' and '19'
and concat(person.pid,person.pcucodeperson) not in (select concat(persondeath.pid,persondeath.pcucodeperson) from persondeath where persondeath.pcucodeperson=person.pcucodeperson and (persondeath.deaddate is null or persondeath.deaddate<=curdate()))
group by village.villcode;
***********************************************************************************************
ตรวจสอบการคัดกรอง NCD ในคนตาย และ Typearea ไม่ใช่ 1+3
================================================================
select
person.pid as pid,
concat(ctitle.titlename,person.fname,' ',person.lname)as pname,
age_year,
person.idcard as pcid,
person.typelive as 'สถานะการอยู่อาศัย',
case when person.dischargetype = '1' then 'ตาย' when person.dischargetype = '2'then 'ย้ายออกนอกเขต' when person.dischargetype = '3'then 'สุญหาย'
when person.dischargetype = '9'then 'ยังไม่จำหน่าย'else null end as 'สาเหตุจำหน่าย',
person.hnomoi as hno,
person.mumoi as mu,
DATE_FORMAT(screen_date,'%Y-%m-%d') as screen_date
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 >= '15' and ncd_person_ncd_screen.screen_date between '2013-10-01' and '2014-09-30' and (person.typelive not in ('1','3') or person.dischargetype = '1')
group by person.pid
order by ncd_person.village;
person.pid as pid,
concat(ctitle.titlename,person.fname,' ',person.lname)as pname,
age_year,
person.idcard as pcid,
person.typelive as 'สถานะการอยู่อาศัย',
case when person.dischargetype = '1' then 'ตาย' when person.dischargetype = '2'then 'ย้ายออกนอกเขต' when person.dischargetype = '3'then 'สุญหาย'
when person.dischargetype = '9'then 'ยังไม่จำหน่าย'else null end as 'สาเหตุจำหน่าย',
person.hnomoi as hno,
person.mumoi as mu,
DATE_FORMAT(screen_date,'%Y-%m-%d') as screen_date
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 >= '15' and ncd_person_ncd_screen.screen_date between '2013-10-01' and '2014-09-30' and (person.typelive not in ('1','3') or person.dischargetype = '1')
group by person.pid
order by ncd_person.village;
************************************************************************************************
ตรวจสอบการให้รหัสสมุนไพรที่ไม่ขึ้นต้นด้วย 41 และ 42
==============================================================
select drugcode ,drugname,drugcode24,drugtype
from cdrug
where drugtype = '10' and drugflag = '1'
and cdrug.drugcode24 not Like '41%' and cdrug.drugcode24 not Like '42%';
*****************************************************************************************
ตรวจสอบการจำหน่ายตายใน Chronic
================================================================
select
concat(ctitle.titlename,person
person.idcard as pidcard,
person.hnomoi as 'บ้านเลขที่',
person.mumoi as 'หมู่',
person.typelive as 'สถานะการอยู่อาศัย',
case when person.dischargetype = '1' then 'ตาย' when person.dischargetype = '2'then 'ย้ายออกนอกเขต' when person.dischargetype = '3'then 'สุญหาย'
when person.dischargetype = '9'then 'ยังไม่จำหน่าย'else null end AS 'สาเหตุจำหน่าย',
persondeath.deaddate as 'วันที่ตาย',
personchronic.chroniccode as 'รหัสเรื้อรัง',
personchronic.typedischart as 'สาเหตุจำหน่ายเรื้อรัง'
from
person inner join ctitle on person.prename = ctitle.titlecode
inner join personchronic on person.pcucodeperson = personchronic.pcucodeperson
and person.pid = personchronic.pid
inner join persondeath on person.pcucodeperson = persondeath.pcucodeperson
and person.pid = persondeath.pid
where personchronic.typedischart != '02';
***********************************************************************************************
ตรวจสอบรายชื่อเด็กอายุระหว่าง 6-7 ปี
==============================================================
SELECT
person.pid AS pid,
concat(ctitle.titlename,person.fname,'
',person.lname)as pname,
date_format(person.birth,"%Y-%m-%d") 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 GetAgeYearNum(person.birth,CURRENT_DATE)
>= '6'
and GetAgeYearNum(person.birth,CURRENT_DATE)
<= '7'
and SUBSTRING(house.villcode,7,2)<>'00'
order BY village.villno,person.hnomoi*1;
***********************************************************************************************
ผลงานการคัดกรองโรคเรื้อรังความดันโลหิตสูง เบาหวาน
,CONCAT_WS('-',substring(idcard,1,1),substring(idcard,2,4),substring(idcard,6,5),substring(idcard,11,2),substring(idcard,13,1))AS เลขบัตรประชาชน
,concat(ctitle.titlename,' ',person.fname,' ',person.lname) as ชื่อสุกล
,CONCAT(DATE_FORMAT(person.birth,'%d-%m-'),DATE_FORMAT(person.birth,'%Y')+543) AS วันเดือนปีเกิด
,YEAR( FROM_DAYS( DATEDIFF( NOW( ) ,person.birth ) ) ) AS อายุ
,person.hnomoi AS บ้านเลขที่
,person.mumoi AS หมู่
,IFNULL(CONCAT(DATE_FORMAT(screen58.screen_date,'%d-%m-'),DATE_FORMAT(screen58.screen_date,'%Y')+543),'') AS ผลงาน
FROM person
INNER JOIN ctitle ON person.prename = ctitle.titlecode
LEFT JOIN persondeath ON person.pcucodeperson = persondeath.pcucodeperson AND person.pid = persondeath.pid
LEFT JOIN house ON house.pcucode = person.pcucodeperson AND house.hcode = person.hcode
LEFT JOIN village ON village.pcucode = house.pcucode AND village.villcode = house.villcode
LEFT JOIN (SELECT ncd_person_ncd_screen.pcucode
,ncd_person_ncd_screen.pid
,ncd_person_ncd_screen.screen_date
FROM ncd_person_ncd_screen
WHERE ncd_person_ncd_screen.screen_date BETWEEN '2014-10-01' AND '2015-09-30') AS screen58
ON person.pcucodeperson = screen58.pcucode AND person.pid = screen58.pid
WHERE persondeath.pid IS NULL
AND (YEAR(person.birth)+543) <= '2522'
AND person.typelive NOT IN ('2','4')
ORDER BY village.villcode,person.hnomoi*1 ASC;
==========================================================
