QOF-64 (2)การตรวจพัฒนาการเด็กอายุ 9, 18, 30, 42 เดือน
ID | 819 |
---|---|
Parent ID | 61 |
Table Name | qof64_kpi02_child_dev |
Title | QOF-64 (2)การตรวจพัฒนาการเด็กอายุ 9, 18, 30, 42 เดือน |
Description | |
Script | #SQL_OPTIONS# PROVIDERS=1 PROVIDER1=43STD PROVIDER1_VALIDATE_TABLES=accident,admission,anc,appointment 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 @start_d:='2020-04-01'; SET @end_d:='2021-03-01'; # กำหนดช่วงวันเกิด ของเด็กที่อยู่ในกลุ่มเป้าหมาย SET @begin_d=DATE_ADD(@start_d,INTERVAL -60 month); DROP TABLE IF EXISTS t_childdev_QOF; CREATE TABLE IF NOT EXISTS t_childdev_QOF( HOSPCODE varchar(5) NOT NULL DEFAULT '', CID varchar(13) NOT NULL DEFAULT '' , FNAME varchar(255) , LNAME varchar(255) , BIRTH date DEFAULT Null , SEX varchar(1) DEFAULT '', TYPEAREA VARCHAR(1) DEFAULT '', instype varchar(3) DEFAULT '', HMain varchar(5) DEFAULT '', HSUB varchar(5) DEFAULT '', AGE_9 int(1) DEFAULT '0', AGE_18 int(1) DEFAULT '0', AGE_30 int(1) DEFAULT '0', AGE_42 int(1) DEFAULT '0', AGE_60 int(1) DEFAULT '0', PRIMARY KEY (`HOSPCODE`,`CID`), KEY (`HOSPCODE`,`CID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; #.ใส่เด็กที่อยู่ในช่วงวันเกิดที่คำนวณแล้ว ยังไม่ตาย เป็นคนไทย รหัส 13 INSERT IGNORE INTO t_childdev_QOF (SELECT p.HOSPCODE,p.CID,p.name,p.LNAME,#DATE_SUB(p.BirthDate,INTERVAL 543 YEAR), p.BIRTH,p.SEX,p.TYPEAREA,null,null,null, #d.MainInScl,d.HMain,d.hsub, 0,0,0,0,0 FROM t_person_cid p #LEFT OUTER JOIN dbpop d on p.CID = d.PID WHERE p.BIRTH BETWEEN @begin_d AND @end_d and p.DISCHARGE='9' AND LEFT(p.CID,6)<>CONCAT('0',p.HOSPCODE) AND p.NATION in(99) #and p.typearea in ('1','3') ); # คำนวณว่หาว่าเด็กจะมีอายุ 9,18,30,42 หรือไม่ UPDATE t_childdev_QOF SET AGE_9=1 WHERE (DATE_ADD(BIRTH,INTERVAL 9 month) BETWEEN @start_d AND @end_d) OR (DATE_ADD(BIRTH,INTERVAL 10 month) BETWEEN @start_d AND @end_d); UPDATE t_childdev_QOF SET AGE_18=1 WHERE (DATE_ADD(BIRTH,INTERVAL 18 month) BETWEEN @start_d AND @end_d) OR (DATE_ADD(BIRTH,INTERVAL 19 month) BETWEEN @start_d AND @end_d); UPDATE t_childdev_QOF SET AGE_30=1 WHERE (DATE_ADD(BIRTH,INTERVAL 30 month) BETWEEN @start_d AND @end_d) OR (DATE_ADD(BIRTH,INTERVAL 31 month) BETWEEN @start_d AND @end_d); UPDATE t_childdev_QOF SET AGE_42=1 WHERE (DATE_ADD(BIRTH,INTERVAL 42 month) BETWEEN @start_d AND @end_d) OR (DATE_ADD(BIRTH,INTERVAL 43 month) BETWEEN @start_d AND @end_d); UPDATE t_childdev_QOF SET AGE_60=1 WHERE (DATE_ADD(BIRTH,INTERVAL 60 month) BETWEEN @start_d AND @end_d) OR (DATE_ADD(BIRTH,INTERVAL 61 month) BETWEEN @start_d AND @end_d); # สร้างตารางรับเด็กเป้าหมาย 9,18,30,42 อาจจะมี 13 หลักซ้ำได้ ถ้าเด็กอายุ 2 ช่วง DROP TABLE IF EXISTS _qof64_kpi02_t_develop; CREATE TABLE IF NOT EXISTS _qof64_kpi02_t_develop( hospcode char(5) NOT NULL DEFAULT '', cid varchar(13) NOT NULL , FNAME varchar(255) , LNAME varchar(255) , sex varchar(1) DEFAULT NULL , birth date DEFAULT NULL, TYPEAREA VARCHAR(1) DEFAULT '', instype CHAR(3), Hmain CHAR(5), hsub CHAR(5), agemonth VARCHAR(3) DEFAULT NULL, date_start date, date_end date, scr_hos char(5), date_serv_first date COMMENT 'วันที่คัดกรองครั้งแรก', sp_first text COMMENT 'รหัสคัดกรองครั้งแรก', PRIMARY KEY (CID,agemonth), KEY cid (`cid`) )ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT IGNORE INTO _qof64_kpi02_t_develop (hospcode,cid,fname,lname,birth,sex,typearea,instype,HMain,hsub,agemonth) ( SELECT p.hospcode,p.cid,p.fname,p.lname,p.birth,p.sex,p.typearea,p.instype,p.hmain,p.hsub,'60' FROM t_childdev_QOF p WHERE AGE_60=1 ); INSERT IGNORE INTO _qof64_kpi02_t_develop (hospcode,cid,fname,lname,birth,sex,typearea,instype,HMain,hsub,agemonth) ( SELECT p.hospcode,p.cid,p.fname,p.lname,p.birth,p.sex,p.typearea,p.instype,p.hmain,p.hsub,'42' FROM t_childdev_QOF p WHERE AGE_42=1 ); INSERT IGNORE INTO _qof64_kpi02_t_develop (hospcode,cid,fname,lname,birth,sex,typearea,instype,HMain,hsub,agemonth) ( SELECT p.hospcode,p.cid,p.fname,p.lname,p.birth,p.sex,p.typearea,p.instype,p.hmain,p.hsub,'30' FROM t_childdev_QOF p WHERE AGE_30=1 ); INSERT IGNORE INTO _qof64_kpi02_t_develop (hospcode,cid,fname,lname,birth,sex,typearea,instype,HMain,hsub,agemonth) ( SELECT p.hospcode,p.cid,p.fname,p.lname,p.birth,p.sex,p.typearea,p.instype,p.hmain,p.hsub,'18' FROM t_childdev_QOF p WHERE AGE_18=1 ); INSERT IGNORE INTO _qof64_kpi02_t_develop (hospcode,cid,fname,lname,birth,sex,typearea,instype,HMain,hsub,agemonth) ( SELECT p.hospcode,p.cid,p.fname,p.lname,p.birth,p.sex,p.typearea,p.instype,p.hmain,p.hsub,'9' FROM t_childdev_QOF p WHERE AGE_9=1 ); INSERT IGNORE INTO _qof64_kpi02_t_develop (hospcode,cid,fname,lname,birth,sex,typearea,instype,HMain,hsub,agemonth) (SELECT p.hospcode,p.cid,p.fname,p.lname,p.birth,p.sex,p.typearea,p.instype,p.hmain,p.hsub ,IF(age_18=1 AND age_9=1 , 18 ,0) FROM t_childdev_QOF p ); INSERT IGNORE INTO _qof64_kpi02_t_develop (hospcode,cid,fname,lname,birth,sex,typearea,instype,HMain,hsub,agemonth) (SELECT p.hospcode,p.cid,p.fname,p.lname,p.birth,p.sex,p.typearea,p.instype,p.hmain,p.hsub ,IF(age_18=1 AND age_9=1 , 9 ,0) FROM t_childdev_QOF p ); #ลบเด็กที่ไม่ตกในช่วง 9 18 30 42 DELETE FROM _qof64_kpi02_t_develop WHERE agemonth=0; #อัพเดทช่วงวันที่ที่ทำแล้วถือว่าได้รับการคัดกรอง UPDATE _qof64_kpi02_t_develop SET date_start=DATE_ADD(BIRTH,INTERVAL 9 month) ,date_end=DATE_ADD(DATE_ADD(BIRTH,INTERVAL 10 month),INTERVAL -1 DAY) WHERE agemonth=9; UPDATE _qof64_kpi02_t_develop SET date_start=DATE_ADD(BIRTH,INTERVAL 18 month) ,date_end=DATE_ADD(DATE_ADD(BIRTH,INTERVAL 19 month),INTERVAL -1 DAY) WHERE agemonth=18; UPDATE _qof64_kpi02_t_develop SET date_start=DATE_ADD(BIRTH,INTERVAL 30 month) ,date_end=DATE_ADD(DATE_ADD(BIRTH,INTERVAL 31 month),INTERVAL -1 DAY) WHERE agemonth=30; UPDATE _qof64_kpi02_t_develop SET date_start=DATE_ADD(BIRTH,INTERVAL 42 month) ,date_end=DATE_ADD(DATE_ADD(BIRTH,INTERVAL 43 month),INTERVAL -1 DAY) WHERE agemonth=42; UPDATE _qof64_kpi02_t_develop SET date_start=DATE_ADD(BIRTH,INTERVAL 60 month) ,date_end=DATE_ADD(DATE_ADD(BIRTH,INTERVAL 61 month),INTERVAL -1 DAY) WHERE agemonth=60; update _qof64_kpi02_t_develop t LEFT OUTER JOIN (SELECT s.HOSPCODE,s.PID,s.SEQ,min(s.DATE_SERV) min_date_serv ,s.SERVPLACE,s.PPSPECIAL,s.PPSPLACE,s.PROVIDER,p.CID,TIMESTAMPDIFF(MONTH,p.BIRTH,DATE_SERV) AS agemonth FROM specialpp s LEFT JOIN person p ON s.HOSPCODE=p.HOSPCODE AND s.PID=p.PID WHERE s.DATE_SERV BETWEEN @start_d AND @end_d AND s.PPSPECIAL in('1B260','1B261','1B262') AND p.DISCHARGE='9' AND LEFT(p.CID,6)<>CONCAT('0',p.HOSPCODE) AND p.NATION ='099' GROUP BY p.cid,s.DATE_SERV ) ss on t.cid = ss.cid and t.agemonth = ss.agemonth and ss.min_date_serv BETWEEN t.date_start and t.date_end SET t.date_serv_first = ss.min_date_serv ,t.scr_hos=ss.PPSPLACE,t.sp_first=ss.PPSPECIAL ; DROP TABLE IF EXISTS t_childdev_QOF; DROP TABLE IF EXISTS tmp_specialpp_qof; SELECT * from _qof64_kpi02_t_develop t where t.date_start BETWEEN @start_d and @end_d ; #PROVIDER1_SQL# |
Script Cron | |
Force Script Cron | 0 |
Active | 1 |
Client Office Type | |
Create Date | 2020-09-22 08:53:25 |
Last Update | 2021-01-21 11:35:25 |
- Home
- About
- Privacy Policy
- Terms of Use
- Contact Us
© 2015. All Rights Reserved.