NHSO_PP60-KPI01-ANC
ID | 763 |
---|---|
Parent ID | 52 |
Table Name | _pp60_kpi01_anc |
Title | NHSO_PP60-KPI01-ANC |
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='2017-04-01'; SET @end_d:='2018-03-31'; DROP TABLE IF EXISTS _zon6_tmp_anc; CREATE TABLE IF NOT EXISTS _zon6_tmp_anc( KEY (cid), KEY (hospcode,pid), KEY (date_serv), KEY (ga), KEY (gravida) )ENGINE=MyISAM AS( SELECT SQL_BIG_RESULT tt.HOSPCODE,tt.PID,tt.SEQ,tt.DATE_SERV,tt.GRAVIDA,tt.ancno,tt.GA,tt.ANCRESULT,tt.ANCPLACE,tt.PROVIDER,tt.D_UPDATE,pe.cid,pe.nation,pe.birth,pe.sex FROM anc tt LEFT JOIN person pe ON tt.HOSPCODE=pe.HOSPCODE AND tt.pid=pe.pid WHERE tt.date_serv BETWEEN @start_d AND @end_d and tt.HOSPCODE=tt.ANCPLACE #check มั๊ย and mod11(pe.CID)=1 ); DROP TABLE IF EXISTS _zone6_person_anc; CREATE TABLE IF NOT EXISTS _zone6_person_anc( hospcode varchar(5) NOT NULL ,pid varchar(15) NOT NULL ,typearea varchar(1) NOT NULL ,cid VARCHAR(13) NOT NULL ,birth date ,sex VARCHAR(1) DEFAULT NULL ,nation VARCHAR(3) DEFAULT NULL ,occupat_new VARCHAR(4) DEFAULT NULL ,gravida VARCHAR(2) DEFAULT NULL ,g1_ga VARCHAR(2) DEFAULT NULL ,g1_date date ,g1_hospcode VARCHAR(5) DEFAULT NULL ,g1_input_hosp VARCHAR(5) DEFAULT NULL ,g2_ga VARCHAR(2) DEFAULT NULL ,g2_date date ,g2_hospcode VARCHAR(5) DEFAULT NULL ,g2_input_hosp VARCHAR(5) DEFAULT NULL ,g3_ga VARCHAR(2) DEFAULT NULL ,g3_date date ,g3_hospcode VARCHAR(5) DEFAULT NULL ,g3_input_hosp VARCHAR(5) DEFAULT NULL ,g4_ga VARCHAR(2) DEFAULT NULL ,g4_date date ,g4_hospcode VARCHAR(5) DEFAULT NULL ,g4_input_hosp VARCHAR(5) DEFAULT NULL ,g5_ga VARCHAR(2) DEFAULT NULL ,g5_date date ,g5_hospcode VARCHAR(5) DEFAULT NULL ,g5_input_hosp VARCHAR(5) DEFAULT NULL ,KEY (hospcode) ,KEY (pid) ,KEY (typearea) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; TRUNCATE TABLE _zone6_person_anc; /*GROUP BY HOSPCODE,pid,Gravida */ INSERT IGNORE INTO _zone6_person_anc ( hospcode,pid,typearea,cid,birth,sex,nation,occupat_new,gravida ) ( SELECT pe.hospcode,pe.pid,pe.check_typearea,pe.cid,pe.BIRTH,pe.SEX,pe.NATION,pe.OCCUPATION_NEW,tt.GRAVIDA FROM _zon6_tmp_anc as tt LEFT JOIN t_person_db as pe ON tt.hospcode=pe.HOSPCODE and tt.pid=pe.PID WHERE LENGTH(pe.cid) = 13 GROUP BY pe.HOSPCODE,pe.pid,tt.GRAVIDA ORDER BY pe.HOSPCODE,pe.pid ); /*ครั้งที่ 1 ga ก่อน 12 wks*/ UPDATE _zone6_person_anc a INNER JOIN ( SELECT hospcode,pid,cid,date_serv ,gravida,ancplace,ga FROM _zon6_tmp_anc WHERE ga <= 12 GROUP BY hospcode,pid,gravida ) as g ON a.hospcode=g.hospcode AND a.pid=g.pid AND g.gravida=a.gravida SET a.g1_ga= g.ga ,a.g1_date=g.date_serv ,a.g1_hospcode =g.ancplace,a.g1_input_hosp= g.hospcode; /*ครั้งที่ 2 ตรวจสอบตามเกณฑ์คุณภาพก่อน*/ UPDATE _zone6_person_anc a INNER JOIN ( SELECT hospcode,pid,cid,date_serv ,gravida,ancplace,ga FROM _zon6_tmp_anc WHERE ga BETWEEN 16 AND 20 GROUP BY hospcode,pid,gravida ) as g ON a.hospcode=g.hospcode AND a.pid=g.pid AND g.gravida=a.gravida SET a.g2_ga= g.ga ,a.g2_date=g.date_serv,a.g2_hospcode =g.ancplace,a.g2_input_hosp= g.hospcode; /*ครั้งที่ 2 หากไม่ผ่านตามเกณฑ์คุณภาพก็ให้ใช้ตามเกณฑ์สิทธิประโยชน์*/ UPDATE _zone6_person_anc a INNER JOIN ( SELECT hospcode,pid,cid,date_serv ,gravida,ancplace,ga FROM _zon6_tmp_anc WHERE ga BETWEEN 13 AND 20 GROUP BY hospcode,pid,gravida ) as g ON a.hospcode=g.hospcode AND a.pid=g.pid AND g.gravida=a.gravida SET a.g2_ga= g.ga ,a.g2_date=g.date_serv,a.g2_hospcode =g.ancplace,a.g2_input_hosp= g.hospcode WHERE ISNULL(a.g2_date) OR ISNULL(a.g2_ga); /*ครั้งที่ 3 ตรวจสอบตามเกณฑ์คุณภาพก่อน*/ UPDATE _zone6_person_anc a INNER JOIN ( SELECT hospcode,pid,cid,date_serv ,gravida,ancplace,ga FROM _zon6_tmp_anc WHERE ga BETWEEN 24 AND 28 GROUP BY hospcode,pid,gravida ) as g ON a.hospcode=g.hospcode AND a.pid=g.pid AND g.gravida=a.gravida SET a.g3_ga= g.ga ,a.g3_date=g.date_serv ,a.g3_hospcode =g.ancplace ,a.g3_input_hosp= g.hospcode; /*ครั้งที่ 3 หากไม่ผ่านตามเกณฑ์คุณภาพก็ให้ใช้ตามเกณฑ์สิทธิประโยชน์*/ UPDATE _zone6_person_anc a INNER JOIN ( SELECT hospcode,pid,cid,date_serv ,gravida,ancplace,ga FROM _zon6_tmp_anc WHERE ga BETWEEN 26 AND 28 GROUP BY hospcode,pid,gravida ) as g ON a.hospcode=g.hospcode AND a.pid=g.pid AND g.gravida=a.gravida SET a.g3_ga= g.ga ,a.g3_date=g.date_serv ,a.g3_hospcode =g.ancplace ,a.g3_input_hosp= g.hospcode WHERE ISNULL(a.g3_date) OR ISNULL(a.g3_ga); /*ครั้งที่ 4 ตรวจสอบตามเกณฑ์คุณภาพก่อน*/ UPDATE _zone6_person_anc a INNER JOIN ( SELECT hospcode,pid,cid,date_serv ,gravida,ancplace,ga FROM _zon6_tmp_anc WHERE ga BETWEEN 30 AND 34 GROUP BY hospcode,pid,gravida ) as g ON a.hospcode=g.hospcode AND a.pid=g.pid AND g.gravida=a.gravida SET a.g4_ga= g.ga ,a.g4_date=g.date_serv ,a.g4_hospcode =g.ancplace ,a.g4_input_hosp= g.hospcode; /*ครั้งที่ 4 หากไม่ผ่านตามเกณฑ์คุณภาพก็ให้ใช้ตามเกณฑ์สิทธิประโยชน์*/ UPDATE _zone6_person_anc a INNER JOIN ( SELECT hospcode,pid,cid,date_serv ,gravida,ancplace,ga FROM _zon6_tmp_anc WHERE ga BETWEEN 29 AND 34 GROUP BY hospcode,pid,gravida ) as g ON a.hospcode=g.hospcode AND a.pid=g.pid AND g.gravida=a.gravida SET a.g4_ga= g.ga ,a.g4_date=g.date_serv ,a.g4_hospcode =g.ancplace ,a.g4_input_hosp= g.hospcode WHERE ISNULL(a.g4_date) OR ISNULL(a.g4_ga); /*ครั้งที่ 5 */ UPDATE _zone6_person_anc a INNER JOIN ( SELECT hospcode,pid,cid,date_serv ,gravida,ancplace,ga FROM _zon6_tmp_anc WHERE ga BETWEEN 36 AND 40 GROUP BY hospcode,pid,gravida ) as g ON a.hospcode=g.hospcode AND a.pid=g.pid AND g.gravida=a.gravida SET a.g5_ga= g.ga ,a.g5_date=g.date_serv ,a.g5_hospcode =g.ancplace ,a.g5_input_hosp= g.hospcode; #ปรับปรุงสิทธิ์จาก DBPOP alter table _zone6_person_anc add maininscl varchar(5) DEFAULT null; UPDATE _zone6_person_anc,dbpop set _zone6_person_anc.maininscl=dbpop.MainInScl WHERE _zone6_person_anc.cid=dbpop.PID; #ดูว่า ฝากครั้งที่2ถึง5 มากี่ครั้งไว้คิดคะแนน alter table _zone6_person_anc add anc2 INTEGER(1) DEFAULT '0'; alter table _zone6_person_anc add anc_check varchar(2) DEFAULT null; update _zone6_person_anc, ( SELECT _zone6_person_anc.hospcode, _zone6_person_anc.pid, _zone6_person_anc.cid, _zone6_person_anc.gravida, sum(case when _zone6_person_anc.g2_date is not null then 1 else 0 end) +sum(case when _zone6_person_anc.g3_date is not null then 1 else 0 end) +sum(case when _zone6_person_anc.g4_date is not null then 1 else 0 end) +sum(case when _zone6_person_anc.g5_date is not null then 1 else 0 end) as anc2 FROM _zone6_person_anc GROUP BY _zone6_person_anc.hospcode, _zone6_person_anc.pid, _zone6_person_anc.cid, _zone6_person_anc.gravida ) as s set _zone6_person_anc.anc2=s.anc2 where _zone6_person_anc.hospcode=s.hospcode and _zone6_person_anc.pid=s.pid AND _zone6_person_anc.cid=s.cid AND _zone6_person_anc.gravida=s.gravida; #ตัดคนที่ซ้ำซ้อน cid ออกไปก่อน โดยสร้างตารางพักข้อมูล แล้วลบข้อมูล DROP TABLE IF EXISTS _zone6_person_anc_dup; CREATE TABLE IF NOT EXISTS _zone6_person_anc_dup ( SELECT _zone6_person_anc.hospcode, _zone6_person_anc.pid, _zone6_person_anc.typearea, _zone6_person_anc.cid, _zone6_person_anc.birth, _zone6_person_anc.sex, _zone6_person_anc.nation, _zone6_person_anc.occupat_new, _zone6_person_anc.gravida, _zone6_person_anc.g1_ga, _zone6_person_anc.g1_date, _zone6_person_anc.g1_hospcode, _zone6_person_anc.g1_input_hosp, _zone6_person_anc.g2_ga, _zone6_person_anc.g2_date, _zone6_person_anc.g2_hospcode, _zone6_person_anc.g2_input_hosp, _zone6_person_anc.g3_ga, _zone6_person_anc.g3_date, _zone6_person_anc.g3_hospcode, _zone6_person_anc.g3_input_hosp, _zone6_person_anc.g4_ga, _zone6_person_anc.g4_date, _zone6_person_anc.g4_hospcode, _zone6_person_anc.g4_input_hosp, _zone6_person_anc.g5_ga, _zone6_person_anc.g5_date, _zone6_person_anc.g5_hospcode, _zone6_person_anc.g5_input_hosp, _zone6_person_anc.maininscl, _zone6_person_anc.anc2, _zone6_person_anc.anc_check FROM _zone6_person_anc WHERE _zone6_person_anc.cid in( SELECT _zone6_person_anc.cid FROM _zone6_person_anc GROUP BY _zone6_person_anc.cid HAVING count(_zone6_person_anc.pid)>1 ) ORDER BY cid ); #ขั้นตอนการลบmarkว่าเรคคอร์ดใหน error ก่อนในfield ชื่อ anc_check ว่า'er'แล้วค่อยลบออก UPDATE _zone6_person_anc as a,_zone6_person_anc_dup as b set a.anc_check='er' WHERE a.hospcode=b.hospcode AND a.pid=b.pid AND a.cid=b.cid AND a.gravida=b.gravida; DELETE FROM _zone6_person_anc WHERE _zone6_person_anc.anc_check='er'; #ตรวจสอบว่าเรคคอร์ดใหนควรpenddind เอาไปสร้างตารางใหม่ แล้วลบออกจากตารางซ้ำ _zone6_person_anc_dup DROP TABLE IF EXISTS _zone6_person_anc_pendding; CREATE TABLE IF NOT EXISTS _zone6_person_anc_pendding ( SELECT _zone6_person_anc_dup.hospcode, _zone6_person_anc_dup.pid, _zone6_person_anc_dup.typearea, _zone6_person_anc_dup.cid, _zone6_person_anc_dup.birth, _zone6_person_anc_dup.sex, _zone6_person_anc_dup.nation, _zone6_person_anc_dup.occupat_new, _zone6_person_anc_dup.gravida, _zone6_person_anc_dup.g1_ga, _zone6_person_anc_dup.g1_date, _zone6_person_anc_dup.g1_hospcode, _zone6_person_anc_dup.g1_input_hosp, _zone6_person_anc_dup.g2_ga, _zone6_person_anc_dup.g2_date, _zone6_person_anc_dup.g2_hospcode, _zone6_person_anc_dup.g2_input_hosp, _zone6_person_anc_dup.g3_ga, _zone6_person_anc_dup.g3_date, _zone6_person_anc_dup.g3_hospcode, _zone6_person_anc_dup.g3_input_hosp, _zone6_person_anc_dup.g4_ga, _zone6_person_anc_dup.g4_date, _zone6_person_anc_dup.g4_hospcode, _zone6_person_anc_dup.g4_input_hosp, _zone6_person_anc_dup.g5_ga, _zone6_person_anc_dup.g5_date, _zone6_person_anc_dup.g5_hospcode, _zone6_person_anc_dup.g5_input_hosp, _zone6_person_anc_dup.maininscl, _zone6_person_anc_dup.anc2, _zone6_person_anc_dup.anc_check FROM _zone6_person_anc_dup LEFT JOIN (SELECT _zone6_person_anc_dup.cid, _zone6_person_anc_dup.hospcode, count(_zone6_person_anc_dup.gravida) as ga FROM _zone6_person_anc_dup GROUP BY _zone6_person_anc_dup.cid, _zone6_person_anc_dup.hospcode) as d on _zone6_person_anc_dup.hospcode=d.hospcode and _zone6_person_anc_dup.cid=d.cid WHERE d.ga>1 ); UPDATE _zone6_person_anc_dup as a,_zone6_person_anc_pendding as b SET a.anc_check='pe' WHERE a.hospcode=b.hospcode AND a.pid=b.pid and a.cid=b.cid and a.gravida=b.gravida; #นำข้อมูลที่สงสัยว่าผิดกลับไปรวมใหม่ตอนทำรายงานค่อยตัดสินใจว่าเอาหรือไม่เอา INSERT INTO _zone6_person_anc ( _zone6_person_anc.hospcode, _zone6_person_anc.pid, _zone6_person_anc.typearea, _zone6_person_anc.cid, _zone6_person_anc.birth, _zone6_person_anc.sex, _zone6_person_anc.nation, _zone6_person_anc.occupat_new, _zone6_person_anc.gravida, _zone6_person_anc.g1_ga, _zone6_person_anc.g1_date, _zone6_person_anc.g1_hospcode, _zone6_person_anc.g1_input_hosp, _zone6_person_anc.g2_ga, _zone6_person_anc.g2_date, _zone6_person_anc.g2_hospcode, _zone6_person_anc.g2_input_hosp, _zone6_person_anc.g3_ga, _zone6_person_anc.g3_date, _zone6_person_anc.g3_hospcode, _zone6_person_anc.g3_input_hosp, _zone6_person_anc.g4_ga, _zone6_person_anc.g4_date, _zone6_person_anc.g4_hospcode, _zone6_person_anc.g4_input_hosp, _zone6_person_anc.g5_ga, _zone6_person_anc.g5_date, _zone6_person_anc.g5_hospcode, _zone6_person_anc.g5_input_hosp, _zone6_person_anc.maininscl, _zone6_person_anc.anc2, _zone6_person_anc.anc_check ) SELECT _zone6_person_anc_dup.hospcode, _zone6_person_anc_dup.pid, _zone6_person_anc_dup.typearea, _zone6_person_anc_dup.cid, _zone6_person_anc_dup.birth, _zone6_person_anc_dup.sex, _zone6_person_anc_dup.nation, _zone6_person_anc_dup.occupat_new, _zone6_person_anc_dup.gravida, _zone6_person_anc_dup.g1_ga, _zone6_person_anc_dup.g1_date, _zone6_person_anc_dup.g1_hospcode, _zone6_person_anc_dup.g1_input_hosp, _zone6_person_anc_dup.g2_ga, _zone6_person_anc_dup.g2_date, _zone6_person_anc_dup.g2_hospcode, _zone6_person_anc_dup.g2_input_hosp, _zone6_person_anc_dup.g3_ga, _zone6_person_anc_dup.g3_date, _zone6_person_anc_dup.g3_hospcode, _zone6_person_anc_dup.g3_input_hosp, _zone6_person_anc_dup.g4_ga, _zone6_person_anc_dup.g4_date, _zone6_person_anc_dup.g4_hospcode, _zone6_person_anc_dup.g4_input_hosp, _zone6_person_anc_dup.g5_ga, _zone6_person_anc_dup.g5_date, _zone6_person_anc_dup.g5_hospcode, _zone6_person_anc_dup.g5_input_hosp, _zone6_person_anc_dup.maininscl, _zone6_person_anc_dup.anc2, _zone6_person_anc_dup.anc_check FROM _zone6_person_anc_dup ; #ลบตารางไม่ใช้ DROP TABLE IF EXISTS _zone6_person_anc_pendding; DROP TABLE IF EXISTS _zon6_tmp_anc; DROP TABLE IF EXISTS _zone6_person_anc_dup; #รายงาน SELECT 'g1_ga' AS id ,a.hospcode ,sum(case when a.g1_ga is not null then 1 else 0 end) as total ,sum(case when a.nation in('99','099') and a.maininscl IN ('WEL','UCS') and a.g1_ga is not null then 1 else 0 end) as uc ,sum(case when a.nation in('99','099') and a.maininscl IN ('BFC','FRG','IOL','L01','L02','L03','L04','L05','L06','L07','L08', 'LBF','LGO','OBV','OFB','OFC','OFL','PBF','PIL','PLB','PLG','POF','POL','PSB','PSI','PSL','PSO','PSS','PVT','SBF','SIF','SIL', 'SLB','SLG','SOB','SOF','SOL','SSI','SSS','STP','VBF','VET','VIL','VIO','VLB','VLG','VOF','VOL','VPB','VPL','VPO','VPS','VPT', 'VSB','VSI','VSL','VSO','VSS') and a.g1_ga is not null then 1 else 0 end) as nonuc ,sum(case when a.nation in('99','099') and a.maininscl is null and a.g1_ga is not null then 1 else 0 end) as other ,sum(case when a.nation not in('99','099') and a.g1_ga is not null then 1 else 0 end) as alien FROM _zone6_person_anc as a WHERE a.anc_check is NULL or a.anc_check<>'pe' GROUP BY a.hospcode UNION SELECT 'anc2' AS id ,a.hospcode ,sum(case when a.anc2 is not null then 1 else 0 end) as total_person ,sum(case when a.nation in('99','099') and a.maininscl IN ('WEL','UCS') and a.anc2 is not null then a.anc2 else 0 end) as uc ,sum(case when a.nation in('99','099') and a.maininscl IN ('BFC','FRG','IOL','L01','L02','L03','L04','L05','L06','L07','L08', 'LBF','LGO','OBV','OFB','OFC','OFL','PBF','PIL','PLB','PLG','POF','POL','PSB','PSI','PSL','PSO','PSS','PVT','SBF','SIF','SIL', 'SLB','SLG','SOB','SOF','SOL','SSI','SSS','STP','VBF','VET','VIL','VIO','VLB','VLG','VOF','VOL','VPB','VPL','VPO','VPS','VPT', 'VSB','VSI','VSL','VSO','VSS') and a.anc2 is not null then a.anc2 else 0 end) as nonuc ,sum(case when a.nation in('99','099') and a.maininscl is null and a.anc2 is not null then a.anc2 else 0 end) as other ,sum(case when a.nation not in('99','099') and a.anc2 is not null then a.anc2 else 0 end) as alien FROM _zone6_person_anc as a WHERE a.anc_check is NULL or a.anc_check<>'pe' GROUP BY a.hospcode; #COMPLETE #PROVIDER1_SQL# |
Script Cron | |
Force Script Cron | 0 |
Active | 1 |
Client Office Type | |
Create Date | 2017-08-15 04:12:06 |
Last Update | 2018-05-18 11:28:32 |
- Home
- About
- Privacy Policy
- Terms of Use
- Contact Us
© 2015. All Rights Reserved.