• Home
  • My Scripts
  • QOF-64 (1.2) การคัดกรองภาวะซึมเศร้าในกลุ่มผู้ป่วยเรื้อรัง

QOF-64 (1.2) การคัดกรองภาวะซึมเศร้าในกลุ่มผู้ป่วยเรื้อรัง

Update Delete

ID824
Parent ID61
Table Nameqof64_kpi01_depression_chronic
TitleQOF-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 Cron0
Active1
Client Office Type
Create Date2020-11-02 01:48:18
Last Update2020-11-04 03:50:48