2. คัดกรอง DM ในเรือนจำ
ID | 807 |
---|---|
Parent ID | 59 |
Table Name | prison64_01_dm_screen |
Title | 2. คัดกรอง DM ในเรือนจำ |
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 TABLE IF EXISTS prison64_01_dm_screen; CREATE TABLE IF NOT EXISTS prison64_01_dm_screen (SELECT * FROM(SELECT n.HOSPCODE, n.PID, p.CID, p.BIRTH, TIMESTAMPDIFF(YEAR,p.BIRTH,n.DATE_SERV) as AGE, n.DATE_SERV, n.BSTEST, n.BSLEVEL,' ' as "risk" FROM ncdscreen n LEFT JOIN t_person_db p ON n.HOSPCODE=p.HOSPCODE and n.PID=p.PID WHERE n.date_serv BETWEEN @datestart AND @dateend AND TIMESTAMPDIFF(YEAR,p.BIRTH,n.DATE_SERV) BETWEEN 35 AND 120 AND BSLEVEL >=50 AND p.CID IN(SELECT CID from prison64_00_prisonerDB) ORDER BY p.CID,DATE_SERV DESC) dup # เรียงลำดับบริการล่าสุดก่อนตัดซ้ำ GROUP BY CID); # ตัดซ้ำ # แปลผล update prison64_01_dm_screen SET risk = '0' where (((bstest in('1','3') OR bstest is null) AND bslevel >= 70 AND bslevel < 100 ) OR (bstest in('2','4') AND bslevel >= 70 AND bslevel < 140)); update prison64_01_dm_screen SET risk='1' where (((bstest in('1','3') OR bstest is null) AND bslevel >= 100 AND bslevel <= 125 ) OR (bstest in('2','4') AND bslevel >= 140 AND bslevel <= 200 )); update prison64_01_dm_screen SET risk='2' where (((bstest in('1','3') OR bstest is null) AND bslevel > 125) OR (bstest in('2','4') AND bslevel > 200)); # INDIVIDUAL ส่งเข้าฐาน a6hd SELECT h.hospcode,h.hosname as hosname ,p.cid ,p.AGE as age,CONCAT('[',p.HOSPCODE,'] ',p.DATE_SERV) as date_serv,p.BSLEVEL as bslevel ,IF(risk='0','ปกติ ',IF(risk='1','เสี่ยง ',IF(risk='2','สงสัยป่วย','นอกเกณฑ์'))) as result FROM prison64_01_dm_screen p LEFT JOIN prison64_00_prisonerDB h ON p.CID=h.CID ORDER BY p.CID ; #PROVIDER1_SQL# |
Script Cron | |
Force Script Cron | 0 |
Active | 1 |
Client Office Type | |
Create Date | 2020-05-06 09:28:52 |
Last Update | 2021-03-25 11:36:51 |
- Home
- About
- Privacy Policy
- Terms of Use
- Contact Us
© 2015. All Rights Reserved.