• Home
  • My Scripts
  • 6. คัดกรองซึมเศร้าในเรือนจำ

6. คัดกรองซึมเศร้าในเรือนจำ

Update Delete

ID810
Parent ID59
Table Nameprison64_04_depress_screen
Title6. คัดกรองซึมเศร้าในเรือนจำ
Description
Script#SQL_OPTIONS#
PROVIDERS=1
PROVIDER1=43STD
PROVIDER1_VALIDATE_TABLES=ncdscreen,t_person_db
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 @datestart:='2020-10-15';
SET @dateend:='2021-07-31';

DROP TABLES IF EXISTS prison64_04_depress_screen ;
CREATE TABLE IF NOT EXISTS prison64_04_depress_screen (
`HOSPCODE` varchar(5) NOT NULL,
`PID` varchar(15) NOT NULL,
`CID` varchar(13) DEFAULT NULL,
`BIRTH` date DEFAULT NULL,
`AGE` varchar(3) DEFAULT NULL,
`CODE_2Q` varchar(10) DEFAULT NULL,
`result_2Q` varchar(40) DEFAULT NULL,
`D_SERV_2Q` date DEFAULT NULL,
`H_SERV_2Q` varchar(5) DEFAULT NULL,
`CODE_9Q` varchar(10) DEFAULT NULL,
`result_9Q` varchar(40) DEFAULT NULL,
`D_SERV_9Q` date DEFAULT NULL,
`H_SERV_9Q` varchar(5) DEFAULT NULL,
`CODE_8Q` varchar(10) DEFAULT NULL,
`result_8Q` varchar(40) DEFAULT NULL,
`D_SERV_8Q` date DEFAULT NULL,
`H_SERV_8Q` varchar(5) DEFAULT NULL,
`CODE_SCREEN` varchar(10) DEFAULT NULL,
`result_SCREEN` varchar(40) DEFAULT NULL,
`D_SERV_SCREEN` date DEFAULT NULL,
`H_SERV_SCREEN` varchar(5) DEFAULT NULL,
PRIMARY KEY (HOSPCODE,PID),
KEY (cid)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 IGNORE AS (
SELECT HOSPCODE,PID,CID,BIRTH,NULL'AGE'
,NULL'CODE_2Q',NULL'result_2Q',NULL'D_SERV_2Q',NULL'H_SERV_2Q',NULL'CODE_9Q',NULL'result_9Q',NULL'D_SERV_9Q',NULL'H_SERV_9Q'
,NULL'CODE_8Q',NULL'result_8Q',NULL'D_SERV_8Q',NULL'H_SERV_8Q',NULL'CODE_SCREEN',NULL'result_SCREEN',NULL'D_SERV_SCREEN',NULL'H_SERV_SCREEN'
FROM prison64_00_prisonerDB
);

#สร้างtemp table specialpp & diagnosis_opd
DROP TABLE IF EXISTS `_tmp_depress_screen`;
CREATE TEMPORARY TABLE `_tmp_depress_screen` (
`hospcode` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
`pid` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
`seq` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`date_serv` date NULL DEFAULT '0000-00-00',
`screencode` varchar(6) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
`file` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
`cid` char(13) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
INDEX `cid`(`cid`) USING BTREE
)
SELECT s.HOSPCODE,s.PID,s.SEQ,s.DATE_SERV,s.diagcode as 'screencode','diagnosis_opd' as 'file',p.CID
FROM diagnosis_opd s INNER JOIN person p ON s.HOSPCODE=p.HOSPCODE AND s.PID=p.PID
WHERE s.DATE_SERV BETWEEN @datestart AND @dateend
ORDER BY DATE_SERV DESC #เพื่อไดบริการล่าสุด
;
INSERT INTO `_tmp_depress_screen` SELECT s.HOSPCODE,s.PID,s.SEQ,s.DATE_SERV,s.PPSPECIAL as 'screencode','SPECIALPP' as 'file',p.CID
FROM specialpp s INNER JOIN person p ON s.HOSPCODE=p.HOSPCODE AND s.PID=p.PID
WHERE s.DATE_SERV BETWEEN @datestart AND @dateend
ORDER BY DATE_SERV DESC #เพื่อไดบริการล่าสุด
;

#หยอดผลงาน 8Q
UPDATE prison64_04_depress_screen p,_tmp_depress_screen s
SET p.AGE= AGE(s.DATE_SERV,p.BIRTH,'y') #มีdate_serv จึงคำนวณอายุ
,p.CODE_9Q=s.screencode,p.D_SERV_9Q=s.DATE_SERV,p.H_SERV_9Q=s.HOSPCODE
WHERE s.screencode in('1B0270','1B0271','1B0272','1B0273','1B0279')
AND p.CID=s.CID
;
#หยอดผลงาน 9Q
UPDATE prison64_04_depress_screen p,_tmp_depress_screen s
SET p.AGE= AGE(s.DATE_SERV,p.BIRTH,'y') #มีdate_serv จึงคำนวณอายุ
,p.CODE_9Q=s.screencode,p.D_SERV_9Q=s.DATE_SERV,p.H_SERV_9Q=s.HOSPCODE
WHERE s.screencode in('1B0260','1B0261','1B0262','1B0263','1B0269','1B0282','1B0283','1B0284','1B0285')
AND p.CID=s.CID
;
#หยอดผลงาน 2Q เรียงลำดับท้ายเพราะเทียบอายุจากวันบริการให้มาทับ
UPDATE prison64_04_depress_screen p,_tmp_depress_screen s
SET p.AGE= AGE(s.DATE_SERV,p.BIRTH,'y') #มีdate_serv จึงคำนวณอายุ
,p.CODE_2Q=s.screencode,p.D_SERV_2Q=s.DATE_SERV,p.H_SERV_2Q=s.HOSPCODE
WHERE s.screencode in('1B0280','1B0281','1B130','1B131','1B140','1B141','1B150','1B151','1B160','1B161','1B170','1B171','1B180','1B181')
AND p.CID=s.CID
;

#หยอดผลงาน คัดกรองซึมเศร้าจากแฟ้ม diagnosis_opd ='Z133%'
UPDATE prison64_04_depress_screen p,_tmp_depress_screen s
SET p.AGE= AGE(s.DATE_SERV,p.BIRTH,'y') #มีdate_serv จึงคำนวณอายุ
,p.CODE_SCREEN=s.screencode,p.D_SERV_SCREEN=s.DATE_SERV,p.H_SERV_SCREEN=s.HOSPCODE
WHERE s.screencode LIKE 'Z133%'
AND p.CID=s.CID
;

#แปลผล
UPDATE prison64_04_depress_screen p
SET p.result_2Q=IF(p.CODE_2Q in('1B0280','1B130','1B140','1B150','1B160','1B170','1B180'),'ผลปกติ'
,IF(p.CODE_2Q in('1B0281','1B131','1B141','1B151','1B161','1B171','1B181'),'ผลผิดปกติ',NULL))
#9Q
,p.result_9Q=IF(p.CODE_9Q in('1B0260','1B0282'),'ผลปกติ',
IF(p.CODE_9Q in('1B0261','1B0283'),'ซึมเศร้าน้อย',
IF(p.CODE_9Q in('1B0262','1B0284'),'ซึมเศร้าปานกลาง ',
IF(p.CODE_9Q in('1B0263','1B0285'),'ซึมเศร้ารุนแรง ',NULL))))
#8Q
,p.result_8Q=IF(p.CODE_8Q in('1B0270'),'ไม่มีแนวโน้มการฆ่าตัวตาย ',
IF(p.CODE_8Q in('1B0271'),'มีแนวโน้มที่จะฆ่าตัวตายระดับน้อย',
IF(p.CODE_8Q in('1B0272'),'มีแนวโน้มที่จะฆ่าตัวตายระดับปานกลาง ',
IF(p.CODE_8Q in('1B0273'),'มีแนวโน้มที่จะฆ่าตัวตายระดับรุนแรง ',
IF(p.CODE_8Q in('1B0279'),'ไม่ระบุรายละเอียด ',NULL)))))
#Z133
,p.result_SCREEN=IF(p.CODE_SCREEN <>'','Z13.3',NULL)
;


# INDIVIDUAL
SELECT h.hospcode,h.hosname ,p.cid ,p.AGE as age
,GROUP_CONCAT('[',p.H_SERV_2Q,'] ',p.D_SERV_2Q,' : ',p.result_2Q) as 'date_serv_2Q'
,GROUP_CONCAT('[',p.H_SERV_9Q,'] ',p.D_SERV_9Q,' : ',p.result_9Q) as 'date_serv_9Q'
,GROUP_CONCAT('[',p.H_SERV_8Q,'] ',p.D_SERV_8Q,' : ',p.result_8Q) as 'date_serv_8Q'
,GROUP_CONCAT('[',p.H_SERV_SCREEN,'] ',p.D_SERV_SCREEN,' : ',p.result_SCREEN) as result
FROM prison64_04_depress_screen p LEFT JOIN prison64_00_prisonerDB h ON p.CID=h.CID
WHERE p.AGE BETWEEN 15 AND 120
GROUP BY p.CID
;





#PROVIDER1_SQL#
Script Cron
Force Script Cron0
Active1
Client Office Type
Create Date2020-05-14 11:36:19
Last Update2021-03-25 11:42:09