2. คัดกรอง DM ในเรือนจำ

Update Delete

ID807
Parent ID59
Table Nameprison64_01_dm_screen
Title2. คัดกรอง 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 Cron0
Active1
Client Office Type
Create Date2020-05-06 09:28:52
Last Update2021-03-25 11:36:51