DROP TABLES IF EXISTS tmp_diag_opd_pap_qof;
        CREATE TABLE IF NOT EXISTS tmp_diag_opd_pap_qof (
        KEY(cid),KEY(hospcode),KEY(pid),KEY(seq),KEY(date_serv),
        KEY(diagcode),KEY(diagtype),KEY(hospcode,pid,seq)
        ) ENGINE=MyISAM  AS(
        SELECT SQL_BIG_RESULT 
          o.HOSPCODE,o.DATE_SERV,o.DIAGCODE,p.CID,o.pid,o.seq,o.DIAGTYPE
        FROM
         diagnosis_opd o 
         LEFT JOIN t_person_db p ON o.HOSPCODE=p.HOSPCODE AND o.PID=p.PID
        WHERE DATE_SERV BETWEEN '20150401' AND '20200331' and o.diagcode in('Z014','Z124')
        );
        DROP TABLE IF EXISTS tmp_specialpp_pap_qof;
        CREATE TABLE IF NOT EXISTS tmp_specialpp_pap_qof(
        PRIMARY KEY (HOSPCODE,PID,DATE_SERV,PPSPECIAL) 
        ,KEY (cid)
        ,KEY (hospcode,pid)
        ,KEY (date_serv)
        ,KEY (ppspecial)
        )(
        SELECT 
        s.HOSPCODE,s.PID,s.DATE_SERV,s.PPSPECIAL,p.CID
        FROM specialpp s LEFT JOIN person p ON s.HOSPCODE=p.HOSPCODE AND s.PID=p.PID
        WHERE DATE_SERV BETWEEN '20150401' AND '20200331' and 
        (LEFT(s.PPSPECIAL,4) in ('1B30','1B40')  OR LEFT(s.PPSPECIAL,5) = '1B004'));

DROP TABLE IF EXISTS `tt_qof_pap_63`;
CREATE TABLE `tt_qof_pap_63` (
  `chwcode` varchar(2) DEFAULT NULL,
  `HOSPCODE` varchar(5) NOT NULL DEFAULT '',
  `target` bigint(21) NOT NULL DEFAULT '0',
  `result` decimal(32,0) DEFAULT NULL,
  `D_UPDATE` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT IGNORE INTO tt_qof_pap_63
select (SELECT provincecode FROM hdc.sys_config LIMIT 1) as chwcode,s.HOSPCODE,COUNT(s.cid) as target,sum(s.val) as result,
DATE_FORMAT(NOW(),'%Y-%m-%d %T') as D_UPDATE
 from

        (SELECT
           p.HOSPCODE,chospital.distcode,p.cid  ,IF(max(d.DATE_SERV) is null and max(s.DATE_SERV) is null,0,1) as val
          FROM
           t_person_cid p  
           left join chospital on p.hospcode=chospital.hoscode 
              LEFT JOIN tmp_diag_opd_pap_qof d on p.cid = d.CID
           LEFT JOIN tmp_specialpp_pap_qof s on p.cid = s.cid 
              
          WHERE
           p.birth BETWEEN '19600401' AND '19890331'
           AND p.sex='2' AND p.DISCHARGE='9' AND p.nation= '099'
           AND p.check_typearea in('1','3')  AND left(p.CID,1) not in('0','9','6','7')
           
        GROUP BY p.cid
        ORDER BY p.HOSPCODE)s
        GROUP BY s.HOSPCODE;