tmp_anc

``` sql
SET @prov_c := '14';
SET @b_year:='2017';
SET @start_d:=concat(@b_year-1,'1001');
SET @end_d:=concat(@b_year,'0930');
SET @date_3:=concat(@b_year-2,'1001');

DROP TABLE IF EXISTS tmp_anc;
CREATE TABLE IF NOT EXISTS tmp_anc (
PRIMARY KEY (hospcode,pid,date_serv),
KEY (cid),
KEY (hospcode,pid),
KEY (date_serv),
KEY (ga),
KEY (gravida)
)ENGINE=MyISAM AS(
SELECT a.*,p.cid,p.nation,p.birth,p.sex
FROM anc a LEFT JOIN person p ON a.HOSPCODE=p.HOSPCODE AND a.pid=p.pid
WHERE a.date_serv BETWEEN @date_3 AND @end_d
);

DROP TABLE IF EXISTS tmp_labor;
CREATE TABLE IF NOT EXISTS tmp_labor (
PRIMARY KEY (hospcode,pid,gravida,bdate),
KEY (cid),
KEY (hospcode,pid),
KEY (bdate)
) ENGINE=MyISAM AS(
SELECT lb.*,p.cid,p.birth,TIMESTAMPDIFF(year,p.birth,lb.bdate) as age_y
FROM labor lb LEFT JOIN person p ON lb.HOSPCODE=p.HOSPCODE AND lb.pid=p.pid
WHERE lb.bdate BETWEEN @date_3 AND @end_d
);

DROP TABLE IF EXISTS t_labor;
CREATE TABLE IF NOT EXISTS t_labor (
PRIMARY KEY (CID,BDATE)
) ENGINE=MyISAM IGNORE AS
SELECT l.* FROM tmp_labor l INNER JOIN chospital h ON l.hospcode=h.hoscode WHERE h.hostype in(5,6,7,11);

INSERT IGNORE t_labor (SELECT * FROM tmp_labor);

DROP TABLE IF EXISTS t_person_anc;
CREATE TABLE IF NOT EXISTS t_person_anc(
id int(15) NOT NULL AUTO_INCREMENT
,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
,bdate date
,bhosp VARCHAR(5) DEFAULT NULL
,input_bhosp VARCHAR(5) 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
,PRIMARY KEY (id)
,KEY cid (cid)
,KEY (hospcode)
,KEY (pid)
,KEY (typearea)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*GROUP BY CID Gravida */
INSERT IGNORE INTO t_person_anc
(
hospcode,pid,typearea,cid,birth,sex,nation,occupat_new,gravida
)
(
SELECT pe.hospcode,pe.pid,pe.typearea,pe.cid,pe.BIRTH,pe.SEX,pe.NATION,pe.OCCUPATION_NEW,a.GRAVIDA
FROM tmp_anc as a LEFT JOIN t_person_cid as pe ON a.CID=pe.CID
WHERE a.DATE_SERV BETWEEN @date_3 AND @end_d AND LENGTH(pe.cid) = 13
GROUP BY pe.CID,a.GRAVIDA
ORDER BY pe.CID
);
#ครั้งที่ 1 ga ก่อน 12 wks/
UPDATE t_person_anc a INNER JOIN
( SELECT cid,date_serv ,gravida,ancplace,ga,hospcode
FROM tmp_anc WHERE ga <= 12
GROUP BY cid,gravida
) as g ON a.cid=g.cid 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 t_person_anc a INNER JOIN
( SELECT cid,date_serv ,gravida,ancplace,ga,hospcode
FROM tmp_anc WHERE ga BETWEEN 16 AND 20
GROUP BY cid,gravida
) as g ON a.cid=g.cid 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 t_person_anc a INNER JOIN
( SELECT cid,date_serv ,gravida,ancplace,ga,hospcode
FROM tmp_anc WHERE ga BETWEEN 13 AND 19
GROUP BY cid,gravida
) as g ON a.cid=g.cid 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 t_person_anc a INNER JOIN
(
SELECT cid,date_serv ,gravida,ancplace,ga,hospcode
FROM tmp_anc WHERE ga BETWEEN 24 AND 28
GROUP BY cid,gravida
) as g ON a.cid=g.cid 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 t_person_anc a INNER JOIN
(
SELECT cid,date_serv ,gravida,ancplace,ga,hospcode
FROM tmp_anc WHERE ga BETWEEN 20 AND 25
GROUP BY cid,gravida
) as g ON a.cid=g.cid 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 t_person_anc a INNER JOIN
(
SELECT cid,date_serv ,gravida,ancplace,ga,hospcode
FROM tmp_anc WHERE ga BETWEEN 30 AND 34
GROUP BY cid,gravida
) as g ON a.cid=g.cid 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 t_person_anc a INNER JOIN
(
SELECT cid,date_serv ,gravida,ancplace,ga,hospcode
FROM tmp_anc WHERE ga BETWEEN 26 AND 31
GROUP BY cid,gravida
) as g ON a.cid=g.cid 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 t_person_anc a INNER JOIN
(
SELECT cid,date_serv ,gravida,ancplace,ga,hospcode
FROM tmp_anc WHERE ga BETWEEN 36 AND 40
GROUP BY cid,gravida
) as g ON a.cid=g.cid 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;
#ครั้งที่ 5 หากไม่ผ่านตามเกณฑ์คุณภาพก็ให้ใช้ตามเกณฑ์สิทธิประโยชน์/
UPDATE t_person_anc a INNER JOIN
(
SELECT cid,date_serv ,gravida,ancplace,ga,hospcode
FROM tmp_anc WHERE ga BETWEEN 36 AND 40
GROUP BY cid,gravida
) as g ON a.cid=g.cid 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
WHERE ISNULL(a.g5_date) OR ISNULL(a.g5_ga);

UPDATE t_person_anc a INNER JOIN
(
SELECT cid,bdate,gravida,bhosp,hospcode FROM tmp_labor l INNER JOIN chospital h ON l.hospcode=h.hoscode AND h.hostype IN(5,6,7,11,11,12,15)
WHERE bdate BETWEEN @start_d AND @end_d AND BHOSP=HOSPCODE
GROUP BY cid,gravida
) as g ON a.cid=g.cid AND g.gravida=a.gravida
SET a.bdate=g.bdate ,a.bhosp=g.bhosp ,a.input_bhosp=g.hospcode;

UPDATE t_person_anc a INNER JOIN
(
SELECT cid,bdate,gravida,bhosp,hospcode FROM tmp_labor
WHERE bdate BETWEEN @start_d AND @end_d
GROUP BY cid,gravida
) as g ON a.cid=g.cid AND g.gravida=a.gravida
SET a.bdate=g.bdate ,a.bhosp=g.bhosp ,a.input_bhosp=g.hospcode
WHERE ISNULL(a.bdate);

The issue contains a standalone SQL script for tmp_anc, tmp_labor, t_labor, and t_person_anc, but it does not state what should change. Start by identifying the intended outcome and the schema or execution entry point for these queries. Done cannot be determined until the requested behavior and validation criteria are clarified.

Written by the indexing model from the issue text.