SET @provis=(SELECT provincecode FROM hdc.sys_config LIMIT 1);
SET @d1='2019-04-01';
SET @d2='2020-03-31';

DROP TABLE IF EXISTS `tt_qof_dm_63`;
CREATE TABLE `tt_qof_dm_63` (
  `chwcode` varchar(255) DEFAULT NULL,
  `HOSPCODE` varchar(255) DEFAULT NULL,
  `B` varchar(255) DEFAULT NULL,
  `A` varchar(255) DEFAULT NULL,
  `C` varchar(255) DEFAULT NULL,
  `D_UPDATE` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT IGNORE INTO tt_qof_dm_63
select 
@provis as chwcode,
summary.HOSPCODE,
summary.B,
summary.A,
round(summary.A*100/summary.B,2) as C,
DATE_FORMAT(NOW(),'%Y-%m-%d %T') as D_UPDATE

from(
	SELECT
		t_person_cid.HOSPCODE,
		count(distinct t_person_cid.CID) as B,
		count(distinct if(sc.cid is not null,sc.cid,null)) as A
	FROM
	t_person_cid
	#INNER JOIN card ON t_person_cid.HOSPCODE = card.HOSPCODE AND t_person_cid.PID = card.PID
	LEFT JOIN (
		select t_dmht.cid
		from t_dmht
		where t_dmht.min_date_dx_dm<@d1 and t_dmht.type_dx in('02','03')
	) reg on t_person_cid.CID=reg.cid
	LEFT JOIN (
		select person.cid
		from person
		inner join ncdscreen on person.pid=ncdscreen.pid and person.hospcode=ncdscreen.hospcode
		where ncdscreen.date_serv between @d1 and @d2 AND ncdscreen.BSTEST in(1,2,3,4) AND ncdscreen.BSLEVEL>0
		group by person.cid
	) sc on t_person_cid.CID=sc.cid
	WHERE t_person_cid.NATION='099'
	AND left(t_person_cid.CID,1) not in('0','9','6','7')
	AND t_person_cid.DISCHARGE=9
	AND t_person_cid.TYPEAREA in(1,3)
	AND t_person_cid.BIRTH BETWEEN subdate(@d1,INTERVAL 74 year) AND subdate(@d2,INTERVAL 35 YEAR)
	AND LENGTH(t_person_cid.CID)=13
	AND reg.cid is null
	GROUP BY t_person_cid.HOSPCODE
) summary
INNER JOIN chospital on summary.HOSPCODE=chospital.hoscode 
WHERE chospital.hdc_regist=1