QOF-64 ส่วนกลาง 03 ANC12 wks
ID | 827 |
---|---|
Parent ID | 61 |
Table Name | qof64_kpi03_anc12wks_v2 |
Title | QOF-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 Cron | 0 |
Active | 1 |
Client Office Type | |
Create Date | 2020-11-10 11:41:09 |
Last Update | 2021-01-21 01:13:26 |
- Home
- About
- Privacy Policy
- Terms of Use
- Contact Us
© 2015. All Rights Reserved.