Workshop การเขียน HDC SQL Query (March 2017)

[jsondune]

# vi /etc/udev/rules.d/70-persistent-net.rules

[jsondune]

# vi /etc/sysconfig/network-scripts/ifcfg-eth0

[paiboonwai]

DROP TABLE IF EXISTS `t_person_cid`;
CREATE TABLE IF NOT EXISTS  `t_person_cid` (
  `HOSPCODE` varchar(5) NOT NULL,
  `CID` varchar(13) DEFAULT NULL,
  `PID` varchar(15) NOT NULL,
  `HID` varchar(14) DEFAULT NULL,
DROP TABLE IF EXISTS `t_person_cid`;
CREATE TABLE IF NOT EXISTS  `t_person_cid` (
  `HOSPCODE` varchar(5) NOT NULL,
  `CID` varchar(13) DEFAULT NULL,
  `PID` varchar(15) NOT NULL,
  `HID` varchar(14) DEFAULT NULL,
  `PRENAME` varchar(3) NOT NULL,
  `NAME` varchar(50) NOT NULL,
  `LNAME` varchar(50) NOT NULL,
  `HN` varchar(15) DEFAULT NULL,
  `SEX` varchar(1) NOT NULL,
  `BIRTH` date NOT NULL,
  `MSTATUS` char(1) DEFAULT NULL,
  `OCCUPATION_OLD` varchar(3) DEFAULT NULL,
  `OCCUPATION_NEW` varchar(4) DEFAULT NULL,
  `RACE` varchar(3) DEFAULT NULL,
  `NATION` varchar(3) NOT NULL,
  `RELIGION` varchar(2) DEFAULT NULL,
  `EDUCATION` varchar(2) DEFAULT NULL,
  `FSTATUS` varchar(1) DEFAULT NULL,
  `FATHER` varchar(13) DEFAULT NULL,
  `MOTHER` varchar(13) DEFAULT NULL,
  `COUPLE` varchar(13) DEFAULT NULL,
  `VSTATUS` varchar(1) DEFAULT NULL,
  `MOVEIN` date DEFAULT NULL,
  `DISCHARGE` varchar(1) DEFAULT NULL,
  `DDISCHARGE` date DEFAULT NULL,
  `ABOGROUP` varchar(1) DEFAULT NULL,
  `RHGROUP` varchar(1) DEFAULT NULL,
  `LABOR` varchar(2) DEFAULT NULL,
  `PASSPORT` varchar(8) DEFAULT NULL,
  `TYPEAREA` varchar(1) NOT NULL,
  `D_UPDATE` datetime NOT NULL,
  PRIMARY KEY (`CID`),
  KEY `idx1` (`CID`),
  KEY `idx2` (`HOSPCODE`,`CID`),
  KEY `idx3` (`NAME`),
  KEY `idx4` (`LNAME`),
  KEY `idx5` (`TYPEAREA`),
  KEY `idx6` (`HOSPCODE`,`PID`),
  KEY `idx7` (`BIRTH`),
  KEY `idx8` (`DISCHARGE`),
  KEY `idx9` (`LABOR`),
  KEY `idx10` (`HOSPCODE`,`HID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT IGNORE INTO t_person_cid (
SELECT * FROM person WHERE TYPEAREA in(1,3) ORDER BY D_UPDATE DESC
);

[paiboonwai]

SET @b_year :='2017';

DROP TABLE IF EXISTS t_person_cid;
CREATE  TABLE IF NOT EXISTS  t_person_cid(
  `HOSPCODE` varchar(5) NOT NULL,
  `CID` varchar(13) DEFAULT NULL,
  `PID` varchar(15) NOT NULL,
  `HID` varchar(14) DEFAULT NULL,
  `PRENAME` varchar(3) NOT NULL,
  `NAME` varchar(50) NOT NULL,
  `LNAME` varchar(50) NOT NULL,
  `HN` varchar(15) DEFAULT NULL,
  `SEX` varchar(1) NOT NULL,
  `BIRTH` date NOT NULL,
  `MSTATUS` char(1) DEFAULT NULL,
  `OCCUPATION_OLD` varchar(3) DEFAULT NULL,
  `OCCUPATION_NEW` varchar(4) DEFAULT NULL,
  `RACE` varchar(3) DEFAULT NULL,
  `NATION` varchar(3) NOT NULL,
  `RELIGION` varchar(2) DEFAULT NULL,
  `EDUCATION` varchar(2) DEFAULT NULL,
  `FSTATUS` varchar(1) DEFAULT NULL,
  `FATHER` varchar(13) DEFAULT NULL,
  `MOTHER` varchar(13) DEFAULT NULL,
  `COUPLE` varchar(13) DEFAULT NULL,
  `VSTATUS` varchar(1) DEFAULT NULL,
  `MOVEIN` date DEFAULT NULL,
  `DISCHARGE` varchar(1) DEFAULT NULL,
  `DDISCHARGE` date DEFAULT NULL,
  `ABOGROUP` varchar(1) DEFAULT NULL,
  `RHGROUP` varchar(1) DEFAULT NULL,
  `LABOR` varchar(2) DEFAULT NULL,
  `PASSPORT` varchar(8) DEFAULT NULL,
  `TYPEAREA` varchar(1) NOT NULL,
  `D_UPDATE` datetime NOT NULL,
  vhid VARCHAR(8) DEFAULT NULL,
  age_y VARCHAR(3) DEFAULT 0,
  PRIMARY KEY (`CID`),
  KEY `idx1` (`CID`),
  KEY `idx2` (`HOSPCODE`,`CID`),
  KEY `idx3` (`NAME`),
  KEY `idx4` (`LNAME`),
  KEY `idx5` (`TYPEAREA`),
  KEY `idx6` (`HOSPCODE`,`PID`),
  KEY `idx7` (`BIRTH`),
  KEY `idx8` (`DISCHARGE`),
  KEY `idx9` (`LABOR`),
  KEY `idx10` (`HOSPCODE`,`HID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT IGNORE INTO t_person_cid (
SELECT *,null,TIMESTAMPDIFF(YEAR,BIRTH,concat(@b_year,'0101')) age_y 
FROM person WHERE TYPEAREA in(1,3) ORDER BY D_UPDATE DESC
);

INSERT IGNORE INTO t_person_cid (
SELECT *,null,TIMESTAMPDIFF(YEAR,BIRTH,concat(@b_year,'0101')) age_y 
FROM person WHERE TYPEAREA in(2) ORDER BY D_UPDATE DESC
);

INSERT IGNORE INTO t_person_cid (
SELECT *,null,TIMESTAMPDIFF(YEAR,BIRTH,concat(@b_year,'0101')) age_y 
FROM person WHERE TYPEAREA in(4,5) ORDER BY D_UPDATE DESC
);

UPDATE t_person_cid p INNER JOIN home h ON p.hospcode=h.hospcode AND p.hid=h.hid
SET p.vhid=concat(h.CHANGWAT,h.AMPUR,h.TAMBON,SUBSTR(CONCAT('00',h.VILLAGE),-2));

[mrekaluk]

[mrekaluk]

[paiboonwai]

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;
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
);
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;

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);

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;
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);

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;
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);

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;

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);

[paiboonwai]

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

CREATE TABLE IF NOT EXISTS s_kpi_anc12(
  id varchar(32) NOT NULL,
  hospcode varchar(5) NOT NULL,
  areacode varchar(8) NOT NULL,
  flag_sent varchar(1) DEFAULT NULL,
  date_com varchar(14) DEFAULT NULL,
  b_year varchar(4) NOT NULL,
  target int(7) DEFAULT 0,
  result int(7) DEFAULT 0,
  target1 int(7) DEFAULT 0,
  result1 int(7) DEFAULT 0,
  target2 int(7) DEFAULT 0,
  result2 int(7) DEFAULT 0,
  target3 int(7) DEFAULT 0,
  result3 int(7) DEFAULT 0,
  target4 int(7) DEFAULT 0,
 result4 int(7) DEFAULT 0,

    PRIMARY KEY (id,hospcode,areacode,b_year),
    KEY (hospcode),
    KEY (areacode),
    KEY (b_year)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DELETE FROM s_kpi_anc12 WHERE id=@id AND b_year=(@b_year+543);

INSERT IGNORE INTO s_kpi_anc12
(
SELECT @id,p.hospcode,p.vhid
,@send,DATE_FORMAT(now(),'%Y%m%d%H%i') as d_com,@b_year+543
,COUNT(DISTINCT CONCAT(l.cid,'-',l.bdate)) traget
,COUNT(DISTINCT IF( a.g1_ga <=12, CONCAT(a.cid,'-',a.bdate),NULL)) result
,COUNT(DISTINCT IF(DATE_FORMAT(l.bdate,'%m') IN(10,11,12), CONCAT(l.cid,'-',l.bdate) ,NULL)) tragetq1
,COUNT(DISTINCT IF(DATE_FORMAT(l.bdate,'%m') IN(10,11,12) AND a.g1_ga <=12, CONCAT(a.cid,'-',a.bdate),NULL)) resultq1
,COUNT(DISTINCT IF(DATE_FORMAT(l.bdate,'%m') IN(1,2,3), CONCAT(l.cid,'-',l.bdate) ,NULL)) tragetq2
,COUNT(DISTINCT IF(DATE_FORMAT(l.bdate,'%m') IN(1,2,3) AND a.g1_ga <=12, CONCAT(a.cid,'-',a.bdate),NULL)) resultq2
,COUNT(DISTINCT IF(DATE_FORMAT(l.bdate,'%m') IN(4,5,6), CONCAT(l.cid,'-',l.bdate) ,NULL)) tragetq3
,COUNT(DISTINCT IF(DATE_FORMAT(l.bdate,'%m') IN(4,5,6) AND a.g1_ga <=12, CONCAT(a.cid,'-',a.bdate),NULL)) resultq3
,COUNT(DISTINCT IF(DATE_FORMAT(l.bdate,'%m') IN(7,8,9), CONCAT(l.cid,'-',l.bdate) ,NULL)) tragetq4
,COUNT(DISTINCT IF(DATE_FORMAT(l.bdate,'%m') IN(7,8,9) AND a.g1_ga <=12, CONCAT(a.cid,'-',a.bdate),NULL)) resultq4
FROM    t_labor l 
    INNER JOIN t_person_cid p ON l.cid=p.cid
    INNER JOIN chospital h ON p.hospcode=h.hoscode
  LEFT JOIN t_person_anc a ON l.cid=a.cid AND l.bdate =a.bdate
WHERE  l.BDATE BETWEEN @start_d AND @end_d AND p.typearea in(1,3) AND p.discharge IN(9)
 AND p.nation in(99) AND h.provcode in(@prov_c) 
GROUP BY p.hospcode,p.vhid
);

[paiboonwai]

/------------ s_anc5 ------------/; SET @prov_c := '14'; SET @id:= 'bd63b8d99f7054560fcf9c3b96f39c13'; SET @cat_id := '1ed90bc32310b503b7ca9b32af425ae5'; SET @b_year:='2017'; SET @start_d:=concat(@b_year-1,'1001'); SET @end_d:=concat(@b_year,'0930');

CREATE TABLE IF NOT EXISTS s_anc5( id varchar(32) NOT NULL, hospcode varchar(5) NOT NULL, areacode varchar(8) NOT NULL, flag_sent varchar(1) DEFAULT NULL, date_com varchar(14) DEFAULT NULL, b_year varchar(4) NOT NULL, target int(7) DEFAULT 0, result int(7) DEFAULT 0, target1 int(7) DEFAULT 0, result1 int(7) DEFAULT 0, target2 int(7) DEFAULT 0, result2 int(7) DEFAULT 0, target3 int(7) DEFAULT 0, result3 int(7) DEFAULT 0, target4 int(7) DEFAULT 0, result4 int(7) DEFAULT 0, PRIMARY KEY (id,hospcode,areacode,b_year), KEY (hospcode), KEY (areacode), KEY (b_year) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DELETE FROM s_anc5 WHERE id=@id AND b_year=(@b_year+543);

INSERT IGNORE INTO s_anc5 (SELECT @id,p.hospcode,p.vhid ,@send,DATE_FORMAT(now(),'%Y%m%d%H%i') as d_com,@b_year+543 ,COUNT(DISTINCT CONCAT(l.cid,'-',l.bdate)) target ,COUNT(DISTINCT IF(a.g1_ga <=12 AND a.g2_ga IN(16,17,18,19,20) AND a.g3_ga IN(24,25,26,27,28) AND a.g4_ga IN(30,31,32,33,34) AND a.g5_ga IN(36,37,38,39,40) , CONCAT(a.cid,'-',a.bdate),NULL)) result ,COUNT(DISTINCT IF(DATE_FORMAT(l.bdate,'%m') IN(10,11,12), CONCAT(l.cid,'-',l.bdate),NULL)) targetq1 ,COUNT(DISTINCT IF(DATE_FORMAT(l.bdate,'%m') IN(10,11,12) AND a.g1_ga <=12 AND a.g2_ga IN(16,17,18,19,20) AND a.g3_ga IN(24,25,26,27,28) AND a.g4_ga IN(30,31,32,33,34) AND a.g5_ga IN(36,37,38,39,40) , CONCAT(a.cid,'-',a.bdate),NULL)) resultq1 ,COUNT(DISTINCT IF(DATE_FORMAT(l.bdate,'%m') IN(1,2,3), CONCAT(l.cid,'-',l.bdate),NULL)) targetq2 ,COUNT(DISTINCT IF(DATE_FORMAT(l.bdate,'%m') IN(1,2,3) AND a.g1_ga <=12 AND a.g2_ga IN(16,17,18,19,20) AND a.g3_ga IN(24,25,26,27,28) AND a.g4_ga IN(30,31,32,33,34) AND a.g5_ga IN(36,37,38,39,40) , CONCAT(a.cid,'-',a.bdate),NULL)) resultq2 ,COUNT(DISTINCT IF(DATE_FORMAT(l.bdate,'%m') IN(4,5,6), CONCAT(l.cid,'-',l.bdate),NULL)) target3 ,COUNT(DISTINCT IF(DATE_FORMAT(l.bdate,'%m') IN(4,5,6) AND a.g1_ga <=12 AND a.g2_ga IN(16,17,18,19,20) AND a.g3_ga IN(24,25,26,27,28) AND a.g4_ga IN(30,31,32,33,34) AND a.g5_ga IN(36,37,38,39,40) , CONCAT(a.cid,'-',a.bdate),NULL)) resultq3 ,COUNT(DISTINCT IF(DATE_FORMAT(l.bdate,'%m') IN(7,8,9), CONCAT(l.cid,'-',l.bdate),NULL)) target4 ,COUNT(DISTINCT IF(DATE_FORMAT(l.bdate,'%m') IN(7,8,9) AND a.g1_ga <=12 AND a.g2_ga IN(16,17,18,19,20) AND a.g3_ga IN(24,25,26,27,28) AND a.g4_ga IN(30,31,32,33,34) AND a.g5_ga IN(36,37,38,39,40) , CONCAT(a.cid,'-',a.bdate),NULL)) resultq4 FROM t_labor l INNER JOIN t_person_cid p ON l.cid=p.cid INNER JOIN chospital h ON p.hospcode=h.hoscode LEFT JOIN t_person_anc a ON l.cid=a.cid AND l.bdate =a.bdate WHERE l.BDATE BETWEEN @start_d AND @end_d AND l.BTYPE NOT IN(6) AND p.typearea in(1,3) AND p.nation in(99) AND h.provcode in(@prov_c) AND p.discharge IN(9) GROUP BY p.hospcode,p.vhid );

[paiboonwai]

SET @prov_c := '14'; SET @id := '4f7d8042fb0a064b25f29a48f6ccd23f'; SET @b_year:='2017'; SET @start_d:=concat(@b_year-1,'1001'); SET @end_d:=concat(@b_year,'0930');

CREATE TABLE IF NOT EXISTS s_labor20_repeate( id varchar(32) NOT NULL, hospcode varchar(5) NOT NULL, areacode varchar(8) NOT NULL, flag_sent varchar(1) NOT NULL, date_com varchar(14) NOT NULL, b_year varchar(4) NOT NULL, target int(9) DEFAULT 0, result10 int(9) DEFAULT 0, result11 int(9) DEFAULT 0, result12 int(9) DEFAULT 0, result1 int(9) DEFAULT 0, result2 int(9) DEFAULT 0, result3 int(9) DEFAULT 0, result4 int(9) DEFAULT 0, result5 int(9) DEFAULT 0, result6 int(9) DEFAULT 0, result7 int(9) DEFAULT 0, result8 int(9) DEFAULT 0, result9 int(9) DEFAULT 0, target10 int(10) DEFAULT 0, target11 int(10) DEFAULT 0, target12 int(10) DEFAULT 0, target1 int(10) DEFAULT 0, target2 int(10) DEFAULT 0, target3 int(10) DEFAULT 0, target4 int(10) DEFAULT 0, target5 int(10) DEFAULT 0, target6 int(10) DEFAULT 0, target7 int(10) DEFAULT 0, target8 int(10) DEFAULT 0, target9 int(10) DEFAULT 0, PRIMARY KEY (id,hospcode,areacode,b_year), KEY (hospcode), KEY (areacode), KEY (b_year) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DELETE FROM s_labor20_repeate WHERE id=@id AND b_year=(@b_year+543);

INSERT IGNORE INTO s_labor20_repeate ( SELECT @id,h.hoscode,concat(h.provcode,SUBSTR(CONCAT('00',h.distcode),-2),SUBSTR(CONCAT('00',h.subdistcode),-2),SUBSTR(CONCAT('00',h.mu),-2)) as areacode ,@send,DATE_FORMAT(now(),'%Y%m%d%H%i') as d_com,@b_year+543 ,COUNT(DISTINCT CONCAT(l.cid,'-',l.bdate)) traget ,COUNT(DISTINCT IF(l.gravida >1 AND DATE_FORMAT(l.bdate,'%m') IN(10), CONCAT(l.cid,'-',l.bdate),NULL )) r10 ,COUNT(DISTINCT IF(l.gravida >1 AND DATE_FORMAT(l.bdate,'%m') IN(11), CONCAT(l.cid,'-',l.bdate),NULL )) r11 ,COUNT(DISTINCT IF(l.gravida >1 AND DATE_FORMAT(l.bdate,'%m') IN(12), CONCAT(l.cid,'-',l.bdate),NULL )) r12 ,COUNT(DISTINCT IF(l.gravida >1 AND DATE_FORMAT(l.bdate,'%m') IN(1), CONCAT(l.cid,'-',l.bdate),NULL )) r1 ,COUNT(DISTINCT IF(l.gravida >1 AND DATE_FORMAT(l.bdate,'%m') IN(2), CONCAT(l.cid,'-',l.bdate),NULL )) r2 ,COUNT(DISTINCT IF(l.gravida >1 AND DATE_FORMAT(l.bdate,'%m') IN(3), CONCAT(l.cid,'-',l.bdate),NULL )) r3 ,COUNT(DISTINCT IF(l.gravida >1 AND DATE_FORMAT(l.bdate,'%m') IN(4), CONCAT(l.cid,'-',l.bdate),NULL )) r4 ,COUNT(DISTINCT IF(l.gravida >1 AND DATE_FORMAT(l.bdate,'%m') IN(5), CONCAT(l.cid,'-',l.bdate),NULL )) r5 ,COUNT(DISTINCT IF(l.gravida >1 AND DATE_FORMAT(l.bdate,'%m') IN(6), CONCAT(l.cid,'-',l.bdate),NULL )) r6 ,COUNT(DISTINCT IF(l.gravida >1 AND DATE_FORMAT(l.bdate,'%m') IN(7), CONCAT(l.cid,'-',l.bdate),NULL )) r7 ,COUNT(DISTINCT IF(l.gravida >1 AND DATE_FORMAT(l.bdate,'%m') IN(8), CONCAT(l.cid,'-',l.bdate),NULL )) r8 ,COUNT(DISTINCT IF(l.gravida >1 AND DATE_FORMAT(l.bdate,'%m') IN(9), CONCAT(l.cid,'-',l.bdate),NULL )) r9 ,COUNT(DISTINCT IF(DATE_FORMAT(l.bdate,'%m') IN(10), CONCAT(l.cid,'-',l.bdate),NULL)) traget10 ,COUNT(DISTINCT IF(DATE_FORMAT(l.bdate,'%m') IN(11), CONCAT(l.cid,'-',l.bdate),NULL)) traget11 ,COUNT(DISTINCT IF(DATE_FORMAT(l.bdate,'%m') IN(12), CONCAT(l.cid,'-',l.bdate),NULL)) traget12 ,COUNT(DISTINCT IF(DATE_FORMAT(l.bdate,'%m') IN(1), CONCAT(l.cid,'-',l.bdate),NULL)) traget1 ,COUNT(DISTINCT IF(DATE_FORMAT(l.bdate,'%m') IN(2), CONCAT(l.cid,'-',l.bdate),NULL)) traget2 ,COUNT(DISTINCT IF(DATE_FORMAT(l.bdate,'%m') IN(3), CONCAT(l.cid,'-',l.bdate),NULL)) traget3 ,COUNT(DISTINCT IF(DATE_FORMAT(l.bdate,'%m') IN(4), CONCAT(l.cid,'-',l.bdate),NULL)) traget4 ,COUNT(DISTINCT IF(DATE_FORMAT(l.bdate,'%m') IN(5), CONCAT(l.cid,'-',l.bdate),NULL)) traget5 ,COUNT(DISTINCT IF(DATE_FORMAT(l.bdate,'%m') IN(6), CONCAT(l.cid,'-',l.bdate),NULL)) traget6 ,COUNT(DISTINCT IF(DATE_FORMAT(l.bdate,'%m') IN(7), CONCAT(l.cid,'-',l.bdate),NULL)) traget7 ,COUNT(DISTINCT IF(DATE_FORMAT(l.bdate,'%m') IN(8), CONCAT(l.cid,'-',l.bdate),NULL)) traget8 ,COUNT(DISTINCT IF(DATE_FORMAT(l.bdate,'%m') IN(9), CONCAT(l.cid,'-',l.bdate),NULL)) traget9 FROM t_labor l INNER JOIN chospital h ON l.bhosp=h.hoscode AND hostype in(5,6,7,11,12,15) AND h.provcode in(@prov_c) INNER JOIN t_person_cid p ON l.cid=p.cid WHERE TIMESTAMPDIFF(YEAR,p.birth,BDATE) < 20 AND l.BDATE BETWEEN @start_d AND @end_d AND p.nation in(99) GROUP BY HOSCODE );

[paiboonwai]

SELECT COUNT(DISTINCT cid) FROM t_person_cid WHERE TYPEAREA IN(1,3) -- AND TIMESTAMPDIFF(year,BIRTH,'20170101') BETWEEN 0 AND 5; AND age_y BETWEEN 0 AND 5;

[paiboonwai]

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-14,'1001');

DROP TABLE IF EXISTS tmp_nutrition; CREATE TABLE tmp_nutrition ( error_code varchar(255) DEFAULT NULL, KEY (cid), KEY (hospcode,pid), KEY (food,childdevelop) ) ENGINE=MyISAM AS( SELECT t.HOSPCODE,t.PID,t.SEQ,t.DATE_SERV,t.NUTRITIONPLACE,t.WEIGHT,t.HEIGHT,t.HEADCIRCUM,t.CHILDDEVELOP,t.FOOD,t.BOTTLE,t.PROVIDER,t.D_UPDATE,pe.cid,null as error_code FROM nutrition t LEFT JOIN person pe ON t.HOSPCODE=pe.HOSPCODE AND t.pid=pe.pid WHERE t.date_serv BETWEEN @start_d AND @end_d );

DROP TABLE IF EXISTS t_nutrition_service; CREATE TABLE IF NOT EXISTS t_nutrition_service( hospcode VARCHAR(5) NOT NULL, pid VARCHAR(15) NOT NULL, cid VARCHAR(13) NOT NULL, seq VARCHAR(16) NOT NULL, date_serv date, weight decimal(5,1) NOT NULL, height int(3) NOT NULL, HEADCIRCUM int(3) DEFAULT NULL, FOOD varchar(1) DEFAULT NULL, BOTTLE varchar(1) DEFAULT NULL, BIRTH date, SEX varchar(1) NOT NULL, NATION varchar(3) DEFAULT NULL, quarter_m int(1) DEFAULT 0, nutri1 int(1) DEFAULT 0, nutri2 int(1) DEFAULT 0, nutri3 int(1) DEFAULT 0, PRIMARY KEY (hospcode,pid,quarter_m) ) ENGINE MyISAM DEFAULT CHARACTER SET=utf8;

INSERT IGNORE INTO t_nutrition_service (HOSPCODE,PID,CID,SEQ,DATE_SERV,WEIGHT,HEIGHT,HEADCIRCUM,FOOD,BOTTLE ,BIRTH,SEX,NATION,quarter_m) ( SELECT n.HOSPCODE,n.PID,p.cid,n.SEQ,n.DATE_SERV,n.WEIGHT,n.HEIGHT,n.HEADCIRCUM,FOOD,BOTTLE ,p.BIRTH,p.SEX,p.NATION, IF(DATE_FORMAT(n.DATE_SERV,'%m') BETWEEN 10 AND 12,1, IF(DATE_FORMAT(n.DATE_SERV,'%m') BETWEEN 1 AND 3,2, IF(DATE_FORMAT(n.DATE_SERV,'%m') BETWEEN 4 AND 6,3, IF(DATE_FORMAT(n.DATE_SERV,'%m') BETWEEN 7 AND 9,4,0 )))) as quarter_m FROM tmp_nutrition n INNER JOIN person p ON n.HOSPCODE=p.HOSPCODE AND n.PID=p.PID WHERE WEIGHT BETWEEN 0.1 AND 300 AND HEIGHT BETWEEN 40 AND 250 AND n.DATE_SERV >= p.birth AND n.DATE_SERV BETWEEN @start_d AND @end_d AND p.NATION in(99) ORDER BY n.HOSPCODE ASC ,n.PID ASC ,n.DATE_SERV DESC );

[paiboonwai]

SELECT COUNT(DISTINCT n.cid) FROM t_nutrition_service n INNER JOIN t_person_cid p ON n.cid=p.CID WHERE p.TYPEAREA IN(1,3) AND p.age_y BETWEEN 0 AND 5;

[paiboonwai]

---Function nutri_cal() BEGIN DECLARE chkrs int(1) DEFAULT 0; DECLARE ay int(3) DEFAULT 0; DECLARE am int(2) DEFAULT 0;

    SET chkrs=NULL;
    SET ay=truncate(agem/12,0);
    SET am=MOD(agem,12);

                IF ntype=1 THEN  
                        SELECT nutri_level INTO chkrs  FROM cwhpa_referen WHERE year=ay AND month=am AND sex=isex AND nutri_type='1' AND (w BETWEEN low AND hi)=1;              
                END IF;

                IF ntype=2 THEN 
                            SELECT nutri_level INTO chkrs  FROM cwhpa_referen WHERE year=ay AND month=am AND sex=isex AND nutri_type='2' AND (h BETWEEN low AND hi)=1;              
                END IF;

            IF ntype=3 THEN 
                            SELECT nutri_level INTO chkrs  FROM cwh_referen WHERE (ay BETWEEN age_low AND age_max)=1 AND sex=isex AND h=height AND  (w BETWEEN weight_low AND weight_max)=1;                
                END IF;

    RETURN chkrs;
    END

[paiboonwai]

agem int(3),isex varchar(1),ntype int(1),h int(3),w int(3)

varchar(1)

[paiboonwai]

UPDATE t_nutrition_service SET nutri1=nutri_cal(TIMESTAMPDIFF(month,birth,date_serv),sex,1,height,weight) ,nutri2=nutri_cal(TIMESTAMPDIFF(month,birth,date_serv),sex,2,height,weight) ,nutri3=nutri_cal(TIMESTAMPDIFF(month,birth,date_serv),sex,3,height,weight);

[paiboonwai]

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

CREATE TABLE IF NOT EXISTS s_kpi_height05( id varchar(32) NOT NULL, hospcode varchar(5) NOT NULL, areacode varchar(8) NOT NULL, flag_sent varchar(1) NOT NULL, date_com varchar(14) NOT NULL, b_year varchar(4) NOT NULL, targetq1 int(9) DEFAULT 0, targetq2 int(9) DEFAULT 0, targetq3 int(9) DEFAULT 0, targetq4 int(9) DEFAULT 0, resultq1 int(9) DEFAULT 0, resultq2 int(9) DEFAULT 0, resultq3 int(9) DEFAULT 0, resultq4 int(9) DEFAULT 0,
b2q1 int(9) DEFAULT 0, b2q2 int(9) DEFAULT 0, b2q3 int(9) DEFAULT 0, b2q4 int(9) DEFAULT 0, a2q1 int(9) DEFAULT 0, a2q2 int(9) DEFAULT 0, a2q3 int(9) DEFAULT 0, a2q4 int(9) DEFAULT 0, b3q1 int(9) DEFAULT 0, b3q2 int(9) DEFAULT 0, b3q3 int(9) DEFAULT 0, b3q4 int(9) DEFAULT 0, a3q1 int(9) DEFAULT 0, a3q2 int(9) DEFAULT 0, a3q3 int(9) DEFAULT 0, a3q4 int(9) DEFAULT 0, PRIMARY KEY (id,hospcode,areacode,b_year), KEY (hospcode), KEY (areacode), KEY (b_year) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DELETE FROM s_kpi_height05 WHERE id=@id AND b_year=(@b_year+543); INSERT IGNORE INTO s_kpi_height05 ( SELECT @id,h.hoscode hospcode,concat(h.provcode,h.distcode,h.subdistcode,SUBSTR(CONCAT('00',h.mu),-2)) as areacode ,@send,DATE_FORMAT(now(),'%Y%m%d%H%i') as d_com,@b_year+543 as b_year ,COUNT(DISTINCT if(quarter_m in(1), CONCAT(n.HOSPCODE,'-',n.PID),NULL)) B1Q1 ,COUNT(DISTINCT if(quarter_m in(2), CONCAT(n.HOSPCODE,'-',n.PID),NULL)) B1Q2 ,COUNT(DISTINCT if(quarter_m in(3), CONCAT(n.HOSPCODE,'-',n.PID),NULL)) B1Q3 ,COUNT(DISTINCT if(quarter_m in(4), CONCAT(n.HOSPCODE,'-',n.PID),NULL)) B1Q4 ,COUNT(DISTINCT if(quarter_m in(1) AND nutri2 in(3,4,5) AND nutri3 in(3), CONCAT(n.HOSPCODE,'-',n.PID),NULL)) A1Q1 ,COUNT(DISTINCT if(quarter_m in(2) AND nutri2 in(3,4,5) AND nutri3 in(3), CONCAT(n.HOSPCODE,'-',n.PID),NULL)) A1Q2 ,COUNT(DISTINCT if(quarter_m in(3) AND nutri2 in(3,4,5) AND nutri3 in(3), CONCAT(n.HOSPCODE,'-',n.PID),NULL)) A1Q3 ,COUNT(DISTINCT if(quarter_m in(4) AND nutri2 in(3,4,5) AND nutri3 in(3), CONCAT(n.HOSPCODE,'-',n.PID),NULL)) A1Q4

,COUNT(DISTINCT if(quarter_m in(1) AND sex in(1) AND TIMESTAMPDIFF(year,birth,date_serv) in(5) , CONCAT(n.HOSPCODE,'-',n.PID),NULL)) B2Q1 ,COUNT(DISTINCT if(quarter_m in(2) AND sex in(1) AND TIMESTAMPDIFF(year,birth,date_serv) in(5), CONCAT(n.HOSPCODE,'-',n.PID),NULL)) B2Q2 ,COUNT(DISTINCT if(quarter_m in(3) AND sex in(1) AND TIMESTAMPDIFF(year,birth,date_serv) in(5), CONCAT(n.HOSPCODE,'-',n.PID),NULL)) B2Q3 ,COUNT(DISTINCT if(quarter_m in(4) AND sex in(1) AND TIMESTAMPDIFF(year,birth,date_serv) in(5), CONCAT(n.HOSPCODE,'-',n.PID),NULL)) B2Q4 ,SUM(if(quarter_m in(1) AND sex in(1) AND TIMESTAMPDIFF(year,birth,date_serv) in(5),height,0 )) A2Q1 ,SUM(if(quarter_m in(2) AND sex in(1) AND TIMESTAMPDIFF(year,birth,date_serv) in(5),height,0 )) A2Q2 ,SUM(if(quarter_m in(3) AND sex in(1) AND TIMESTAMPDIFF(year,birth,date_serv) in(5),height,0 )) A2Q3 ,SUM(if(quarter_m in(4) AND sex in(1) AND TIMESTAMPDIFF(year,birth,date_serv) in(5),height,0 )) A2Q4

,COUNT(DISTINCT if(quarter_m in(1) AND sex in(2) AND TIMESTAMPDIFF(year,birth,date_serv) in(5) , CONCAT(n.HOSPCODE,'-',n.PID),NULL)) B3Q1 ,COUNT(DISTINCT if(quarter_m in(2) AND sex in(2) AND TIMESTAMPDIFF(year,birth,date_serv) in(5), CONCAT(n.HOSPCODE,'-',n.PID),NULL)) B3Q2 ,COUNT(DISTINCT if(quarter_m in(3) AND sex in(2) AND TIMESTAMPDIFF(year,birth,date_serv) in(5), CONCAT(n.HOSPCODE,'-',n.PID),NULL)) B3Q3 ,COUNT(DISTINCT if(quarter_m in(4) AND sex in(2) AND TIMESTAMPDIFF(year,birth,date_serv) in(5), CONCAT(n.HOSPCODE,'-',n.PID),NULL)) B3Q4 ,SUM(if(quarter_m in(1) AND sex in(2) AND TIMESTAMPDIFF(year,birth,date_serv) in(5),height,0 )) A3Q1 ,SUM(if(quarter_m in(2) AND sex in(2) AND TIMESTAMPDIFF(year,birth,date_serv) in(5),height,0 )) A3Q2 ,SUM(if(quarter_m in(3) AND sex in(2) AND TIMESTAMPDIFF(year,birth,date_serv) in(5),height,0 )) A3Q3 ,SUM(if(quarter_m in(4) AND sex in(2) AND TIMESTAMPDIFF(year,birth,date_serv) in(5),height,0 )) A3Q4 FROM t_nutrition_service n inner join chospital h on n.hospcode=h.hoscode WHERE h.provcode in(@prov_c) AND TIMESTAMPDIFF(YEAR,n.birth,n.DATE_SERV) <= 5 GROUP BY n.hospcode );

[paiboonwai]

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-14,'1001');

DROP TABLE IF EXISTS t_ageing; CREATE TABLE IF NOT EXISTS t_ageing( hospcode VARCHAR(5) NOT NULL, pid VARCHAR(15) NOT NULL, cid VARCHAR(13) NOT NULL, seq VARCHAR(16) NOT NULL, date_serv date, SERVPLACE char(1) NOT NULL, PPSPECIAL varchar(6) NOT NULL, PPSPLACE varchar(5) DEFAULT NULL, PROVIDER varchar(15) DEFAULT NULL, D_UPDATE datetime NOT NULL, BIRTH date, SEX varchar(1) NOT NULL, NATION varchar(3) DEFAULT NULL, quarter_m int(1) DEFAULT 0, PRIMARY KEY (hospcode,pid,quarter_m) ) ENGINE MyISAM DEFAULT CHARACTER SET=utf8;

INSERT IGNORE INTO t_ageing ( SELECT n.HOSPCODE,n.PID,p.cid,n.SEQ,n.DATE_SERV,n.SERVPLACE,n.PPSPECIAL,n.PPSPLACE,n.PROVIDER,n.D_UPDATE ,p.BIRTH,p.SEX,p.NATION, IF(DATE_FORMAT(n.DATE_SERV,'%m') IN(10,11,12,1,2,3),1, IF(DATE_FORMAT(n.DATE_SERV,'%m') IN(4,5,6,7,8,9),2,0 )) as quarter_m FROM specialpp n INNER JOIN person p ON n.HOSPCODE=p.HOSPCODE AND n.PID=p.PID WHERE n.DATE_SERV >= p.birth AND n.DATE_SERV BETWEEN @start_d AND @end_d AND p.NATION in(99) AND TIMESTAMPDIFF(year,p.BIRTH,n.DATE_SERV) >=60 AND n.PPSPECIAL in('1B1280','1B1281','1B1282') ORDER BY n.HOSPCODE ASC ,n.PID ASC ,n.DATE_SERV DESC );

[paiboonwai]

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-14,'1001');

SELECT COUNT(DISTINCT if(PPSPECIAL IN('1B1280','1B1281','1B1282') ,CONCAT(hospcode,'-',pid),NULL)) target ,COUNT(DISTINCT if(PPSPECIAL IN('1B1280') ,CONCAT(hospcode,'-',pid),NULL)) result FROM t_ageing

[paiboonwai]

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-14,'1001');

DROP TABLE IF EXISTS tmp_nutrition; CREATE TABLE tmp_nutrition ( error_code varchar(255) DEFAULT NULL, KEY (cid), KEY (hospcode,pid), KEY (food,childdevelop) ) ENGINE=MyISAM AS( SELECT t.HOSPCODE,t.PID,t.SEQ,t.DATE_SERV,t.NUTRITIONPLACE,t.WEIGHT,t.HEIGHT,t.HEADCIRCUM,t.CHILDDEVELOP,t.FOOD,t.BOTTLE,t.PROVIDER,t.D_UPDATE,pe.cid,null as error_code FROM nutrition t LEFT JOIN person pe ON t.HOSPCODE=pe.HOSPCODE AND t.pid=pe.pid WHERE t.date_serv BETWEEN @start_d AND @end_d );

DROP TABLE IF EXISTS t_nutrition_service; CREATE TABLE IF NOT EXISTS t_nutrition_service( hospcode VARCHAR(5) NOT NULL, pid VARCHAR(15) NOT NULL, cid VARCHAR(13) NOT NULL, seq VARCHAR(16) NOT NULL, date_serv date, weight decimal(5,1) NOT NULL, height int(3) NOT NULL, HEADCIRCUM int(3) DEFAULT NULL, FOOD varchar(1) DEFAULT NULL, BOTTLE varchar(1) DEFAULT NULL, BIRTH date, SEX varchar(1) NOT NULL, NATION varchar(3) DEFAULT NULL, quarter_m int(1) DEFAULT 0, nutri1 int(1) DEFAULT 0, nutri2 int(1) DEFAULT 0, nutri3 int(1) DEFAULT 0, PRIMARY KEY (hospcode,pid,quarter_m) ) ENGINE MyISAM DEFAULT CHARACTER SET=utf8;

INSERT IGNORE INTO t_nutrition_service (HOSPCODE,PID,CID,SEQ,DATE_SERV,WEIGHT,HEIGHT,HEADCIRCUM,FOOD,BOTTLE ,BIRTH,SEX,NATION,quarter_m) ( SELECT n.HOSPCODE,n.PID,p.cid,n.SEQ,n.DATE_SERV,n.WEIGHT,n.HEIGHT,n.HEADCIRCUM,FOOD,BOTTLE ,p.BIRTH,p.SEX,p.NATION, IF(DATE_FORMAT(n.DATE_SERV,'%m') BETWEEN 10 AND 12,1, IF(DATE_FORMAT(n.DATE_SERV,'%m') BETWEEN 1 AND 3,2, IF(DATE_FORMAT(n.DATE_SERV,'%m') BETWEEN 4 AND 6,3, IF(DATE_FORMAT(n.DATE_SERV,'%m') BETWEEN 7 AND 9,4,0 )))) as quarter_m FROM tmp_nutrition n INNER JOIN person p ON n.HOSPCODE=p.HOSPCODE AND n.PID=p.PID WHERE WEIGHT BETWEEN 0.1 AND 300 AND HEIGHT BETWEEN 40 AND 250 AND n.DATE_SERV >= p.birth AND n.DATE_SERV BETWEEN @start_d AND @end_d AND p.NATION in(99) ORDER BY n.HOSPCODE ASC ,n.PID ASC ,n.DATE_SERV DESC );

UPDATE t_nutrition_service SET nutri1=nutri_cal(TIMESTAMPDIFF(month,birth,date_serv),sex,1,height,weight) ,nutri2=nutri_cal(TIMESTAMPDIFF(month,birth,date_serv),sex,2,height,weight) ,nutri3=nutri_cal(TIMESTAMPDIFF(month,birth,date_serv),sex,3,height,weight);

DROP TABLE IF EXISTS t_nutrition_service6_14; CREATE TABLE IF NOT EXISTS t_nutrition_service6_14( hospcode VARCHAR(5) NOT NULL, pid VARCHAR(15) NOT NULL, seq VARCHAR(16) NOT NULL, date_serv date, weight decimal(5,1) NOT NULL, height int(3) NOT NULL, HEADCIRCUM int(3) DEFAULT NULL, FOOD varchar(1) DEFAULT NULL, BOTTLE varchar(1) DEFAULT NULL, BIRTH date, SEX varchar(1) NOT NULL, NATION varchar(3) DEFAULT NULL, quarter_m int(1) DEFAULT 0, nutri1 int(1) DEFAULT 0, nutri2 int(1) DEFAULT 0, nutri3 int(1) DEFAULT 0, PRIMARY KEY (hospcode,pid,quarter_m) ) ENGINE MyISAM DEFAULT CHARACTER SET=utf8;

INSERT IGNORE INTO t_nutrition_service6_14 (HOSPCODE,PID,SEQ,DATE_SERV,WEIGHT,HEIGHT,HEADCIRCUM,FOOD,BOTTLE ,BIRTH,SEX,NATION,quarter_m) ( SELECT n.HOSPCODE,n.PID,n.SEQ,n.DATE_SERV,n.WEIGHT,n.HEIGHT,n.HEADCIRCUM,FOOD,BOTTLE ,p.BIRTH,p.SEX,p.NATION, IF(DATE_FORMAT(n.DATE_SERV,'%m') IN(10,11,12),2, IF(DATE_FORMAT(n.DATE_SERV,'%m') IN(5,6,7),1,0 )) as quarter_m FROM tmp_nutrition n INNER JOIN person p ON n.HOSPCODE=p.HOSPCODE AND n.PID=p.PID WHERE WEIGHT BETWEEN 0.1 AND 300 AND HEIGHT BETWEEN 40 AND 250 AND n.DATE_SERV >= p.birth AND TIMESTAMPDIFF(YEAR,p.BIRTH,n.DATE_SERV) BETWEEN 6 AND 14 AND n.DATE_SERV BETWEEN @start_d AND @end_d AND p.NATION in(99) ORDER BY n.HOSPCODE ASC ,n.PID ASC ,n.DATE_SERV DESC );

UPDATE t_nutrition_service6_14 SET nutri1=nutri_cal(TIMESTAMPDIFF(month,birth,date_serv),sex,1,height,weight) ,nutri2=nutri_cal(TIMESTAMPDIFF(month,birth,date_serv),sex,2,height,weight) ,nutri3=nutri_cal(TIMESTAMPDIFF(month,birth,date_serv),sex,3,height,weight);

[paiboonwai]

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

CREATE TABLE IF NOT EXISTS s_kpi_height614( id varchar(32) NOT NULL, hospcode varchar(5) NOT NULL, areacode varchar(8) NOT NULL, flag_sent varchar(1) NOT NULL, date_com varchar(14) NOT NULL, b_year varchar(4) NOT NULL, targetq1 int(9) DEFAULT 0, targetq2 int(9) DEFAULT 0, resultq1 int(9) DEFAULT 0, resultq2 int(9) DEFAULT 0, a2q1 int(9) DEFAULT 0, a2q2 int(9) DEFAULT 0, a3q1 int(9) DEFAULT 0, a3q2 int(9) DEFAULT 0, a4q1 int(9) DEFAULT 0, a4q2 int(9) DEFAULT 0, b3q1 int(9) DEFAULT 0, b3q2 int(9) DEFAULT 0, a5q1 int(9) DEFAULT 0, a5q2 int(9) DEFAULT 0, b4q1 int(9) DEFAULT 0, b4q2 int(9) DEFAULT 0, a6q1 int(9) DEFAULT 0, a6q2 int(9) DEFAULT 0,

PRIMARY KEY (id,hospcode,areacode,b_year), KEY (hospcode), KEY (areacode), KEY (b_year) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DELETE FROM s_kpi_height614 WHERE id=@id AND b_year=(@b_year+543); INSERT IGNORE INTO s_kpi_height614 ( SELECT @id,h.hoscode hospcode,concat(h.provcode,h.distcode,h.subdistcode,SUBSTR(CONCAT('00',h.mu),-2)) as areacode ,@send,DATE_FORMAT(now(),'%Y%m%d%H%i') as d_com,@b_year+543 as b_year ,COUNT(DISTINCT if(DATE_FORMAT(n.date_serv,'%m') IN(10,11,12) , CONCAT(n.HOSPCODE,'-',n.PID),NULL)) B1Q1 ,COUNT(DISTINCT if(DATE_FORMAT(n.date_serv,'%m') IN(5,6,7), CONCAT(n.HOSPCODE,'-',n.PID),NULL)) B1Q2 ,COUNT(DISTINCT if(DATE_FORMAT(n.date_serv,'%m') IN(10,11,12) AND nutri2 in(3,4,5) AND nutri3 in(3), CONCAT(n.HOSPCODE,'-',n.PID),NULL)) A1Q1 ,COUNT(DISTINCT if(DATE_FORMAT(n.date_serv,'%m') IN(5,6,7) AND nutri2 in(3,4,5) AND nutri3 in(3), CONCAT(n.HOSPCODE,'-',n.PID),NULL)) A1Q2

,COUNT(DISTINCT if(DATE_FORMAT(n.date_serv,'%m') IN(10,11,12) AND nutri3 in(1), CONCAT(n.HOSPCODE,'-',n.PID),NULL)) A2Q1 ,COUNT(DISTINCT if(DATE_FORMAT(n.date_serv,'%m') IN(5,6,7) AND nutri3 in(1), CONCAT(n.HOSPCODE,'-',n.PID),NULL)) A2Q2

,COUNT(DISTINCT if(DATE_FORMAT(n.date_serv,'%m') IN(10,11,12) AND nutri3 IN(5,6,7), CONCAT(n.HOSPCODE,'-',n.PID),NULL)) A3Q1 ,COUNT(DISTINCT if(DATE_FORMAT(n.date_serv,'%m') IN(5,6,7) AND nutri3 IN(5,6,7), CONCAT(n.HOSPCODE,'-',n.PID),NULL)) A3Q2

,COUNT(DISTINCT if(DATE_FORMAT(n.date_serv,'%m') IN(10,11,12) AND nutri2 in(1), CONCAT(n.HOSPCODE,'-',n.PID),NULL)) A4Q1 ,COUNT(DISTINCT if(DATE_FORMAT(n.date_serv,'%m') IN(5,6,7) AND nutri2 in(1), CONCAT(n.HOSPCODE,'-',n.PID),NULL)) A4Q2

,COUNT(DISTINCT if(DATE_FORMAT(n.date_serv,'%m') IN(10,11,12) AND sex in(1) AND TIMESTAMPDIFF(year,birth,date_serv) in(12) , CONCAT(n.HOSPCODE,'-',n.PID),NULL)) B3Q1 ,COUNT(DISTINCT if(DATE_FORMAT(n.date_serv,'%m') IN(5,6,7) AND sex in(1) AND TIMESTAMPDIFF(year,birth,date_serv) in(12), CONCAT(n.HOSPCODE,'-',n.PID),NULL)) B3Q2 ,SUM(if(DATE_FORMAT(n.date_serv,'%m') IN(10,11,12) AND sex in(1) AND TIMESTAMPDIFF(year,birth,date_serv) in(12),height,0 )) A5Q1 ,SUM(if(DATE_FORMAT(n.date_serv,'%m') IN(5,6,7) AND sex in(1) AND TIMESTAMPDIFF(year,birth,date_serv) in(12),height,0 )) A5Q2

,COUNT(DISTINCT if(DATE_FORMAT(n.date_serv,'%m') IN(10,11,12) AND sex in(2) AND TIMESTAMPDIFF(year,birth,date_serv) in(12) , CONCAT(n.HOSPCODE,'-',n.PID),NULL)) B4Q1 ,COUNT(DISTINCT if(DATE_FORMAT(n.date_serv,'%m') IN(5,6,7) AND sex in(2) AND TIMESTAMPDIFF(year,birth,date_serv) in(12), CONCAT(n.HOSPCODE,'-',n.PID),NULL)) B4Q2 ,SUM(if(DATE_FORMAT(n.date_serv,'%m') IN(10,11,12) AND sex in(2) AND TIMESTAMPDIFF(year,birth,date_serv) in(12),height,0 )) A6Q1 ,SUM(if(DATE_FORMAT(n.date_serv,'%m') IN(5,6,7) AND sex in(2) AND TIMESTAMPDIFF(year,birth,date_serv) in(12),height,0 )) A6Q2

FROM t_nutrition_service6_14 n inner join chospital h on n.hospcode=h.hoscode WHERE h.provcode in(@prov_c) AND TIMESTAMPDIFF(YEAR,n.birth,n.DATE_SERV) BETWEEN 6 AND 14 GROUP BY n.hospcode );

[paiboonwai]

SET @prov_c := '14'; SET @id:= '13'; SET @b_year:='2017'; SET @start_d:=concat(@b_year-1,'1001'); SET @end_d:=concat(@b_year,'0930');

DROP TABLES IF EXISTS tmp_ncdscreen; CREATE TABLE IF NOT EXISTS tmp_ncdscreen (error_code varchar(255) DEFAULT NULL, KEY(cid)) ENGINE=MyISAM AS( SELECT SQL_BIG_RESULT n.*,p.CID,null as error_code FROM ncdscreen n left join person p ON n.HOSPCODE=p.HOSPCODE AND n.PID=p.PID WHERE DATE_SERV BETWEEN @start_d AND @end_d );

[paiboonwai]

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

SELECT COUNT(DISTINCT concat(n.hospcode,'-',n.pid)) as target ,COUNT(DISTINCT IF(ROUND(weight/((height/100)*(height/100)),2) BETWEEN 18.5 AND 22.9 , concat(n.hospcode,'-',n.pid),null) ) result FROM tmp_ncdscreen n INNER JOIN person p ON n.HOSPCODE=p.HOSPCODE AND n.PID=p.PID INNER JOIN chospital h ON n.HOSPCODE=h.hoscode WHERE TIMESTAMPDIFF(YEAR,p.BIRTH,n.DATE_SERV) BETWEEN 30 AND 44 AND n.DATE_SERV BETWEEN @start_d AND @end_d AND WEIGHT BETWEEN 20 AND 300 AND HEIGHT BETWEEN 40 AND 250 AND h.provcode in(@prov_c);

[paiboonwai]

mysqldump -a -c -e -n -Q --add-drop-table --skip-lock-tables -uroot -p123456 hdc > hdc.sql

[paiboonwai]

https://downloads.mysql.com/archives/installer/