• Home
  • My Scripts
  • QOF-64 (2)การตรวจพัฒนาการเด็กอายุ 9, 18, 30, 42 เดือน

QOF-64 (2)การตรวจพัฒนาการเด็กอายุ 9, 18, 30, 42 เดือน

Update Delete

ID819
Parent ID61
Table Nameqof64_kpi02_child_dev
TitleQOF-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 Cron0
Active1
Client Office Type
Create Date2020-09-22 08:53:25
Last Update2021-01-21 11:35:25