3. คัดกรอง HT ในเรือนจำ

Update Delete

ID808
Parent ID59
Table Nameprison64_02_ht_screen
Title3. คัดกรอง HT ในเรือนจำ
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_02_HT_screen;
CREATE TABLE IF NOT EXISTS prison64_02_HT_screen
(SELECT * FROM(SELECT n.HOSPCODE, n.PID, p.CID, p.BIRTH, TIMESTAMPDIFF(YEAR,p.BIRTH,n.DATE_SERV) as AGE, n.DATE_SERV, SBP_1, DBP_1 ,' ' 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 15 AND 120
AND (SBP_1>=50 AND DBP_1>=50)
AND p.CID IN(SELECT CID from prison64_00_prisonerDB)
ORDER BY p.CID,DATE_SERV DESC) dup

GROUP BY CID);

# แปลผล
update prison64_02_HT_screen SET risk='0'
where (SBP_1 > 50 AND SBP_1 < 130) and (DBP_1 > 50 AND DBP_1 < 85);

update prison64_02_HT_screen SET risk='1'
where (SBP_1 BETWEEN 130 AND 139) OR (DBP_1 BETWEEN 85 AND 89);

update prison64_02_HT_screen SET risk='2'
where (SBP_1 BETWEEN 140 AND 179) OR (DBP_1 BETWEEN 90 AND 109);

update prison64_02_HT_screen SET risk='3'
where SBP_1 >=180 OR DBP_1 >=110;

# INDIVIDUAL
SELECT h.hospcode,h.hosname ,p.cid,p.AGE AS age ,p.DATE_SERV as date_serv , CONCAT(SBP_1,'/',DBP_1) as bp
,IF(risk='0','ปกติ ',IF(risk='1','เสี่ยง ',IF(risk='2','สงสัยป่วย',IF(risk='3','ป่วย','นอกเกณฑ์')))) as result
FROM prison64_02_HT_screen p LEFT JOIN prison64_00_prisonerDB h ON p.CID=h.CID
;




#PROVIDER1_SQL#
Script Cron
Force Script Cron0
Active1
Client Office Type
Create Date2020-05-06 09:30:54
Last Update2021-03-25 11:40:01