Scrip SQL For JHCIS

รายชื่อ ปชช. อายุ 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
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
จำนวนคัดกรองและการตีค่าความเสี่ยง 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_date between '2013-10-01' and '2014-11-30'
and SUBSTRING(ncd_person.village,7,2)<> '00'
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

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'
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 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  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 '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;

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;


Non-NCD (รายชื่อคนที่ยังไม่ได้คัดกรอง) ปี2557
=================================================================================
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 
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


จำนวนคัดกรอง 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 'ปี' 

จำนวนผู้ป่วยโรคเรื้อรัง จำแนกตามเพศและกลุ่มอายุ

=================================================================================
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

ถ้าต้องการแค่โรค HT DM ให้เพิ่ม Code and cdiseasechronic.groupcode in ('01','10')
ต่อจาก 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

จำนวนผู้พิการแยกตามลักษณะความพิการ

=================================================================================
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

คัดกรองมะเร็งเต้านมและมะเร็งปากมดลูก

=================================================================================
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 'ปี' ;

จำนวนการให้หัตถการ

=================================================================================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;

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)

จำนวนผู้พิการแยกรายหมู่ 

=================================================================================
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;

พฤติกรรมสุขภาพ 

=================================================================================
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;

ตรวจสอบการคัดกรอง 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;

ตรวจสอบการให้รหัสสมุนไพรที่ไม่ขึ้นต้นด้วย 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.fname,' ',person.lname,' ','(',convert(person.pid using utf8),')')as 'ชื่อ - สกุล',
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';


0 Comment to "Scrip SQL For JHCIS"

แสดงความคิดเห็น