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

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

Update Delete

ID823
Parent ID61
Table Nameqof64_kpi01_depression60
TitleQOF-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 Cron0
Active1
Client Office Type
Create Date2020-11-02 01:46:17
Last Update2021-03-02 10:02:43