• Home
  • My Scripts
  • 4. คัดกรอง มะเร็งเต้านม ในเรือนจำ

4. คัดกรอง มะเร็งเต้านม ในเรือนจำ

Update Delete

ID809
Parent ID59
Table Nameprison64_03_breast_screen
Title4. คัดกรอง มะเร็งเต้านม ในเรือนจำ
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_03_breast_screen;
CREATE TABLE IF NOT EXISTS prison64_03_breast_screen
(SELECT * FROM(SELECT n.HOSPCODE,n.PID,p.CID,n.DATE_SERV,n.PPSPECIAL "code",'specialpp' as "file",p.BIRTH,TIMESTAMPDIFF(YEAR,p.BIRTH,n.DATE_SERV) as AGE
FROM specialpp 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 25 AND 120
AND PPSPECIAL LIKE '1B003%'
AND p.CID IN(SELECT CID from prison64_00_prisonerDB where SEX='2')

UNION

SELECT n.HOSPCODE,n.PID,p.CID,n.DATE_SERV,n.DIAGCODE "code",'diagnosis_opd' as "file",p.BIRTH,TIMESTAMPDIFF(YEAR,p.BIRTH,n.DATE_SERV) as AGE
FROM diagnosis_opd 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 25 AND 120
AND DIAGCODE LIKE 'Z123%'
# เฉพาะผู้หญิง
AND p.CID IN(SELECT CID from prison64_00_prisonerDB where SEX='2')

ORDER BY CID,DATE_SERV DESC,file DESC) dup

GROUP BY CID);

# INDIVIDUAL
SELECT h.hospcode,h.hosname,p.cid,p.AGE as age,p.DATE_SERV as date_serv
,IF(code in('1B0030','1B0031','1B0032','1B0033'),'ปกติ',IF(code in('1B0034','1B0035','1B0036','1B0037'),'ผิดปกติ','ไม่ระบุผล')) as result
FROM prison64_03_breast_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:33:46
Last Update2021-03-25 11:44:09