PPA4 ผู้ป่วยโรคเรื้อรัง ได้รับการคัดกรองภาวะซึมเศร้า
ID | 840 |
---|---|
Parent ID | 64 |
Table Name | 65_ppa_depression_chronic_2 |
Title | PPA4 ผู้ป่วยโรคเรื้อรัง ได้รับการคัดกรองภาวะซึมเศร้า |
Description | |
Script | #SQL_OPTIONS# PROVIDERS=1 PROVIDER1=43STD PROVIDER1_VALIDATE_TABLES=student,t_person_cid,anc,labor 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:='2022'; #งวดที่ 1 SET @start_d1:=concat(@b_year-1,'1001'); SET @end_d1:=concat(@b_year,'0228'); #งวดที่ 2 SET @start_d:=concat(@b_year,'0301'); SET @end_d:=concat(@b_year,'0630'); SET SESSION group_concat_max_len = 1000000; #------------------------------------------------# #Stap1 #2Q One Record in Hospcode DROP TABLE IF EXISTS tmp_ppa65_specialpp2q; CREATE TABLE IF NOT EXISTS tmp_ppa65_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 ); #Stap1-งวดที่1 #2Q One Record in Hospcode DROP TABLE IF EXISTS tmp_ppa65_specialpp2q_A1; CREATE TABLE IF NOT EXISTS tmp_ppa65_specialpp2q_A1( 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', 'A1' AS '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_d1 AND @end_d1 AND s.PPSPECIAL IN ('1B0280','1B0281') GROUP BY p.CID ORDER BY s.date_serv ); INSERT IGNORE INTO tmp_ppa65_specialpp2q (SELECT * FROM tmp_ppa65_specialpp2q_A1); DROP TABLE IF EXISTS tmp_ppa65_specialpp2q_A1; #9Q One Record in Hospcode DROP TABLE IF EXISTS tmp_ppa65_specialpp9q; CREATE TABLE IF NOT EXISTS tmp_ppa65_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 ); #งวดที่ 1 #9Q One Record in Hospcode DROP TABLE IF EXISTS tmp_ppa65_specialpp9q_B1; CREATE TABLE IF NOT EXISTS tmp_ppa65_specialpp9q_B1( 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', 'B1' AS '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_d1 AND @end_d1 AND a.PPSPECIAL = '1B0281' AND b.PPSPECIAL IN ('1B0282','1B0283','1B0284','1B0285') GROUP BY p.CID ORDER BY a.date_serv ); INSERT IGNORE INTO tmp_ppa65_specialpp9q (SELECT * FROM tmp_ppa65_specialpp9q_B1); DROP TABLE IF EXISTS tmp_ppa65_specialpp9q_B1; #merge 2Q,9Q to One Table (Service 2Q,9Q One Hospcode) DROP TABLE IF EXISTS tmp_ppa65_ppspecial_2q9q; CREATE TABLE IF NOT EXISTS tmp_ppa65_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_ppa65_specialpp2q a LEFT JOIN tmp_ppa65_specialpp9q b ON (b.HOSPCODE=a.HOSPCODE AND b.PID=a.PID) ); DROP TABLE IF EXISTS tmp_ppa65_specialpp2q; DROP TABLE IF EXISTS tmp_ppa65_specialpp9q; DROP TABLE IF EXISTS tmp1_ppa65_specialpp2q; CREATE TABLE IF NOT EXISTS tmp1_ppa65_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 ); # งวดที่ 1 DROP TABLE IF EXISTS tmp1_ppa65_specialpp2q_C1; CREATE TABLE IF NOT EXISTS tmp1_ppa65_specialpp2q_C1( 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', 'C1' AS '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_d1 AND @end_d1 AND s.PPSPECIAL IN ('1B130','1B131') GROUP BY p.CID ORDER BY s.date_serv ); INSERT IGNORE INTO tmp1_ppa65_specialpp2q (SELECT * FROM tmp1_ppa65_specialpp2q_C1); DROP TABLE IF EXISTS tmp1_ppa65_specialpp2q_C1; DROP TABLE IF EXISTS tmp1_ppa65_specialpp9q; CREATE TABLE IF NOT EXISTS tmp1_ppa65_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 ); DROP TABLE IF EXISTS tmp1_ppa65_specialpp9q_D1; CREATE TABLE IF NOT EXISTS tmp1_ppa65_specialpp9q_D1( 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', 'D1' AS '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_d1 AND @end_d1 AND a.PPSPECIAL = '1B131' AND b.PPSPECIAL IN ('1B0260','1B0261','1B0262','1B0263') GROUP BY p.CID ORDER BY a.date_serv ); INSERT IGNORE INTO tmp1_ppa65_specialpp9q (SELECT * FROM tmp1_ppa65_specialpp9q_D1); DROP TABLE IF EXISTS tmp1_ppa65_specialpp9q_D1; # #merge 2Q,9Q to One Table (Service 2Q,9Q One Hospcode) # DROP TABLE IF EXISTS tmp1_ppa65_ppspecial_2q9q; CREATE TABLE IF NOT EXISTS tmp1_ppa65_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_ppa65_specialpp2q a LEFT JOIN tmp1_ppa65_specialpp9q b ON (b.HOSPCODE=a.HOSPCODE AND b.PID=a.PID) ); # DROP TABLE IF EXISTS tmp1_ppa65_specialpp2q; DROP TABLE IF EXISTS tmp1_ppa65_specialpp9q; # #------------------------------------------------------------- #Stap3 Crate person Depression Chronic DROP TABLE IF EXISTS tmp_ppa65_depress_chronic; CREATE TABLE IF NOT EXISTS tmp_ppa65_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_ppa65_depress_chronic SET check_chronic = '1' WHERE INSTR(g_chronic,'F'); /*เบาหวาน*/ UPDATE tmp_ppa65_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_ppa65_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_ppa65_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_ppa65_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_ppa65_depress_chronic SET check_chronic = '1' WHERE INSTR(g_chronic,'I6'); /*ปอดอุดกลั้นเรื้อรัง*/ UPDATE tmp_ppa65_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 ppa65_1_depress_chronic; CREATE TABLE IF NOT EXISTS ppa65_1_depress_chronic( SELECT a.*,b.2Q_PLACE,b.2Q_Date,b.2Q,b.9Q,b.9Q_Date,b.9Q_PLACE FROM tmp_ppa65_depress_chronic a LEFT JOIN tmp1_ppa65_ppspecial_2q9q b ON b.CID=a.CID ); #2020-09-02 UPDATE ppa65_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 ppa65_1_depress_chronic a INNER JOIN tmp_ppa65_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_ppa65_depress_chronic; DROP TABLE IF EXISTS tmp_ppa65_ppspecial_2q9q; DROP TABLE IF EXISTS tmp1_ppa65_ppspecial_2q9q; # ALTER TABLE ppa65_1_depress_chronic ADD COLUMN Maininscl VARCHAR(3); ALTER TABLE ppa65_1_depress_chronic ADD COLUMN Hmain VARCHAR(5); ALTER TABLE ppa65_1_depress_chronic ADD COLUMN Hsub VARCHAR(5); ALTER TABLE ppa65_1_depress_chronic ADD COLUMN Pass VARCHAR(1); #ปรับสิทธิรักษาพยาบาล UPDATE ppa65_1_depress_chronic a,dbpop b SET a.Maininscl=b.MainInScl, a.Hmain=b.HMain, a.Hsub=b.Hsub WHERE a.cid=b.PID; #ลบคนที่มีผลงานในงวดที่ 1 ออก DELETE FROM ppa65_1_depress_chronic WHERE 2Q in('A1','B1','C1','D1'); SELECT * FROM ppa65_1_depress_chronic; #PROVIDER1_SQL# |
Script Cron | |
Force Script Cron | 0 |
Active | 1 |
Client Office Type | |
Create Date | 2022-04-01 02:05:48 |
Last Update | 2022-08-18 20:52:45 |
- Home
- About
- Privacy Policy
- Terms of Use
- Contact Us
© 2015. All Rights Reserved.