t_dmht_nhso64 ควบคุม DM/HT

Update Delete

ID764
Parent ID51
Table Namet_dmht_nhso64
Title t_dmht_nhso64 ควบคุม DM/HT
Description
Script#SQL_OPTIONS#
PROVIDERS=1
PROVIDER1=43STD
PROVIDER1_VALIDATE_TABLES=labfu,t_chronic,chronicfu,specialpp,tmp_chronic,clabtest_new
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 session group_concat_max_len=15000;
SET @b_year=2020; #2563
SET @start_d=concat(@b_year-2,'0401'); # 1 เม.ย. 2561
SET @start_d2=concat(@b_year-1,'0401'); # 1 เม.ย. 2562
SET @end_d=concat(@b_year,'0331'); # 31 มี.ค. 2563

DROP TABLES IF EXISTS tmp_labfu_nhso ;
CREATE TABLE IF NOT EXISTS tmp_labfu_nhso (
`HOSPCODE` varchar(5) NOT NULL,
`PID` varchar(15) NOT NULL,
`SEQ` varchar(16) NOT NULL,
`DATE_SERV` date NOT NULL,
`LABTEST` varchar(7) NOT NULL DEFAULT '',
`LABRESULT` double(6,2) NOT NULL,
`D_UPDATE` datetime NOT NULL,
`LABPLACE` varchar(5) DEFAULT NULL,
`CID` varchar(13) DEFAULT NULL,
`HDC_DATE` datetime DEFAULT NULL,
`LABTEST_SEND` varchar(7) NOT NULL DEFAULT '',
`LABTEST_NEW` varchar(7) DEFAULT NULL,
PRIMARY KEY (HOSPCODE,PID,SEQ,LABTEST_SEND),
KEY (HOSPCODE,PID,SEQ,LABTEST),
KEY (hospcode,pid),KEY (cid),
KEY (date_serv),KEY (labtest),KEY (labresult),
KEY (LABTEST_SEND), KEY (LABTEST_NEW)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

SET @s:=CONCAT('INSERT IGNORE INTO tmp_labfu_nhso (HOSPCODE,PID,SEQ,DATE_SERV,LABTEST_SEND,LABRESULT,D_UPDATE,CID,LABPLACE,HDC_DATE)
(SELECT SQL_BIG_RESULT
l.HOSPCODE,l.PID,l.SEQ,l.DATE_SERV,l.LABTEST,l.LABRESULT,l.D_UPDATE,p.CID,LABPLACE,HDC_DATE
FROM
labfu AS l INNER JOIN t_person_db p ON l.HOSPCODE=p.HOSPCODE AND l.PID=p.PID
WHERE DATE_SERV BETWEEN @start_d AND @end_d
GROUP BY HOSPCODE,PID,SEQ,LABTEST
)');

PREPARE stmt FROM @s;
EXECUTE stmt;

UPDATE tmp_labfu_nhso l INNER JOIN clabtest_new c ON l.LABTEST_SEND=c.`code`
SET l.LABTEST_NEW=c.`code`
WHERE LENGTH(TRIM(l.LABTEST_SEND)) =7;

UPDATE tmp_labfu_nhso l INNER JOIN clabtest_new c ON l.LABTEST_SEND=c.old_code
SET l.LABTEST_NEW=c.`code`
WHERE LENGTH(TRIM(l.LABTEST_SEND)) =2;

UPDATE tmp_labfu_nhso l SET l.LABTEST=l.LABTEST_SEND;

SET @table1:='chronicfu';
SET @filed1:=(SELECT GROUP_CONCAT(CONCAT('t.',c.COLUMN_NAME))
FROM information_schema.`COLUMNS` c
WHERE c.TABLE_SCHEMA='hdc' AND c.TABLE_NAME=@table1
AND c.COLUMN_NAME !='cid');

DROP TABLE IF EXISTS tmp_chronicfu_nhso ;
SET @s:=CONCAT('CREATE TABLE IF NOT EXISTS tmp_chronicfu_nhso (
PRIMARY KEY (HOSPCODE,PID,SEQ),
KEY (hospcode),KEY (pid),KEY (seq),KEY (cid),KEY (date_serv),
KEY (sbp),KEY (dbp),KEY (foot),KEY (retina)
) ENGINE=MyISAM AS SELECT SQL_BIG_RESULT ', @filed1, '
,p.CID
FROM ', @table1,' AS t INNER JOIN person p ON t.HOSPCODE=p.HOSPCODE AND t.pid=p.pid
WHERE t.DATE_SERV BETWEEN @start_d AND @end_d
GROUP BY t.HOSPCODE,t.PID,t.SEQ ');
PREPARE stmt FROM @s;
EXECUTE stmt;

DROP TABLE IF EXISTS t_chronicfu_nhso;
CREATE TABLE IF NOT EXISTS t_chronicfu_nhso(
hospcode VARCHAR(5) NOT NULL,
pid VARCHAR(15) NOT NULL,
cid VARCHAR(13) NOT NULL,
ld_bp1 date DEFAULT NULL,
ld_bp2 date DEFAULT NULL,
sbp_1 VARCHAR(10) DEFAULT NULL,
dbp_1 VARCHAR(10) DEFAULT NULL,
sbp_2 VARCHAR(10) DEFAULT NULL,
dbp_2 VARCHAR(10) DEFAULT NULL,
ld_hba1c date DEFAULT NULL,
hba1c VARCHAR(10) DEFAULT NULL,
ld_foot date DEFAULT NULL ,
foot VARCHAR(10) DEFAULT NULL,
ld_retina date DEFAULT NULL,
retina VARCHAR(10) DEFAULT NULL ,
complication_dm text,
control_dm int(1) DEFAULT '0',
control_ht int(1) DEFAULT '0',
`ld_weight` date,
`weight` int(9) NOT NULL,
`ld_height` date,
`height` int(9) NOT NULL,
`ld_waist_cm` date,
`waist_cm` int(9) NOT NULL,
`ld_ldl` date,
`ldl` double(6,2) NOT NULL,
`visit` int(3) NOT NULL DEFAULT 0,
PRIMARY KEY (cid,hospcode),
KEY (cid),KEY (hospcode),KEY (hospcode,pid),KEY (ld_bp1),KEY (ld_bp2),
KEY (ld_hba1c),KEY (ld_foot),KEY (ld_retina),KEY (ld_weight),
KEY (ld_height),KEY (ld_ldl)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 IGNORE AS
(
SELECT hospcode,pid,cid
,null as ld_bp1,null as sbp_1 ,null as dbp_1
,null as ld_bp2,null as sbp_2,null as dbp_2
,null as ld_hba1c,null as hba1c
,null as ld_foot,null as foot
,null as ld_retina,null as retina
,null as complication_dm
,0 as control_dm ,0 as control_ht
,null as ld_weight, 0 as weight
,null as ld_height,0 as height
,null as ld_waist_cm,0 as waist_cm
,null as ld_ldl , 0 as ldl
,COUNT(DISTINCT CONCAT(HOSPCODE,'-',cid,'-',seq)) as visit
FROM tmp_chronicfu_nhso
WHERE LENGTH(cid)=13 AND DATE_SERV BETWEEN @start_d2 AND @end_d
GROUP BY HOSPCODE,cid
);

UPDATE t_chronicfu_nhso f INNER JOIN (
SELECT hospcode , pid
,SUBSTR(GROUP_CONCAT(date_serv ORDER BY date_serv DESC),1,10) date_serv
,IF(INSTR(GROUP_CONCAT(labresult ORDER BY date_serv DESC),','),
SUBSTR(GROUP_CONCAT(labresult ORDER BY date_serv DESC),1,
instr(GROUP_CONCAT(labresult ORDER BY date_serv DESC),',' )-1),
GROUP_CONCAT(labresult ORDER BY date_serv DESC)) labresult
FROM tmp_labfu_nhso WHERE labtest in('0531601')
GROUP BY hospcode,PID
) l ON f.hospcode=l.hospcode AND f.pid=l.pid
SET f.ld_hba1c=l.date_serv,f.hba1c=l.LABRESULT;

#complication
UPDATE t_chronicfu_nhso t INNER JOIN (
SELECT cid,GROUP_CONCAT(DISTINCT chronic ORDER BY chronic) g_code
FROM tmp_chronic
WHERE
SUBSTR(chronic,1,3) BETWEEN 'I20' AND 'I25'
OR SUBSTR(chronic,1,3) BETWEEN 'I60' AND 'I69'
OR SUBSTR(chronic,1,3) BETWEEN 'G40' AND 'G41'
OR SUBSTR(chronic,1,4) BETWEEN 'N184' AND 'N185'
OR SUBSTR(chronic,1,3) IN('I50')
GROUP BY cid
) d ON t.cid=d.cid
SET complication_dm=d.g_code;


UPDATE t_chronicfu_nhso SET control_dm='1'
WHERE ld_hba1c BETWEEN @start_d2 AND @end_d
AND ( ( ( LENGTH(TRIM(complication_dm))=0 OR ISNULL(complication_dm)) AND hba1c >0 AND hba1c <7)
OR ( LENGTH(TRIM(complication_dm))>0 AND hba1c >0 AND hba1c <8)
) ;

UPDATE t_chronicfu_nhso d ,
(SELECT c.HOSPCODE, c.PID
,SUBSTRING_INDEX(GROUP_CONCAT(DATE_SERV ORDER BY date_serv DESC),',',1) DATE_SERV
,SUBSTRING_INDEX(GROUP_CONCAT(SBP ORDER BY date_serv DESC),',',1) SBP
,SUBSTRING_INDEX(GROUP_CONCAT(DBP ORDER BY date_serv DESC),',',1) DBP

,IF(LENGTH(GROUP_CONCAT(DATE_SERV ORDER BY date_serv DESC))
- LENGTH(REPLACE(GROUP_CONCAT(DATE_SERV ORDER BY date_serv DESC), ',', '')) >0
,SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(DATE_SERV ORDER BY date_serv DESC), ',', 2),',',-1),NULL) as DATE_SERV1
,IF(LENGTH(GROUP_CONCAT(SBP ORDER BY date_serv DESC))
- LENGTH(REPLACE(GROUP_CONCAT(SBP ORDER BY date_serv DESC), ',', '')) >0
,SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(SBP ORDER BY date_serv DESC), ',', 2),',',-1),NULL) as SBP1
,IF(LENGTH(GROUP_CONCAT(DBP ORDER BY date_serv DESC))
- LENGTH(REPLACE(GROUP_CONCAT(DBP ORDER BY date_serv DESC), ',', '')) >0
,SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(DBP ORDER BY date_serv DESC), ',', 2),',',-1),NULL) as DBP1
FROM tmp_chronicfu_nhso c
WHERE c.SBP >0 AND c.DBP>0
GROUP BY hospcode,PID) as t
SET ld_bp1 =t.DATE_SERV,sbp_1 =t.SBP,dbp_1 =t.DBP
, ld_bp2 =t.DATE_SERV1,sbp_2 =t.SBP1,dbp_2 =t.DBP1
WHERE d.HOSPCODE=t.HOSPCODE AND d.pid=t.pid ;

UPDATE t_chronicfu_nhso SET control_ht ='1' WHERE
(sbp_1 >= 50 AND sbp_1 <= 139) AND (sbp_2 >= 50 AND sbp_2 <=139 )
AND (dbp_1 >= 50 AND dbp_1 <= 89) AND (dbp_2 >= 50 AND dbp_2 <= 89) AND ld_bp1 BETWEEN @start_d2 AND @end_d AND ld_bp2 BETWEEN @start_d2 AND @end_d ;

UPDATE t_chronicfu_nhso f INNER JOIN (
SELECT hospcode , pid
,SUBSTR(GROUP_CONCAT(date_serv ORDER BY date_serv DESC),1,10) date_serv
,IF(INSTR(GROUP_CONCAT(foot ORDER BY date_serv DESC),','),
SUBSTR(GROUP_CONCAT(foot ORDER BY date_serv DESC),1,
instr(GROUP_CONCAT(foot ORDER BY date_serv DESC),',' )-1),
GROUP_CONCAT(foot ORDER BY date_serv DESC)) foot
FROM tmp_chronicfu_nhso WHERE foot in(1,3)
GROUP BY hospcode,PID
) l ON f.hospcode=l.hospcode AND f.pid=l.pid
SET f.ld_foot=l.date_serv, f.foot=l.foot;

UPDATE t_chronicfu_nhso f INNER JOIN (
SELECT hospcode , pid
,SUBSTR(GROUP_CONCAT(date_serv ORDER BY date_serv DESC),1,10) date_serv
,IF(INSTR(GROUP_CONCAT(retina ORDER BY date_serv DESC),','),
SUBSTR(GROUP_CONCAT(retina ORDER BY date_serv DESC),1,
instr(GROUP_CONCAT(retina ORDER BY date_serv DESC),',' )-1),
GROUP_CONCAT(retina ORDER BY date_serv DESC)) retina
FROM tmp_chronicfu_nhso WHERE retina in(1,2,3,4)
GROUP BY hospcode,PID
) l ON f.hospcode=l.hospcode AND f.pid=l.pid
SET f.ld_retina=l.date_serv,f.retina=l.retina;

UPDATE t_chronicfu_nhso f INNER JOIN (
SELECT hospcode , pid
,SUBSTR(GROUP_CONCAT(date_serv ORDER BY date_serv DESC),1,10) date_serv
,IF(INSTR(GROUP_CONCAT(height ORDER BY date_serv DESC),','),
SUBSTR(GROUP_CONCAT(height ORDER BY date_serv DESC),1,
instr(GROUP_CONCAT(height ORDER BY date_serv DESC),',' )-1),
GROUP_CONCAT(height ORDER BY date_serv DESC)) height
FROM tmp_chronicfu_nhso WHERE height >0
GROUP BY hospcode,PID
) l ON f.hospcode=l.hospcode AND f.pid=l.pid
SET f.ld_height=l.date_serv,f.height=l.height;


UPDATE t_chronicfu_nhso f INNER JOIN (
SELECT hospcode , pid
,SUBSTR(GROUP_CONCAT(date_serv ORDER BY date_serv DESC),1,10) date_serv
,IF(INSTR(GROUP_CONCAT(weight ORDER BY date_serv DESC),','),
SUBSTR(GROUP_CONCAT(weight ORDER BY date_serv DESC),1,
instr(GROUP_CONCAT(weight ORDER BY date_serv DESC),',' )-1),
GROUP_CONCAT(weight ORDER BY date_serv DESC)) weight
FROM tmp_chronicfu_nhso WHERE weight >0
GROUP BY hospcode,PID
) l ON f.hospcode=l.hospcode AND f.pid=l.pid
SET f.ld_weight=l.date_serv,f.weight=l.weight;

UPDATE t_chronicfu_nhso f INNER JOIN (
SELECT hospcode , pid
,SUBSTR(GROUP_CONCAT(date_serv ORDER BY date_serv DESC),1,10) date_serv
,IF(INSTR(GROUP_CONCAT(WAIST_CM ORDER BY date_serv DESC),','),
SUBSTR(GROUP_CONCAT(WAIST_CM ORDER BY date_serv DESC),1,
instr(GROUP_CONCAT(WAIST_CM ORDER BY date_serv DESC),',' )-1),
GROUP_CONCAT(WAIST_CM ORDER BY date_serv DESC)) WAIST_CM
FROM tmp_chronicfu_nhso WHERE WAIST_CM >0
GROUP BY hospcode,PID
) l ON f.hospcode=l.hospcode AND f.pid=l.pid
SET f.ld_WAIST_CM=l.date_serv, f.WAIST_CM=l.WAIST_CM;

UPDATE t_chronicfu_nhso f INNER JOIN (
SELECT hospcode , pid
,SUBSTR(GROUP_CONCAT(date_serv ORDER BY date_serv DESC),1,10) date_serv
,IF(INSTR(GROUP_CONCAT(labresult ORDER BY date_serv DESC),','),
SUBSTR(GROUP_CONCAT(labresult ORDER BY date_serv DESC),1,
instr(GROUP_CONCAT(labresult ORDER BY date_serv DESC),',' )-1),
GROUP_CONCAT(labresult ORDER BY date_serv DESC)) labresult
FROM tmp_labfu_nhso WHERE labtest IN('0541402')
GROUP BY hospcode,PID
) l ON f.hospcode=l.hospcode AND f.pid=l.pid
SET f.ld_ldl=l.date_serv,f.ldl=l.labresult ;

DROP TABLES IF EXISTS t_dmht_nhso63;
CREATE TABLE IF NOT EXISTS t_dmht_nhso63 (
id int(15) NOT NULL AUTO_INCREMENT
,hospcode VARCHAR(5) DEFAULT NULL
,pid VARCHAR(15) DEFAULT NULL
,vhid VARCHAR(8) DEFAULT NULL
,typearea VARCHAR(1) DEFAULT NULL
,cid VARCHAR(13) NOT NULL
,birth date
,age_y INT(3) DEFAULT 0
,groupcode1560 VARCHAR(100) DEFAULT NULL
,groupname1560 VARCHAR(100) DEFAULT NULL
,sex VARCHAR(1) DEFAULT NULL
,nation VARCHAR(3) DEFAULT NULL
,input_hosp VARCHAR(255) DEFAULT NULL
,source_tb VARCHAR(255) DEFAULT NULL
,mix_dx VARCHAR(255) DEFAULT NULL
,t_mix_dx VARCHAR(255) DEFAULT NULL
,type_dx VARCHAR(2) DEFAULT NULL
,date_dx VARCHAR(255) DEFAULT NULL
,hosp_dx varchar(255) DEFAULT NULL
,minscl VARCHAR(5) DEFAULT NULL
,inscl VARCHAR(3) DEFAULT NULL
,ld_hba1c date DEFAULT NULL
,rs_hba1c VARCHAR(10) DEFAULT NULL
,ih_hba1c VARCHAR(5) DEFAULT NULL
,ld_fpg1 date DEFAULT NULL
,rs_fpg1 VARCHAR(10) DEFAULT NULL
,ih_fpg1 VARCHAR(5) DEFAULT NULL
,ld_fpg2 date DEFAULT NULL
,rs_fpg2 VARCHAR(10) DEFAULT NULL
,ih_fpg2 VARCHAR(5) DEFAULT NULL
,ld_fpg3 date DEFAULT NULL
,rs_fpg3 VARCHAR(10) DEFAULT NULL
,ih_fpg3 VARCHAR(5) DEFAULT NULL
,ld_creatinine date DEFAULT NULL
,rs_creatinine VARCHAR(10) DEFAULT NULL
,ih_creatinine VARCHAR(5) DEFAULT NULL
,ld_lipid date DEFAULT NULL
,rs_lipid VARCHAR(10) DEFAULT NULL
,ih_lipid VARCHAR(5) DEFAULT NULL
,ld_foot date DEFAULT NULL
,rs_foot VARCHAR(10) DEFAULT NULL
,ih_foot VARCHAR(5) DEFAULT NULL
,ld_retina date DEFAULT NULL
,rs_retina VARCHAR(10) DEFAULT NULL
,ih_retina VARCHAR(5) DEFAULT NULL
,ld_bp1 date DEFAULT NULL
,ih_bp1 VARCHAR(5) DEFAULT NULL
,rs_bps1 VARCHAR(10) DEFAULT NULL
,rs_bpd1 VARCHAR(10) DEFAULT NULL
,ld_bp2 date DEFAULT NULL
,ih_bp2 VARCHAR(5) DEFAULT NULL
,rs_bps2 VARCHAR(10) DEFAULT NULL
,rs_bpd2 VARCHAR(10) DEFAULT NULL
,`complication_dm` text,
`complication_ht` text,
`control_dm` int(1) DEFAULT '0',
`control_ht` int(1) DEFAULT '0',
`bmi` decimal(10,2) DEFAULT '0',
`obes` int(1) DEFAULT '0',
`height` smallint(6) DEFAULT '0',
`weight` mediumint(9) DEFAULT '0',
`waist_cm` smallint(6) DEFAULT 0 ,
`waist_cm_b` smallint(6) DEFAULT 0 ,
`waist_cm_a` smallint(6) DEFAULT 0 ,
`smoke_b` VARCHAR(1) DEFAULT NULL COMMENT '0=ไม่สูบ,1=สูบ',
`smoke_a` VARCHAR(1) DEFAULT NULL COMMENT '0=ไม่สูบ,1=สูบ',
`height_b` smallint(6) DEFAULT 0 ,
`height_a` smallint(6) DEFAULT 0 ,
`weight_b` smallint(6) DEFAULT 0 ,
`weight_a` smallint(6) DEFAULT 0 ,
`min_date_dx_dm` date DEFAULT NULL,
`min_date_dx_ht` date DEFAULT NULL,
`hmain` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`hsub` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`MainInScl` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`HMain_op` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (cid)
,KEY (cid)
,KEY (id)
,KEY (type_dx)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT IGNORE INTO t_dmht_nhso63(
hospcode,vhid,typearea,cid,birth,age_y,groupcode1560,groupname1560,sex,nation,minscl,inscl
,input_hosp,source_tb,hosp_dx,mix_dx,t_mix_dx,date_dx
)
(
SELECT SQL_BIG_RESULT
p.check_hosp,p.check_vhid,p.check_typearea,p.cid,p.birth,p.age_y,a.groupcode1560,a.groupname1560,p.sex,p.nation
,p.maininscl,p.inscl
,GROUP_CONCAT(c.input_hosp ORDER BY c.date_dx ) input_hosp
,GROUP_CONCAT(c.source_tb ORDER BY c.date_dx ) source_tb
,GROUP_CONCAT(c.hosp_dx ORDER BY c.date_dx ) hosp_dx
,GROUP_CONCAT(c.diagcode ORDER BY c.date_dx ) diagcode
,GROUP_CONCAT(DISTINCT substr(c.diagcode,1,1) ORDER BY SUBSTR(c.diagcode,1,1) )
,GROUP_CONCAT(c.date_dx ORDER BY c.date_dx ) date_dx
FROM
t_chronic c
INNER JOIN t_person_cid p ON c.cid = p.cid
INNER JOIN cage a ON p.age_y=a.age
WHERE (SUBSTR(UPPER(diagcode),1,3) BETWEEN 'E10' and 'E14'
OR SUBSTR(UPPER(diagcode),1,3) BETWEEN 'I10' and 'I15')
AND c.date_dx < @start_d2
AND (TYPEDISCH NOT IN(2) OR ISNULL(TYPEDISCH))
AND p.nation IN(99) AND p.DISCHARGE IN(9)
GROUP BY p.cid);

#update pid
UPDATE IGNORE t_dmht_nhso63 d,t_person_cid p SET d.pid=p.pid WHERE d.cid=p.cid AND d.hospcode=p.hospcode;

#update min date dx
UPDATE IGNORE t_dmht_nhso63 d INNER JOIN
(SELECT cid,min(date_dx) date_dx FROM t_chronic WHERE SUBSTR(UPPER(diagcode),1,3) BETWEEN 'E10' and 'E14' AND date_dx < @start_d2 GROUP BY CID
) s ON d.cid = s.cid SET d.min_date_dx_dm = s.date_dx ;

UPDATE IGNORE t_dmht_nhso63 d INNER JOIN
(SELECT cid,min(date_dx) date_dx FROM t_chronic WHERE SUBSTR(UPPER(diagcode),1,3) BETWEEN 'I10' and 'I15' AND date_dx < @start_d2 GROUP BY CID
) s ON d.cid = s.cid SET d.min_date_dx_ht = s.date_dx ;

#up_mixdiag
UPDATE IGNORE t_dmht_nhso63
SET type_dx = if(t_mix_dx ='I' ,'01'
,if(t_mix_dx ='E' ,'02'
,if(t_mix_dx ='E,I','03',NULL)));


#up_hba1c
UPDATE IGNORE t_dmht_nhso63 d INNER JOIN
(SELECT cid
,SUBSTR(GROUP_CONCAT(hospcode ORDER BY date_serv DESC),1,5) hospcode
,SUBSTR(GROUP_CONCAT(date_serv ORDER BY date_serv DESC),1,10) date_serv
,IF(INSTR(GROUP_CONCAT(labresult ORDER BY date_serv DESC),','),
SUBSTR(GROUP_CONCAT(labresult ORDER BY date_serv DESC),1,
instr(GROUP_CONCAT(labresult ORDER BY date_serv DESC),',' )-1),
GROUP_CONCAT(labresult ORDER BY date_serv DESC)) labresult
FROM tmp_labfu_nhso WHERE labtest IN('0531601')
GROUP BY cid) l ON d.cid=l.cid
SET d.ld_hba1c =l.date_serv,d.rs_hba1c =l.labresult ,d.ih_hba1c=l.HOSPCODE;

#up_fpg1
UPDATE IGNORE t_dmht_nhso63 d INNER JOIN
(SELECT cid
,SUBSTRING_INDEX(GROUP_CONCAT(hospcode ORDER BY date_serv DESC),',',1) hospcode
,SUBSTRING_INDEX(GROUP_CONCAT(date_serv ORDER BY date_serv DESC),',',1) date_serv
,SUBSTRING_INDEX(GROUP_CONCAT(labresult ORDER BY date_serv DESC),',',1) labresult

,IF(LENGTH(GROUP_CONCAT(hospcode ORDER BY date_serv DESC))
- LENGTH(REPLACE(GROUP_CONCAT(hospcode ORDER BY date_serv DESC), ',', '')) >0
,SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(hospcode ORDER BY date_serv DESC), ',', 2),',',-1),NULL) as hospcode1
,IF(LENGTH(GROUP_CONCAT(date_serv ORDER BY date_serv DESC))
- LENGTH(REPLACE(GROUP_CONCAT(date_serv ORDER BY date_serv DESC), ',', '')) >0
,SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(date_serv ORDER BY date_serv DESC), ',', 2),',',-1),NULL) as date_serv1
,IF(LENGTH(GROUP_CONCAT(labresult ORDER BY date_serv DESC))
- LENGTH(REPLACE(GROUP_CONCAT(labresult ORDER BY date_serv DESC), ',', '')) >0
,SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(labresult ORDER BY date_serv DESC), ',', 2),',',-1),NULL) as labresult1

,IF(LENGTH(GROUP_CONCAT(hospcode ORDER BY date_serv DESC))
- LENGTH(REPLACE(GROUP_CONCAT(hospcode ORDER BY date_serv DESC), ',', '')) >1
,SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(hospcode ORDER BY date_serv DESC), ',', 3),',',-1),NULL) as hospcode2
,IF(LENGTH(GROUP_CONCAT(date_serv ORDER BY date_serv DESC))
- LENGTH(REPLACE(GROUP_CONCAT(date_serv ORDER BY date_serv DESC), ',', '')) >1
,SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(date_serv ORDER BY date_serv DESC), ',', 3),',',-1),NULL) as date_serv2
,IF(LENGTH(GROUP_CONCAT(labresult ORDER BY date_serv DESC))
- LENGTH(REPLACE(GROUP_CONCAT(labresult ORDER BY date_serv DESC), ',', '')) >1
,SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(labresult ORDER BY date_serv DESC), ',', 3),',',-1),NULL) as labresult2

FROM tmp_labfu_nhso l
WHERE l.labtest in('0531002','0531101') AND labresult>0
GROUP BY cid) as t ON d.cid=t.cid
SET ld_fpg1 =t.DATE_SERV,rs_fpg1 =t.LABRESULT ,ih_fpg1=t.hospcode
,ld_fpg2 =t.DATE_SERV1,rs_fpg2 =t.LABRESULT1 ,ih_fpg2=t.hospcode1
,ld_fpg3 =t.DATE_SERV2,rs_fpg3 =t.LABRESULT2 ,ih_fpg3=t.hospcode2;

#Creatinine
UPDATE IGNORE t_dmht_nhso63 d INNER JOIN
(SELECT cid
,SUBSTRING_INDEX(GROUP_CONCAT(hospcode ORDER BY date_serv DESC),',',1) hospcode
,SUBSTRING_INDEX(GROUP_CONCAT(date_serv ORDER BY date_serv DESC),',',1) date_serv
,SUBSTRING_INDEX(GROUP_CONCAT(labresult ORDER BY date_serv DESC),',',1) labresult
FROM tmp_labfu_nhso l
WHERE l.labtest in('0581902','0581903')
GROUP BY cid) as t ON d.cid=t.cid
SET ld_creatinine =t.DATE_SERV,rs_creatinine =t.LABRESULT,ih_creatinine=t.hospcode ;


#lipid TotalCholesterol
UPDATE IGNORE t_dmht_nhso63 d INNER JOIN
(SELECT cid
,SUBSTRING_INDEX(GROUP_CONCAT(hospcode ORDER BY date_serv DESC),',',1) hospcode
,SUBSTRING_INDEX(GROUP_CONCAT(date_serv ORDER BY date_serv DESC),',',1) date_serv
,SUBSTRING_INDEX(GROUP_CONCAT(labresult ORDER BY date_serv DESC),',',1) labresult
FROM tmp_labfu_nhso l
WHERE l.labtest in('0541602')
GROUP BY cid) as t ON d.cid=t.cid
SET ld_lipid =t.DATE_SERV,rs_lipid =t.LABRESULT ,ih_lipid=t.hospcode ;

#foot
UPDATE IGNORE t_dmht_nhso63 d INNER JOIN
(SELECT cid
,SUBSTRING_INDEX(GROUP_CONCAT(hospcode ORDER BY date_serv DESC),',',1) hospcode
,SUBSTRING_INDEX(GROUP_CONCAT(date_serv ORDER BY date_serv DESC),',',1) date_serv
,SUBSTRING_INDEX(GROUP_CONCAT(FOOT ORDER BY date_serv DESC),',',1) FOOT
FROM tmp_chronicfu_nhso c
WHERE c.foot in(1,3)
GROUP BY cid) as t ON d.cid=t.cid
SET d.ld_foot =t.DATE_SERV,d.rs_foot =t.FOOT,d.ih_foot=t.hospcode;


#retina
UPDATE IGNORE t_dmht_nhso63 d INNER JOIN
(SELECT cid
,SUBSTRING_INDEX(GROUP_CONCAT(hospcode ORDER BY date_serv DESC),',',1) hospcode
,SUBSTRING_INDEX(GROUP_CONCAT(date_serv ORDER BY date_serv DESC),',',1) date_serv
,SUBSTRING_INDEX(GROUP_CONCAT(RETINA ORDER BY date_serv DESC),',',1) RETINA
FROM tmp_chronicfu_nhso c
WHERE c.RETINA in(1,2,3,4)
GROUP BY cid) as t ON d.cid=t.cid
SET ld_retina =t.DATE_SERV,rs_retina =t.RETINA ,ih_retina=t.hospcode;

#last_bp1
UPDATE IGNORE t_dmht_nhso63 d INNER JOIN
(SELECT cid
,SUBSTRING_INDEX(GROUP_CONCAT(hospcode ORDER BY date_serv DESC),',',1) hospcode
,SUBSTRING_INDEX(GROUP_CONCAT(date_serv ORDER BY date_serv DESC),',',1) date_serv
,SUBSTRING_INDEX(GROUP_CONCAT(SBP ORDER BY date_serv DESC),',',1) SBP
,SUBSTRING_INDEX(GROUP_CONCAT(DBP ORDER BY date_serv DESC),',',1) DBP

,IF(LENGTH(GROUP_CONCAT(hospcode ORDER BY date_serv DESC))
- LENGTH(REPLACE(GROUP_CONCAT(hospcode ORDER BY date_serv DESC), ',', '')) >0
,SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(hospcode ORDER BY date_serv DESC), ',', 2),',',-1),NULL) as hospcode1

,IF(LENGTH(GROUP_CONCAT(date_serv ORDER BY date_serv DESC))
- LENGTH(REPLACE(GROUP_CONCAT(date_serv ORDER BY date_serv DESC), ',', '')) >0
,SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(date_serv ORDER BY date_serv DESC), ',', 2),',',-1),NULL) as date_serv1

,IF(LENGTH(GROUP_CONCAT(sbp ORDER BY date_serv DESC))
- LENGTH(REPLACE(GROUP_CONCAT(sbp ORDER BY date_serv DESC), ',', '')) >0
,SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(sbp ORDER BY date_serv DESC), ',', 2),',',-1),NULL) as sbp1

,IF(LENGTH(GROUP_CONCAT(dbp ORDER BY date_serv DESC))
- LENGTH(REPLACE(GROUP_CONCAT(dbp ORDER BY date_serv DESC), ',', '')) >0
,SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(dbp ORDER BY date_serv DESC), ',', 2),',',-1),NULL) as dbp1
FROM tmp_chronicfu_nhso c
WHERE c.SBP >0 AND c.DBP>0
GROUP BY cid) as t ON d.cid=t.cid
SET ld_bp1 =t.DATE_SERV,rs_bps1 =t.SBP,rs_bpd1 =t.DBP,ih_bp1=t.hospcode
,ld_bp2 =t.DATE_SERV1,rs_bps2 =t.SBP1,rs_bpd2 =t.DBP1,ih_bp2=t.hospcode1;

#w,h
UPDATE IGNORE t_dmht_nhso63 d INNER JOIN
(SELECT cid
,SUBSTRING_INDEX(GROUP_CONCAT(hospcode ORDER BY date_serv DESC),',',1) hospcode
,SUBSTRING_INDEX(GROUP_CONCAT(date_serv ORDER BY date_serv DESC),',',1) date_serv
,SUBSTRING_INDEX(GROUP_CONCAT(weight ORDER BY date_serv DESC),',',1) weight
FROM tmp_chronicfu_nhso c
WHERE c.weight>0
GROUP BY cid) as t ON d.cid=t.cid
SET d.weight=t.weight,d.weight_a=t.weight ;
UPDATE IGNORE t_dmht_nhso63 d INNER JOIN
(SELECT cid
,SUBSTRING_INDEX(GROUP_CONCAT(hospcode ORDER BY date_serv DESC),',',1) hospcode
,SUBSTRING_INDEX(GROUP_CONCAT(date_serv ORDER BY date_serv DESC),',',1) date_serv
,SUBSTRING_INDEX(GROUP_CONCAT(height ORDER BY date_serv DESC),',',1) height
FROM tmp_chronicfu_nhso c
WHERE c.height>0
GROUP BY cid) as t ON d.cid=t.cid
SET d.height=t.height ,d.height_a=t.height;

UPDATE IGNORE t_dmht_nhso63 d INNER JOIN
(SELECT cid
,SUBSTRING_INDEX(GROUP_CONCAT(hospcode ORDER BY date_serv DESC),',',1) hospcode
,SUBSTRING_INDEX(GROUP_CONCAT(date_serv ORDER BY date_serv DESC),',',1) date_serv
,SUBSTRING_INDEX(GROUP_CONCAT(waist_cm ORDER BY date_serv DESC),',',1) waist_cm
FROM tmp_chronicfu_nhso c
WHERE c.waist_cm>0
GROUP BY cid) as t ON d.cid=t.cid
SET d.waist_cm=t.waist_cm ,d.waist_cm_a=t.waist_cm;

#bp control
UPDATE IGNORE t_dmht_nhso63 SET control_ht ='1' WHERE
type_dx in(1,3) AND (rs_bps1 >= 50 AND rs_bps1 <= 139) AND (rs_bps2 >= 50 AND rs_bps2 <=139 )
AND (rs_bpd1 >= 50 AND rs_bpd1 <= 89) AND (rs_bpd2 >= 50 AND rs_bpd2 <= 89) AND ld_bp1 BETWEEN @start_d2 AND @end_d
AND ld_bp2 BETWEEN @start_d2 AND @end_d ;

UPDATE IGNORE t_dmht_nhso63 SET bmi=round(WEIGHT/((HEIGHT/100)*(HEIGHT/100)),2) ;

UPDATE IGNORE t_dmht_nhso63 SET obes =1 WHERE
(sex='1' AND round(WAIST_CM)>90) OR (sex='2' AND round(WAIST_CM)>80)
OR bmi>=25;

UPDATE IGNORE t_dmht_nhso63 d INNER JOIN
(SELECT p.cid
,SUBSTRING_INDEX(GROUP_CONCAT(p.hospcode ORDER BY date_serv DESC),',',1) hospcode
,SUBSTRING_INDEX(GROUP_CONCAT(date_serv ORDER BY date_serv DESC),',',1) date_serv
,SUBSTRING_INDEX(GROUP_CONCAT(waist_cm ORDER BY date_serv DESC),',',1) waist_cm
FROM chronicfu c INNER JOIN t_person_db p ON c.hospcode=p.hospcode AND c.pid=p.pid
WHERE LENGTH(trim(p.cid))=13 AND waist_cm>0
AND c.date_serv BETWEEN CONCAT(@b_year-2,'1001') AND CONCAT(@b_year-1,'0930')
GROUP BY p.cid) as t ON d.cid=t.cid
SET d.waist_cm_b=t.waist_cm ;

UPDATE IGNORE t_dmht_nhso63 d INNER JOIN
(SELECT p.cid
,SUBSTRING_INDEX(GROUP_CONCAT(p.hospcode ORDER BY date_serv DESC),',',1) hospcode
,SUBSTRING_INDEX(GROUP_CONCAT(date_serv ORDER BY date_serv DESC),',',1) date_serv
,SUBSTRING_INDEX(GROUP_CONCAT(weight ORDER BY date_serv DESC),',',1) weight
FROM chronicfu c INNER JOIN t_person_db p ON c.hospcode=p.hospcode AND c.pid=p.pid
WHERE LENGTH(trim(p.cid))=13 AND weight>0
AND c.date_serv BETWEEN CONCAT(@b_year-2,'1001') AND CONCAT(@b_year-1,'0930')
GROUP BY p.cid) as t ON d.cid=t.cid
SET d.weight_b=t.weight ;

UPDATE IGNORE t_dmht_nhso63 d INNER JOIN
(SELECT p.cid
,GROUP_CONCAT(PPSPECIAL ORDER BY date_serv DESC) PPSPECIAL
,IF(INSTR(GROUP_CONCAT(PPSPECIAL ORDER BY date_serv DESC),'1B50') OR
INSTR(GROUP_CONCAT(PPSPECIAL ORDER BY date_serv DESC),'1B54') OR
INSTR(GROUP_CONCAT(PPSPECIAL ORDER BY date_serv DESC),'1B55') OR
INSTR(GROUP_CONCAT(PPSPECIAL ORDER BY date_serv DESC),'1B56') ,1,0) as smoke
FROM specialpp c INNER JOIN t_person_db p ON c.hospcode=p.hospcode AND c.pid=p.pid
WHERE LENGTH(trim(p.cid))=13 AND SUBSTR(c.PPSPECIAL,1,3) IN('1B5')
AND c.date_serv BETWEEN CONCAT(@b_year-2,'1001') AND CONCAT(@b_year-1,'0930')
GROUP BY p.cid) as t ON d.cid=t.cid
SET d.smoke_b=t.smoke ;


SELECT * FROM t_dmht_nhso63;

#PROVIDER1_SQL#
Script Cron
Force Script Cron0
Active1
Client Office Type
Create Date2017-08-17 02:44:58
Last Update2020-06-12 03:05:44