รายชื่อ ปชช. อายุ 35 ปี ขึ้นไป ที่ยังไม่ได้รับการคัดกรองเบาหวาน - ความดัน
=============================================================================
SELECT
person.pid as 'HN',person.fname as 'ชื่อ',...person.lname as 'นามสกุล',person.hnomoi as 'บ้านเลขที่',person.mumoi as 'หมู่ที่',
#person.typelive#,
getAgeYearNum (birth,CURDATE()) as 'อายุ'
FROM
person
INNER JOIN ncdpersonscreenall ON person.pcucodeperson = ncdpersonscreenall.pcucode AND person.pid = ncdpersonscreenall.pid
WHERE
person.typelive IN ( 1,3 )
AND person.pid NOT IN (select persondeath.pid from persondeath)
AND getAgeYearNum (birth,CURDATE()) >= '35'
AND person.pid NOT IN (select personchronic.pid from personchronic)
AND ncdpersonscreenall.visitno NOT BETWEEN '20130401' AND '2014331'
GROUP BY
person.pid
person.pid as 'HN',person.fname as 'ชื่อ',...person.lname as 'นามสกุล',person.hnomoi as 'บ้านเลขที่',person.mumoi as 'หมู่ที่',
#person.typelive#,
getAgeYearNum (birth,CURDATE()) as 'อายุ'
FROM
person
INNER JOIN ncdpersonscreenall ON person.pcucodeperson = ncdpersonscreenall.pcucode AND person.pid = ncdpersonscreenall.pid
WHERE
person.typelive IN ( 1,3 )
AND person.pid NOT IN (select persondeath.pid from persondeath)
AND getAgeYearNum (birth,CURDATE()) >= '35'
AND person.pid NOT IN (select personchronic.pid from personchronic)
AND ncdpersonscreenall.visitno NOT BETWEEN '20130401' AND '2014331'
GROUP BY
person.pid
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 '2013-10-01' AND '2014-03-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 '2013-10-01' AND '2014-03-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
==============================================================================
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 'Grage',
case when ncd_person.sex = '1' then 'ชาย'when ncd_person.sex = '2' then 'หญิง'else'ไม่ระบุ'end as grsex,
sum(CASE WHEN age_year >= '15' THEN 1 ELSE 0 END) AS 'จำนวนคัดกรอง',
sum(CASE WHEN age_year between '15' and '34' and chronic_flag='0' and (screen_q1 + screen_q2 + screen_q3 + (blackarmpit='1') )>='3' THEN 1 ELSE 0 END) AS 'verbal<15ปี',
sum(CASE WHEN age_year > = '35' and chronic_flag='0' and (screen_q1 + screen_q2 + screen_q3 + screen_q4 + screen_q5 + screen_q6 )>='1' THEN 1 ELSE 0 END) AS 'verbal>35ปี',
sum(CASE WHEN chronic_flag='0' and bsl> '0' THEN 1 ELSE 0 END) AS 'ได้รับการเจาะDTX',
sum(CASE WHEN chronic_flag='0' and bsl < '100' THEN 1 ELSE 0 END) AS 'bsl<100',
sum(CASE WHEN chronic_flag='0' and bsl between '100' and '125' THEN 1 ELSE 0 END) AS 'bsl100-125',sum(CASE WHEN chronic_flag='0' and bsl >= '126' THEN 1 ELSE 0 END) AS 'bsl>126',
sum(CASE WHEN chronic_flag='0' and ((hbp_s2 between '120' and '139') or (hbp_d2 between '80' and '89')) THEN 1 ELSE 0 END) AS 'BP120/80-139/89',
sum(CASE WHEN chronic_flag='0' and (hbp_s2 >= '140' and hbp_d2 >= '90') THEN 1 ELSE 0 END) AS 'BP>140/90',
sum(CASE WHEN chronic_flag='0' and (hbp_s1 < '120' and hbp_d1 < '80') THEN 1 ELSE 0 END) AS 'BP<120/80',
sum(CASE WHEN ncd_person.sex = '1' and chronic_flag='0' and ncd_person_ncd_screen.waist >= '90' THEN 1 ELSE 0 END) AS 'รอบเอวเกินชาย',
sum(CASE WHEN ncd_person.sex = '2' and chronic_flag='0' and ncd_person_ncd_screen.waist >= '80' THEN 1 ELSE 0 END) AS 'รอบเอวเกินหญิง',
sum(CASE WHEN chronic_flag='0' and ncd_person_ncd_screen.bmi >= '25' THEN 1 ELSE 0 END) AS 'BMI>25'
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 ncd_person_ncd ON ncd_person_ncd_screen.pcucode = ncd_person_ncd.pcucode AND ncd_person_ncd_screen.pid = ncd_person_ncd.pid
WHERE ncd_person_ncd_screen.screen_d
and SUBSTRING(ncd_person.village,7
group by Grage,grsex
case when ncd_person.sex = '1' then 'ชาย'when ncd_person.sex = '2' then 'หญิง'else'ไม่ระบุ'end as grsex,
sum(CASE WHEN age_year >= '15' THEN 1 ELSE 0 END) AS 'จำนวนคัดกรอง',
sum(CASE WHEN age_year between '15' and '34' and chronic_flag='0' and (screen_q1 + screen_q2 + screen_q3 + (blackarmpit='1') )>='3' THEN 1 ELSE 0 END) AS 'verbal<15ปี',
sum(CASE WHEN age_year > = '35' and chronic_flag='0' and (screen_q1 + screen_q2 + screen_q3 + screen_q4 + screen_q5 + screen_q6 )>='1' THEN 1 ELSE 0 END) AS 'verbal>35ปี',
sum(CASE WHEN chronic_flag='0' and bsl> '0' THEN 1 ELSE 0 END) AS 'ได้รับการเจาะDTX',
sum(CASE WHEN chronic_flag='0' and bsl < '100' THEN 1 ELSE 0 END) AS 'bsl<100',
sum(CASE WHEN chronic_flag='0' and bsl between '100' and '125' THEN 1 ELSE 0 END) AS 'bsl100-125',sum(CASE WHEN chronic_flag='0' and bsl >= '126' THEN 1 ELSE 0 END) AS 'bsl>126',
sum(CASE WHEN chronic_flag='0' and ((hbp_s2 between '120' and '139') or (hbp_d2 between '80' and '89')) THEN 1 ELSE 0 END) AS 'BP120/80-139/89',
sum(CASE WHEN chronic_flag='0' and (hbp_s2 >= '140' and hbp_d2 >= '90') THEN 1 ELSE 0 END) AS 'BP>140/90',
sum(CASE WHEN chronic_flag='0' and (hbp_s1 < '120' and hbp_d1 < '80') THEN 1 ELSE 0 END) AS 'BP<120/80',
sum(CASE WHEN ncd_person.sex = '1' and chronic_flag='0' and ncd_person_ncd_screen.waist >= '90' THEN 1 ELSE 0 END) AS 'รอบเอวเกินชาย',
sum(CASE WHEN ncd_person.sex = '2' and chronic_flag='0' and ncd_person_ncd_screen.waist >= '80' THEN 1 ELSE 0 END) AS 'รอบเอวเกินหญิง',
sum(CASE WHEN chronic_flag='0' and ncd_person_ncd_screen.bmi >= '25' THEN 1 ELSE 0 END) AS 'BMI>25'
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 ncd_person_ncd ON ncd_person_ncd_screen.pcucode = ncd_person_ncd.pcucode AND ncd_person_ncd_screen.pid = ncd_person_ncd.pid
WHERE ncd_person_ncd_screen.screen_d
and SUBSTRING(ncd_person.village,7
group by Grage,grsex
Code จำนวนคัดกรองแยก verbal แยกกลุ่มสี แยกเพศ แยกอายุ
Code จำนวนคัดกรอง แยกตาม verbal 6 ข้อ แยกกลุ่มสี แยกเพศ แยกอายุ
มีเงื่อนไขคือ ผู้ที่อายุ 15 ปีขึ้นไป (อายุ ณ วันที่ 1 ตุลาคม 2556)ที่มีชีวิตทุกคน (ถ้าคุณเอากลุ่มป่วยมาบันทึกก็จะนับไปด้วย) และมี Typearea 1+3 ที่ได้รับการคัดกรอง 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 เป้าหมายคัดกรองแยกเพศ แยกอายุ
จำนวนกลุ่มเป้าหมายคัดกรอง NCD แยกเพศ และกลุ่มอายุ มีเงื่อนไขคือ ประชากรที่อายุ 15 ปีขึ้นไป (อายุ ณ วันที่ 1 ตุลาคม 2556) ทุกคนที่ยังมีชีวิต และมี Typearea 1+3=============================================================================SELECT
CASE WHEN person.sex = '1' THEN 'ชาย'WHEN person.sex = '2' THEN 'หญิง'ELSE'ไม่ระบุ'END AS groupsex,
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',
SUM(CASE WHEN person.typelive in ('1','3') AND GetAgeYearNum(person.birth,'2013-10-01') >= 60 THEN 1 ELSE '' END) as '>60'
FROMperson
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 CONCAT(person.pid,person.pcucodeperson) not in (SELECT CONCAT(persondeath.pid,persondeath.pcucodeperson)
FROM persondeath) and SUBSTRING(house.villcode,7,2) !='00'
group by groupsex
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);
==============================================================================
อายุ 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);
(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
left join cdrug on visitdrug.drugcode = cdrug.drugcode
select drugcode ,drugname,drugcode24,drugtype
select
จำนวนคัดกรอง 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
=================================================================================
0 Comment to "Scrip SQL For JHCIS"
แสดงความคิดเห็น