1.นัดวัคซีนโควิด-19
..............................
SET @start = '2021-12-08' ;
SET @END = '2022-09-30';
select va.pid,ifnull(p.idcard,p.passpotnumber) as idcard,ifnull(ct.titlename,p.prenameeng) as titlename,ifnull(p.fname,p.fnameeng) as fname,ifnull(p.lname,p.lnameeng) as lname,ifnull(r.rightname,'ไม่ระบุ') as rightname,date_format(date_add(va.dateappoint, interval 543 year),"%e/%m/%Y") as dateappoint,va.userappoint,ct1.titlename as titlenameuser,u.fname as fnameuser,u.lname as lnameuser,va.hoscodeappoint,h.hosname,dt.distname,cp.provname,va.slothour,getageymd(p.birth,va.dateappoint) as age,if(va.vaccinecode = 'COVID19-1','เข็มที่ 1','เข็มที่ 2') as vaccine from visitepiappoint va left join person p on (va.pid = p.pid and va.pcucodeperson = p.pcucodeperson) left join cright r on (p.rightcode = r.rightcode) left join ctitle ct on (p.prename = ct.titlecode) left join user u on (va.userappoint = u.username) left join ctitle ct1 on (u.prename = ct1.titlecode) left join chospital h on (va.hoscodeappoint = h.hoscode) left join cdistrict dt on (h.distcode = dt.distcode and h.provcode = dt.provcode) left join cprovince cp on (h.provcode = cp.provcode) where va.vaccinecode in ('COVID19-1','COVID19-2') and va.dateappoint BETWEEN @start AND @END order by va.dateappoint;
............................
2.เป้าไข้หวัดใหญ่ (flu2565)
.......................................
SELECT v1.pcucode,p1.pid,v1.visitno,p1.titlename,p1.fname,p1.lname,p1.idcard,p1.age,v1.dateepi,p1.hno,p1.moo,v1.vaccinecode,v1.lotno,v1.datevacineexpire,v1.code FROM (SELECT p.pid,ct.titlename,p.fname,p.lname,p.idcard,getAgeYearNum(p.birth,CURDATE()) AS 'age',h.hno,RIGHT(vl.villcode,2) AS moo FROM person p INNER JOIN ctitle ct ON p.prename = ct.titlecode INNER JOIN house h ON p.pcucodeperson = h.pcucode AND p.hcode = h.hcode INNER JOIN village vl ON h.pcucode = vl.pcucode AND vl.villcode = h.villcode WHERE getAgeYearNum(p.birth,CURDATE() ) BETWEEN '60'AND '100' AND p.typelive IN ('1','3') AND p.dischargetype = '9') AS p1 LEFT JOIN (SELECT vpi.pcucode,vpi.pid,vpi.visitno,vpi.dateepi,vpi.vaccinecode,vpi.lotno,vpi.datevacineexpire,cdg.files18epi AS 'code' FROM visitepi vpi INNER JOIN cdrug cdg ON vpi.vaccinecode = cdg.drugcode WHERE vpi.dateepi BETWEEN '2021-04-01'AND CURDATE() AND cdg.files18epi = '815') AS v1 ON p1.pid = v1.pid
# WHERE v1.vaccinecode IS NULL # ยังไม่ฉีด
#WHERE v1.vaccinecode IS NOT NULL # ฉีดแล้ว
GROUP BY p1.pid ORDER BY p1.moo,p1.hno;
.........................................
หมายเหตุ : หากต้องการเฉพาะที่ยังไม่ฉีด ให้เอาเครื่องหมาย # ออกจาก หน้า WHERE v1.vaccinecode IS NULL # ยังไม่ฉีด (บรรทัดที่ 3 จากด้านล่าง)
...........................................................................@@@.............................................................................
PCC 1
1.หาผู้ที่ผ่านการคัดกรองมะเร็งปากมดลูก 30-60 ปี ตามนี้ >> หมายเหตุ: (เวลารัน = 10 วิ)
......................................................
SELECT p1.pid,p1.titlename,p1.fname,p1.lname,p1.hno,p1.moo,p1.idcard,birthx AS 'เกิด',Age,p1.rightname AS 'สิทธิการรักษา',v1.visitdate AS 'วันคัดกรอง',v1.methode AS 'วิธีตรวจ',v1.ppname AS 'ผลการตรวจ',v1.visitno,v1.ppspecial,v1.diag FROM (SELECT p.pid,p.idcard,ct.titlename,p.fname,p.lname,h.hno,RIGHT(h.villcode,2) AS 'moo',CONCAT(DAY(p.birth),'-',MONTH(p.birth),'-',YEAR(p.birth)+543)AS 'birthx',getAgeYearNum (p.birth ,CURDATE()) AS 'age',cr.rightname FROM person AS p INNER JOIN ctitle AS ct ON p.prename = ct.titlecode INNER JOIN house h ON p.hcode = h.hcode INNER JOIN cright cr ON p.rightcode = cr.rightcode WHERE p.sex = '2' AND getAgeYearNum (p.birth ,CURDATE()) BETWEEN '30' AND '59' AND p.typelive IN ('1','3') AND p.dischargetype = '9') AS p1 LEFT JOIN (SELECT vt.pcucode,vt.pid,vt.visitdate,cs.ppcode,CASE WHEN cs.ppcode LIKE '1B004%' THEN cs.ppcode ELSE 'papsmear' END AS 'methode',cs.ppname,vt.visitno,GROUP_CONCAT(fpp.ppspecial) AS 'ppspecial',GROUP_CONCAT(vi.diagcode) AS 'diag' FROM visit vt INNER JOIN f43specialpp AS fpp ON vt.pid = fpp.pid AND vt.visitno = fpp.visitno INNER JOIN visitdiag AS vi ON vt.visitno = vi.visitno LEFT JOIN cspecialpp cs ON fpp.ppspecial = cs.ppcode WHERE vt.visitdate BETWEEN '2017-07-01' AND '2022-06-30' AND SUBSTR(fpp.ppspecial,1,4) IN ('1B30', '1B40', '1B00') AND vi.diagcode IN ('z01.4', 'z12.4') AND cs.ppname REGEXP ('มะเร็งปากมดลูก') GROUP BY vt.pid) AS v1 ON p1.pid = v1.pid ORDER BY p1.moo ,p1.hno;
.......................................................
PCC 2
2.หาภาวะสุขภาพ คนในเขตรับผิดชอบ >> หมายเหตุ: (เวลารัน = 3 วิ)
......................................................
SELECT pp1.pid,pp1.titlename,pp1.fname,pp1.lname,pp1.hno,pp1.moo,pp1.y_birth AS'เกิด พ.ศ.',pp1.idcard,pp1.sx AS'เพศ',pp1.age AS'อายุปี',pp1.rightname as 'สิทธิการรักษา',LTRIM(CONCAT(if(pp1.DM IS NOT NULL ,pp1.DM,''),' ',if(pp1.HT IS NOT NULL ,pp1.HT,''),' ',if(pp1.N IS NOT NULL ,pp1.N,''),' ',if(pp1.TB IS NOT NULL ,pp1.TB,''),' ',if(pp1.stroke IS NOT NULL ,pp1.stroke,''),' ',if(h1.smoke IS NOT NULL ,h1.smoke,''),' ',if(h1.alcohol IS NOT NULL ,h1.alcohol,'สุขภาพดี'))) AS 'ภาวะสุขภาพ' FROM (SELECT p.pid,ct.titlename,p.fname,p.lname,p.idcard,CONCAT(DAY(p.birth),'-',MONTH(p.birth),'-',YEAR(p.birth)+543)AS'y_birth',IF(p.sex = 1,'ชาย','หญิง') AS 'sx',cr.rightname,getAgeYearNum(p.birth,CURDATE()) AS 'age',h.hno,RIGHT(h.villcode,2) AS'moo',pc1.DM,pc2.HT,pc3.N,pc4.TB,pc5.stroke FROM person p INNER JOIN ctitle ct ON p.prename = ct.titlecode INNER JOIN house h ON p.hcode = h.hcode INNER JOIN cright cr ON p.rightcode = cr.rightcode LEFT JOIN (SELECT pc.pid,IF(LEFT(pc.chroniccode,1) IN ('e'),'เบาหวาน','') AS 'DM' FROM personchronic pc INNER JOIN cdisease cd ON pc.chroniccode = cd.diseasecode WHERE LEFT(pc.chroniccode,1)IN ('e')) AS pc1 ON p.pid = pc1.pid LEFT JOIN (SELECT pc.pid,IF(LEFT(pc.chroniccode,1) IN ('i'),'ความดัน','') AS 'HT' FROM personchronic pc INNER JOIN cdisease cd ON pc.chroniccode = cd.diseasecode WHERE LEFT(pc.chroniccode,1)IN ('i')) AS pc2 ON p.pid = pc2.pid LEFT JOIN (SELECT pc.pid,IF(LEFT(pc.chroniccode,1) IN ('n'),'ไตวายเรื้อรัง','') AS 'N' FROM personchronic pc INNER JOIN cdisease cd ON pc.chroniccode = cd.diseasecode WHERE LEFT(pc.chroniccode,1)IN ('n')) AS pc3 ON p.pid = pc3.pid LEFT JOIN (SELECT pc.pid, IF(LEFT(pc.chroniccode,3) IN ('A15'),'วัณโรค','') AS 'TB' FROM personchronic pc INNER JOIN cdisease cd ON pc.chroniccode = cd.diseasecode WHERE LEFT(pc.chroniccode,3)IN ('A15')) AS pc4 ON p.pid = pc4.pid LEFT JOIN (SELECT pc.pid, IF(LEFT(pc.chroniccode,3) IN ('i64'),'หลอดเลือด','') AS 'stroke' FROM personchronic pc INNER JOIN cdisease cd ON pc.chroniccode = cd.diseasecode WHERE LEFT(pc.chroniccode,3)IN ('i64')) AS pc5 ON p.pid = pc5.pid GROUP BY p.pid) as pp1 LEFT JOIN (SELECT vt.pid,f1.smoke,f2.alcohol FROM visit vt LEFT JOIN (SELECT f43.pid, IF(f43.ppspecial IN ('1B501','1B502','1B503','1B504','1B505','1B506','1B509'),'บุหรี่','') AS 'smoke' FROM f43specialpp f43 INNER JOIN cspecialpp cp ON f43.ppspecial = cp.ppcode WHERE LEFT(f43.ppspecial,4) in ('1B50')) AS f1 ON vt.pid = f1.pid LEFT JOIN (SELECT f43.pid, IF(f43.ppspecial IN ('1B600','1B601','1B602','1B603','1B604','1B609'),'แอลกอฮอล์','') AS 'alcohol' FROM f43specialpp f43 INNER JOIN cspecialpp cp ON f43.ppspecial = cp.ppcode WHERE LEFT(f43.ppspecial,4) in ('1B60')) AS f2 ON vt.pid = f2.pid WHERE vt.visitdate BETWEEN '2017-04-01' AND '2020-06-30' GROUP BY vt.pid) as h1 ON pp1.pid = h1.pid GROUP BY pp1.pid ORDER BY pp1.pid;
.....................................................
PCC3
3.กลุ่มเป้าหมายไข้หวัดใหญ่
.............................................
SELECT * FROM (SELECT p.pid,ct.titlename,p.fname,p.lname,h.hno,RIGHT(h.villcode,2)AS 'moo',p.idcard,CONCAT(DAY(p.birth),'-',MONTH(p.birth),'-',YEAR(p.birth)+543)AS 'วดป.เกิด',getAgeYearNum(p.birth,CURDATE())AS'age',cd.diseasenamethai AS 'cd.diseasenamethai' FROM person p INNER JOIN ctitle ct ON p.prename = ct.titlecode INNER JOIN house h ON p.hcode = h.hcode INNER JOIN personchronic c ON p.pid= c.pid INNER JOIN cdisease cd ON c.chroniccode = cd.diseasecode WHERE p.dischargetype = '9' AND LEFT(c.chroniccode,3) = 'j45' AND (LEFT(chroniccode,3)IN ('j43','j44','j45','i60') OR LEFT(chroniccode,1)IN ('e')) GROUP BY p.pid UNION SELECT p.pid,ct.titlename,p.fname,p.lname,h.hno,RIGHT(h.villcode,2)AS 'moo',p.idcard,CONCAT(DAY(p.birth),'-',MONTH(p.birth),'-',YEAR(p.birth)+543)AS 'วดป.เกิด',getAgeYearNum(p.birth,CURDATE())AS'age','6ด.-2ปี' AS 'cd.diseasenamethai' FROM person p INNER JOIN ctitle ct ON p.prename = ct.titlecode INNER JOIN house h ON p.hcode = h.hcode WHERE p.dischargetype = '9' AND getagemonth(p.birth,CURDATE()) BETWEEN '6' AND '24' GROUP BY p.pid UNION SELECT p.pid,ct.titlename,p.fname,p.lname,h.hno,RIGHT(h.villcode,2)AS 'moo',p.idcard,CONCAT(DAY(p.birth),'-',MONTH(p.birth),'-',YEAR(p.birth)+543)AS 'วดป.เกิด', getAgeYearNum(p.birth,CURDATE())AS'age','หญิงตั้งครรภ์' AS 'cd.diseasenamethai' FROM person p INNER JOIN ctitle ct ON p.prename = ct.titlecode INNER JOIN house h ON p.hcode = h.hcode INNER JOIN visitanc va ON p.pid = va.pid WHERE p.dischargetype = '9' AND va.datecheck BETWEEN '2021-05-01' AND CURDATE() AND va.pregage >= 16 GROUP BY p.pid UNION SELECT p.pid,ct.titlename,p.fname,p.lname,h.hno,RIGHT(h.villcode,2)AS 'moo',p.idcard,CONCAT(DAY(p.birth),'-',MONTH(p.birth),'-',YEAR(p.birth)+543)AS 'วดป.เกิด',getAgeYearNum(p.birth,CURDATE())AS'age','65ปีขึ้นไป' AS 'cd.diseasenamethai' FROM person p INNER JOIN ctitle ct ON p.prename = ct.titlecode INNER JOIN house h ON p.hcode = h.hcode WHERE p.dischargetype = '9' AND getAgeYearNum(p.birth,CURDATE()) >= '65' GROUP BY p.pid UNION SELECT p.pid,ct.titlename,p.fname,p.lname,h.hno,RIGHT(h.villcode,2)AS 'moo',p.idcard,CONCAT(DAY(p.birth),'-',MONTH(p.birth),'-',YEAR(p.birth)+543)AS 'วดป.เกิด',getAgeYearNum(p.birth,CURDATE())AS'age','โรคอ้วน' AS 'cd.diseasenamethai' FROM person p INNER JOIN ctitle ct ON p.prename = ct.titlecode INNER JOIN house h ON p.hcode = h.hcode INNER JOIN visit vt ON p.pid = vt.pid WHERE p.dischargetype = '9' AND vt.visitdate BETWEEN '2021-05-01' AND CURDATE() AND (ROUND(vt.weight/(vt.height/100 * vt.height/100),2) >='35' OR vt.weight >= 100) GROUP BY p.pid) AS f1 LEFT JOIN (SELECT vi.pid,CONCAT(DAY(vi.dateepi),'-',MONTH(vi.dateepi),'-',YEAR(vi.dateepi)+543) AS 'vpidate',vi.vaccinecode FROM visitepi vi WHERE vi.vaccinecode IN ('flu') AND vi.dateepi BETWEEN '2021-05-01' AND '2021-12-31') AS v1 ON f1.pid = v1.pid
#WHERE v1.pid IS NOT NULL # เฉพาะที่รับวัคซีน
GROUP BY f1.pid ORDER BY f1.pid desc;
..............................................
PCC4
4.ancรายใหม่ (1ตุลาคม2564 - 30 มิถุนายน 2565)
หมายเหตุ: หากรันแล้ว ฝากครรภ์ครั้งแรกไม่มี แสดงว่า ไม่ได้ฝากครั้งแรกจริง..อาจเป็น coverage จ้า
........................
SELECT p1.pid,p1.titlename,p1.fname,p1.lname,p1.hno,p1.moo,p1.birthx AS 'วดป.เกิด',p1.age,va2.pregno AS 'ครรภ์ที่',p1.lmp AS 'ประจำเดือนครั้งสุดท้าย',va2.datecheck as 'วันที่ฝากครรภ์ครั้งแรก',va2.pregage AS 'อายุครรภ์ที่มาฝากครั้งแรก',p1.edc as 'กำหนดคลอด',p1.rightname,p1.diseasename AS 'ภาวะสุขภาพ' FROM (SELECT p.pid,ct.titlename,p.fname,p.lname,h.hno,RIGHT(h.villcode,2)AS 'moo',p.idcard,CONCAT(DAY(p.birth),'-',MONTH(p.birth),'-',YEAR(p.birth)+543)AS 'birthx',getAgeYearNum(p.birth,CURDATE())AS'age',cr.rightname,cd.diseasename,va.pregno,vp.lmp,va.ancno,vp.edc FROM person p LEFT JOIN personchronic pc ON p.pid = pc.pid LEFT JOIN cdisease cd ON pc.chroniccode = cd.diseasecode INNER JOIN ctitle ct ON p.prename = ct.titlecode INNER JOIN house h ON p.hcode = h.hcode INNER JOIN cright cr on p.rightcode = cr.rightcode INNER JOIN visitanc va ON p.pid = va.pid LEFT JOIN visitancpregnancy vp ON va.pid = vp.pid WHERE p.dischargetype = '9' AND va.datecheck BETWEEN '2021-10-01' AND '2022-06-30' GROUP BY p.pid) AS p1 LEFT JOIN (SELECT vc.pid,vc.datecheck,vc.pregage,vc.pregno,vc.ancno FROM visitanc vc WHERE vc.ancno = 1) as va2 ON p1.pid = va2.pid AND p1.pregno = va2.pregno AND p1.ancno = va2.ancno GROUP BY p1.pid;
.............................
PCC5
5.จำนวน ANC ปี 2564 ทั้งหมด
..........................................
SELECT ct.titlename,p.fname,p.lname,p.idcard AS 'CID',p.pid AS 'HN',vp.lmp,vp.edc,getAgeYearNum(p.birth,CURDATE())AS'age',h.hno,RIGHT(h.villcode,2)AS 'moo' ,CONCAT(DAY(p.birth),'-',MONTH(p.birth),'-',YEAR(p.birth)+543)AS 'วดป.เกิด',cr.rightname AS 'สิทธิรักษา',cd.diseasename AS 'โรคประจำตัว',va.pregno AS 'ครรภ์ที่' FROM person p LEFT JOIN personchronic pc ON p.pid = pc.pid LEFT JOIN cdisease cd ON pc.chroniccode = cd.diseasecode INNER JOIN ctitle ct ON p.prename = ct.titlecode INNER JOIN house h ON p.hcode = h.hcode INNER JOIN cright cr on p.rightcode = cr.rightcode INNER JOIN visitanc va ON p.pid = va.pid LEFT JOIN visitancpregnancy vp ON va.pid = vp.pid WHERE p.dischargetype = '9' AND va.datecheck BETWEEN '2020-10-01' AND '2021-09-30' GROUP BY p.pid;
.........................................
PCC6
6.จำนวน ANC ปี 2565 ทั้งหมด
..........................................
SELECT ct.titlename,p.fname,p.lname,p.idcard AS 'CID',p.pid AS 'HN',vp.lmp,vp.edc,getAgeYearNum(p.birth,CURDATE())AS'age',h.hno,RIGHT(h.villcode,2)AS 'moo' ,CONCAT(DAY(p.birth),'-',MONTH(p.birth),'-',YEAR(p.birth)+543)AS 'วดป.เกิด',cr.rightname AS 'สิทธิรักษา',cd.diseasename AS 'โรคประจำตัว',va.pregno AS 'ครรภ์ที่' FROM person p LEFT JOIN personchronic pc ON p.pid = pc.pid LEFT JOIN cdisease cd ON pc.chroniccode = cd.diseasecode INNER JOIN ctitle ct ON p.prename = ct.titlecode INNER JOIN house h ON p.hcode = h.hcode INNER JOIN cright cr on p.rightcode = cr.rightcode INNER JOIN visitanc va ON p.pid = va.pid LEFT JOIN visitancpregnancy vp ON va.pid = vp.pid WHERE p.dischargetype = '9' AND va.datecheck BETWEEN '2021-10-01' AND '2022-09-30' GROUP BY p.pid;
.........................................
ไม่มีความคิดเห็น:
แสดงความคิดเห็น