QOF-64 (1.2) การคัดกรองภาวะซึมเศร้าในกลุ่มผู้ป่วยเรื้อรัง
ID | 824 |
---|---|
Parent ID | 61 |
Table Name | qof64_kpi01_depression_chronic |
Title | QOF-64 (1.2) การคัดกรองภาวะซึมเศร้าในกลุ่มผู้ป่วยเรื้อรัง |
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; DROP TABLE IF EXISTS tmp1_qof64_specialpp2q; CREATE TABLE IF NOT EXISTS tmp1_qof64_specialpp2q( PRIMARY KEY (HOSPCODE,PID),KEY (cid),KEY (hospcode,pid)) (#depress Chronic 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 ('1B130','1B131') GROUP BY p.CID ORDER BY s.date_serv ); DROP TABLE IF EXISTS tmp1_qof64_specialpp9q; CREATE TABLE IF NOT EXISTS tmp1_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 = '1B131' AND b.PPSPECIAL IN ('1B0260','1B0261','1B0262','1B0263') GROUP BY p.CID ORDER BY a.date_serv ); # #merge 2Q,9Q to One Table (Service 2Q,9Q One Hospcode) # DROP TABLE IF EXISTS tmp1_qof64_ppspecial_2q9q; CREATE TABLE IF NOT EXISTS tmp1_qof64_ppspecial_2q9q( PRIMARY KEY (HOSPCODE,PID),KEY (cid),KEY (hospcode,pid)) (#Depress Chronic SELECT a.HOSPCODE,a.PID,a.CID, a.2Q_PLACE,a.2Q_DATE,a.2Q, b.9Q,b.9Q_DATE,b.9Q_PLACE FROM tmp1_qof64_specialpp2q a LEFT JOIN tmp1_qof64_specialpp9q b ON (b.HOSPCODE=a.HOSPCODE AND b.PID=a.PID) ); # DROP TABLE IF EXISTS tmp1_qof64_specialpp2q; DROP TABLE IF EXISTS tmp1_qof64_specialpp9q; # #------------------------------------------------------------- #Stap3 Crate person Depression Chronic DROP TABLE IF EXISTS tmp_qof64_depress_chronic; CREATE TABLE IF NOT EXISTS tmp_qof64_depress_chronic( 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 INNER 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.typearea IN (1,3) ORDER BY a.HOSPCODE,a.PID ); /*โรคจิต*/ UPDATE tmp_qof64_depress_chronic SET check_chronic = '1' WHERE INSTR(g_chronic,'F'); /*เบาหวาน*/ UPDATE tmp_qof64_depress_chronic 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_depress_chronic 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_depress_chronic 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_depress_chronic 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_depress_chronic SET check_chronic = '1' WHERE INSTR(g_chronic,'I6'); /*ปอดอุดกลั้นเรื้อรัง*/ UPDATE tmp_qof64_depress_chronic 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 person + special DROP TABLE IF EXISTS qof64_1_depress_chronic; CREATE TABLE IF NOT EXISTS qof64_1_depress_chronic( SELECT a.*,b.2Q_PLACE,b.2Q_Date,b.2Q,b.9Q,b.9Q_Date,b.9Q_PLACE FROM tmp_qof64_depress_chronic a LEFT JOIN tmp1_qof64_ppspecial_2q9q b ON b.CID=a.CID ); #2020-09-02 UPDATE qof64_1_depress_chronic SET 2Q_PLACE=null ,2Q_Date = NULL,2Q =NULL ,9Q = NULL,9Q_Date = NULL , 9Q_PLACE = NULL WHERE age_y >= 60; # UPDATE qof64_1_depress_chronic a INNER JOIN tmp_qof64_ppspecial_2q9q b ON a.CID=b.CID AND a.age_y >=60 AND a.check_chronic = 1 SET a.2Q_PLACE=b.2Q_PLACE ,a.2Q_Date=b.2Q_DATE ,a.2Q=b.2Q, a.9Q_PLACE=b.9Q_PLACE ,a.9Q_Date=b.9Q_DATE ,a.9Q=b.9Q; # DROP TABLE IF EXISTS tmp_qof64_depress_chronic; DROP TABLE IF EXISTS tmp_qof64_ppspecial_2q9q; DROP TABLE IF EXISTS tmp1_qof64_ppspecial_2q9q; # ALTER TABLE qof64_1_depress_chronic ADD COLUMN Maininscl VARCHAR(3); ALTER TABLE qof64_1_depress_chronic ADD COLUMN Hmain VARCHAR(5); ALTER TABLE qof64_1_depress_chronic ADD COLUMN Hsub VARCHAR(5); ALTER TABLE qof64_1_depress_chronic ADD COLUMN Pass VARCHAR(1); #ปรับสิทธิรักษาพยาบาล UPDATE qof64_1_depress_chronic 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_depress_chronic; #PROVIDER1_SQL# |
Script Cron | |
Force Script Cron | 0 |
Active | 1 |
Client Office Type | |
Create Date | 2020-11-02 01:48:18 |
Last Update | 2020-11-04 03:50:48 |
- Home
- About
- Privacy Policy
- Terms of Use
- Contact Us
© 2015. All Rights Reserved.