QOF-64 ส่วนกลาง 03 ANC12 wks

Update Delete

ID827
Parent ID61
Table Nameqof64_kpi03_anc12wks_v2
TitleQOF-64 ส่วนกลาง 03 ANC12 wks
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;

# ตัวชี้วัด QOF03_ANC ครั้งแรกต่ำกว่า 12 สัปดาห์
#SET @prov_c := (SELECT provincecode FROM sys_config LIMIT 1);
#SET @b_year :=(SELECT yearprocess FROM pk_byear LIMIT 1);
SET @start_d1:=concat('2019-01-01');
SET @start_d:=concat('2020-04-01');
SET @end_d:=concat('2021-03-31');

#สร้างเป้าหมายหญิงตั้งครรภ์
DROP TABLE IF EXISTS _tmp_person_anc12;
CREATE TABLE IF NOT EXISTS _tmp_person_anc12(PRIMARY KEY(CID))
ENGINE=MyISAM AS
(
SELECT a.HOSPCODE,a.PID,a.CID,CONCAT(a.`NAME`,' ',a.LNAME) AS 'PNAME',a.BIRTH,a.age_y,a.NATION,a.TYPEAREA,a.vhid,
#โอ๋เพิ่มให้รู้ว่าเป้าหมายมายังงั้ย
GROUP_CONCAT(b.HOSPCODE,' ' ORDER BY b.DATE_SERV) AS 'Taget_hosp_input',
GROUP_CONCAT(b.SEQ,' ' ORDER BY b.DATE_SERV ) AS 'Taget_SEQ',
GROUP_CONCAT(b.GRAVIDA,' ' ORDER BY b.DATE_SERV) AS 'Taget_GRAVIDA',
GROUP_CONCAT(b.ANCNO,' ' ORDER BY b.DATE_SERV) AS 'Taget_ANCNO',
GROUP_CONCAT(b.GA,' ' ORDER BY b.DATE_SERV) AS 'Taget_GA',
GROUP_CONCAT(b.ANCPLACE,' ' ORDER BY b.DATE_SERV) AS 'Taget_ANCPLACE',
GROUP_CONCAT(b.DATE_SERV,' ' ORDER BY b.DATE_SERV) AS 'Taget_DATE_SERV'
FROM t_person_cid a
INNER JOIN anc b ON (a.HOSPCODE=b.HOSPCODE AND a.PID=b.PID)
WHERE a.TYPEAREA IN ('1','2','3','4')
AND b.DATE_SERV BETWEEN @start_d AND @end_d
AND a.NATION = '099'
AND a.DISCHARGE = '9'
GROUP BY a.CID
ORDER BY a.HOSPCODE,a.PID
);

ALTER TABLE _tmp_person_anc12 ADD COLUMN Maininscl VARCHAR(3);
ALTER TABLE _tmp_person_anc12 ADD COLUMN Hmain VARCHAR(5);
ALTER TABLE _tmp_person_anc12 ADD COLUMN Hsub VARCHAR(5);

#สร้างตารางข้อมูลจาก anc 12 week และ group concat ให้เหลือ 1 record
DROP TABLE IF EXISTS _tmp_anc12week;
CREATE TABLE IF NOT EXISTS _tmp_anc12week(PRIMARY KEY(CID))
ENGINE=MyISAM AS
(
SELECT b.CID,
GROUP_CONCAT(a.HOSPCODE,' ' ORDER BY a.DATE_SERV) AS 'hosp_input',
GROUP_CONCAT(a.SEQ,' ' ORDER BY a.DATE_SERV ) AS 'SEQ',
GROUP_CONCAT(a.GRAVIDA,' ' ORDER BY a.DATE_SERV) AS 'GRAVIDA',
GROUP_CONCAT(a.ANCNO,' ' ORDER BY a.DATE_SERV) AS 'ANCNO',
GROUP_CONCAT(a.GA,' ' ORDER BY a.DATE_SERV) AS 'GA',
GROUP_CONCAT(a.ANCPLACE,' ' ORDER BY a.DATE_SERV) AS 'ANCPLACE',
GROUP_CONCAT(a.DATE_SERV,' ' ORDER BY a.DATE_SERV) AS 'DATE_SERV'
FROM anc a
INNER JOIN person b ON (a.hospcode=b.hospcode AND a.pid = b.pid)
WHERE a.DATE_SERV BETWEEN @start_d1 AND @end_d
AND GA <= 12
AND nation = '099'
GROUP BY b.CID
ORDER BY a.DATE_SERV
);

#เอาผลงานมา join กับคน
DROP TABLE IF EXISTS qof_kpi03;
CREATE TABLE IF NOT EXISTS qof_kpi03(PRIMARY KEY(CID))
ENGINE=MyISAM AS
(
SELECT
a.HOSPCODE,a.PID,a.CID,a.PNAME,a.BIRTH,a.age_y,a.NATION,a.TYPEAREA,
a.vhid,a.Maininscl,a.Hmain,a.Hsub,
#โอ๋ขอเพิ่มไว้Checkเป้าหมาย
a.Taget_hosp_input,a.Taget_SEQ,a.Taget_GRAVIDA,
a.Taget_ANCNO,a.Taget_GA,a.Taget_ANCPLACE,a.Taget_DATE_SERV,
b.hosp_input,b.SEQ,b.GRAVIDA,
b.ANCNO,b.GA,b.ANCPLACE,b.DATE_SERV
FROM _tmp_person_anc12 a
#นับ gravida ครั้งหลังสุดเชือม กับบุคคลที and ในช่วงเวลา สปสช.
LEFT OUTER JOIN _tmp_anc12week b ON a.CID = b.CID AND RIGHT(trim(a.Taget_GRAVIDA),1) = right(trim(b.GRAVIDA),1)
);

#ลบตาราง tmp ออกทั้งหมด
DROP TABLE IF EXISTS _tmp_person_anc12;
DROP TABLE IF EXISTS _tmp_anc12week;
#
#นำตาราง dbpop มาปรับในฐานข้อมูล
UPDATE qof_kpi03 a,dbpop b
SET a.Maininscl=b.MainInScl, a.Hmain=b.HMain, a.Hsub=b.Hsub
WHERE a.cid=b.PID;

SELECT * FROM qof_kpi03;

#PROVIDER1_SQL#
Script Cron
Force Script Cron0
Active1
Client Office Type
Create Date2020-11-10 11:41:09
Last Update2021-01-21 01:13:26