QOF-64 (1.1) การคัดกรองภาวะซึมเศร้าในกลุ่มผู้สูงอายุ
ID | 823 |
---|---|
Parent ID | 61 |
Table Name | qof64_kpi01_depression60 |
Title | QOF-64 (1.1) การคัดกรองภาวะซึมเศร้าในกลุ่มผู้สูงอายุ |
Description | |
Script | #SQL_OPTIONS# PROVIDERS=1 PROVIDER1=43STD PROVIDER1_VALIDATE_TABLES=accident,admission,anc,appointment SCRIPT_FLOW=SQL #SQL_OPTIONS# #PROVIDER1_SQL# SET @provcode = :provcode; SET @rep_year = :rep_year; SET @hoscode = :hoscode; SET @hosname = :hosname; SET @hostype = :hostype; SET @address = :address; SET @subdistcode = :subdistcode; SET @distcode = :distcode; SET @level_service = :level_service; SET @prov_c :=@provcode; SET @b_year:='2021'; SET @start_d:=concat(@b_year-1,'-04-01'); SET @end_d:=concat(@b_year,'-03-31'); SET SESSION group_concat_max_len = 1000000; #------------------------------------------------# #Stap1 #2Q One Record in Hospcode DROP TABLE IF EXISTS tmp_qof64_specialpp2q; CREATE TABLE IF NOT EXISTS tmp_qof64_specialpp2q( PRIMARY KEY (HOSPCODE,PID),KEY (cid),KEY (hospcode,pid)) (#depress 60year SELECT s.HOSPCODE,s.PID,p.CID, GROUP_CONCAT(s.DATE_SERV ORDER BY s.date_serv) '2Q_DATE', GROUP_CONCAT(s.PPSPECIAL ORDER BY s.date_serv) '2Q', GROUP_CONCAT(s.PPSPLACE ORDER BY s.date_serv) '2Q_PLACE',s.PROVIDER,s.D_UPDATE FROM specialpp s LEFT JOIN person p ON s.HOSPCODE=p.HOSPCODE AND s.PID=p.PID WHERE DATE_SERV BETWEEN @start_d AND @end_d AND s.PPSPECIAL IN ('1B0280','1B0281') GROUP BY p.CID ORDER BY s.date_serv ); #9Q One Record in Hospcode DROP TABLE IF EXISTS tmp_qof64_specialpp9q; CREATE TABLE IF NOT EXISTS tmp_qof64_specialpp9q( PRIMARY KEY (HOSPCODE,PID),KEY (cid),KEY (hospcode,pid)) ( SELECT a.HOSPCODE,a.PID,p.CID, GROUP_CONCAT(a.DATE_SERV ORDER BY a.DATE_SERV) '9Q_DATE', GROUP_CONCAT(b.PPSPECIAL ORDER BY a.DATE_SERV) '9Q', GROUP_CONCAT(a.PPSPLACE ORDER BY a.DATE_SERV) '9Q_PLACE',a.PROVIDER,b.D_UPDATE FROM specialpp a INNER JOIN specialpp b ON b.HOSPCODE=a.HOSPCODE AND b.PID=a.PID AND a.DATE_SERV=b.DATE_SERV INNER JOIN t_person_cid p ON p.HOSPCODE=a.HOSPCODE AND p.PID=a.PID WHERE a.DATE_SERV BETWEEN @start_d AND @end_d AND a.PPSPECIAL = '1B0281' AND b.PPSPECIAL IN ('1B0282','1B0283','1B0284','1B0285') GROUP BY p.CID ORDER BY a.date_serv ); #merge 2Q,9Q to One Table (Service 2Q,9Q One Hospcode) DROP TABLE IF EXISTS tmp_qof64_ppspecial_2q9q; CREATE TABLE IF NOT EXISTS tmp_qof64_ppspecial_2q9q( PRIMARY KEY (HOSPCODE,PID),KEY (cid),KEY (hospcode,pid)) (#Depress 60y SELECT a.HOSPCODE,a.PID,a.CID, a.2Q_PLACE,a.2Q_DATE,a.2Q, b.9Q,b.9Q_DATE,b.9Q_PLACE FROM tmp_qof64_specialpp2q a LEFT JOIN tmp_qof64_specialpp9q b ON (b.HOSPCODE=a.HOSPCODE AND b.PID=a.PID) ); DROP TABLE IF EXISTS tmp_qof64_specialpp2q; DROP TABLE IF EXISTS tmp_qof64_specialpp9q; #------------------------------------------------# #Stap2 Crate Depression 60 year DROP TABLE IF EXISTS tmp_qof64_depress60; CREATE TABLE IF NOT EXISTS tmp_qof64_depress60( PRIMARY KEY (HOSPCODE,PID,CID),KEY(HOSPCODE,PID)) ( #SELECT a.HOSPCODE,a.PID,a.CID,CONCAT(a.`NAME`,' ',a.LNAME) 'PNAME',a.SEX,a.NATION,a.age_y,a.DISCHARGE,a.TYPEAREA,a.vhid,b.g_chronic,'0' AS 'check_chronic' #FROM t_person_cid a # LEFT OUTER JOIN (SELECT cid,GROUP_CONCAT(DIAGCODE ORDER BY DIAGCODE) AS 'g_chronic' # FROM t_chronic GROUP BY cid)b ON a.cid=b.cid #WHERE a.NATION = '099' # AND a.DISCHARGE = '9' AND a.age_y >= '60' # AND a.typearea IN (1,3) #all type #ORDER BY a.HOSPCODE,a.PID ## แก้ไข SQL ด้านบน เพื่อเพิ่มความเร็วในการประมวลผล //Shongpon 2/3/2564 SELECT a.HOSPCODE,a.PID,a.CID,CONCAT(a.`NAME`,' ',a.LNAME) 'PNAME',a.SEX,a.NATION,a.age_y,a.DISCHARGE,a.TYPEAREA,a.vhid,GROUP_CONCAT(b.DIAGCODE ORDER BY b.DIAGCODE) AS 'g_chronic','0' AS 'check_chronic' FROM t_person_cid a LEFT OUTER JOIN t_chronic b ON a.cid=b.cid WHERE a.NATION = '099' AND a.DISCHARGE = '9' AND a.age_y >= '60' AND a.typearea IN (1,3) #all type GROUP BY a.cid ORDER BY a.HOSPCODE,a.PID ); UPDATE tmp_qof64_depress60 SET check_chronic = '1' WHERE INSTR(g_chronic,'F'); /*เบาหวาน*/ UPDATE tmp_qof64_depress60 SET check_chronic = '1' WHERE INSTR(g_chronic,'E10') OR INSTR(g_chronic,'E11') OR INSTR(g_chronic,'E12') OR INSTR(g_chronic,'E14'); /*ความดัน*/ UPDATE tmp_qof64_depress60 SET check_chronic = '1' WHERE INSTR(g_chronic,'I10') OR INSTR(g_chronic,'I11') OR INSTR(g_chronic,'I12') OR INSTR(g_chronic,'I13') OR INSTR(g_chronic,'I14') OR INSTR(g_chronic,'I15'); /*มะเร็ง*/ UPDATE tmp_qof64_depress60 SET check_chronic = '1' WHERE INSTR(g_chronic,'C') OR INSTR(g_chronic,'D0') OR INSTR(g_chronic,'D1') OR INSTR(g_chronic,'D2') OR INSTR(g_chronic,'D3') OR INSTR(g_chronic,'D4') ; /*โรคหัวใจและหลอดเลือด*/ UPDATE tmp_qof64_depress60 SET check_chronic = '1' WHERE INSTR(g_chronic,'I2') OR INSTR(g_chronic,'I3') OR INSTR(g_chronic,'I4') OR INSTR(g_chronic,'I50') OR INSTR(g_chronic,'I51') OR INSTR(g_chronic,'I52') ; /*หลอดเลือดสมอง*/ UPDATE tmp_qof64_depress60 SET check_chronic = '1' WHERE INSTR(g_chronic,'I6'); /*ปอดอุดกลั้นเรื้อรัง*/ UPDATE tmp_qof64_depress60 SET check_chronic = '1' WHERE INSTR(g_chronic,'J40') OR INSTR(g_chronic,'J41') OR INSTR(g_chronic,'J42') OR INSTR(g_chronic,'J43') OR INSTR(g_chronic,'J44') OR INSTR(g_chronic,'J45') OR INSTR(g_chronic,'J46') OR INSTR(g_chronic,'J40'); # #Merge People & Service DROP TABLE IF EXISTS qof64_1_depress60y; CREATE TABLE IF NOT EXISTS qof64_1_depress60y( SELECT a.*,b.2Q_PLACE,b.2Q_Date,b.2Q,b.9Q,b.9Q_Date,b.9Q_PLACE FROM tmp_qof64_depress60 a LEFT JOIN tmp_qof64_ppspecial_2q9q b ON b.CID=a.CID ); # DROP TABLE IF EXISTS tmp_qof64_depress60; # ALTER TABLE qof64_1_depress60y ADD COLUMN Maininscl VARCHAR(3); ALTER TABLE qof64_1_depress60y ADD COLUMN Hmain VARCHAR(5); ALTER TABLE qof64_1_depress60y ADD COLUMN Hsub VARCHAR(5); ALTER TABLE qof64_1_depress60y ADD COLUMN Pass VARCHAR(1); # #ปรับสิทธิรักษาพยาบาล UPDATE qof64_1_depress60y a,dbpop b SET a.Maininscl=b.MainInScl, a.Hmain=b.HMain, a.Hsub=b.Hsub WHERE a.cid=b.PID; SELECT * FROM qof64_1_depress60y; #PROVIDER1_SQL# |
Script Cron | |
Force Script Cron | 0 |
Active | 1 |
Client Office Type | |
Create Date | 2020-11-02 01:46:17 |
Last Update | 2021-03-02 10:02:43 |
- Home
- About
- Privacy Policy
- Terms of Use
- Contact Us
© 2015. All Rights Reserved.