NHSO_PP60-KPI06-Nutrition6-12Y

Update Delete

ID781
Parent ID52
Table Name_pp60_kpi06_nutrition
TitleNHSO_PP60-KPI06-Nutrition6-12Y
Description
Script#SQL_OPTIONS#
PROVIDERS=1
PROVIDER1=43STD
PROVIDER1_VALIDATE_TABLES=accident,admission,anc,appointment
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 @prov_c := :provcode;
SET @id:= 'kpi06_nutrition6y12y';
SET @start_d:=('2017-04-01');#Count 1 April
SET @end_d:=('2018-03-31');

/*สร้างตารางkpi06_nutrition6y12y เพื่อให้ตัดข้อมูลแฟ้ม nutrition เด็กอายุ 6 -12 ปี */
DROP TABLE IF EXISTS `_zone6_tmp_kpi06_nutrition6y12y`;
CREATE TABLE _zone6_tmp_kpi06_nutrition6y12y (KEY `idx1` (`HOSPCODE`,`PID`) USING BTREE,
KEY `idx2` (`HOSPCODE`) USING BTREE,
KEY `idx3` (`CID`) USING BTREE,
KEY `idx4` (`DATE_SERV`) USING BTREE)
ENGINE=MyISAM DEFAULT CHARSET=utf8
SELECT n.HOSPCODE,n.PID,p.CID,p.BIRTH,n.DATE_SERV
,p.NATION,YEAR(n.DATE_SERV) - YEAR(p.BIRTH) AS yeardateserv
,n.SEQ,n.WEIGHT,n.HEIGHT
FROM nutrition n
INNER JOIN person p ON n.HOSPCODE = p.HOSPCODE AND n.PID = p.PID
WHERE (n.DATE_SERV BETWEEN @start_d AND @end_d)
AND n.WEIGHT IS NOT NULL AND n.HEIGHT IS NOT NULL
AND YEAR(n.DATE_SERV) - YEAR(p.BIRTH) BETWEEN 6 AND 12
GROUP BY n.HOSPCODE,n.PID
;
ALTER TABLE _zone6_tmp_kpi06_nutrition6y12y ADD COLUMN UC_CID varchar(15) DEFAULT NULL ; -- เพ่ม ฟิลด์สิทธิ เพื่อตรวจสอทธิ

/*ปรับปรุงสิทธิ*/
UPDATE _zone6_tmp_kpi06_nutrition6y12y n ,dbpop d
SET n.UC_CID =d.MainInScl
WHERE n.cid = d.PID;
/*ปรับปรุงสิทธิที่ไม่ใช่คนไทย*/
UPDATE _zone6_tmp_kpi06_nutrition6y12y SET UC_CID = 'nothai' WHERE NATION <> '099';

/*นับข้อมูล*/
SELECT HOSPCODE,
SUM(IF(UC_CID IN ('WEL','UCS'),1,0)) AS 'Uc',
SUM(IF(UC_CID NOT IN ('WEL','UCS','nothai'),1,0)) AS 'Non Uc',
SUM(IF(UC_CID IS NULL,1,0)) AS 'Other',
SUM(IF(UC_CID = 'nothai',1,0)) AS 'Non Thai',
COUNT(CID) 'SUM_ALL'
FROM _zone6_tmp_kpi06_nutrition6y12y
GROUP BY HOSPCODE;

#PROVIDER1_SQL#
Script Cron
Force Script Cron0
Active1
Client Office Type
Create Date2017-12-13 02:31:04
Last Update2018-05-18 11:29:32