NHSO_PP60-KPI05-Childdev0-5Y

Update Delete

ID780
Parent ID52
Table Name_pp60_kpi05_childdev
TitleNHSO_PP60-KPI05-Childdev0-5Y
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:= 'kpi05childdev';
SET @start_d:=('2017-04-01');#Count 1 April
SET @end_d:=('2018-03-31');

/*สร้างตารางkpi05childdev เพื่อให้ตัดข้อมูลแฟ้ม nutrition ,specialpp */
DROP TABLE IF EXISTS `_zone6_tmp_kpi05childdev`;
CREATE TABLE _zone6_tmp_kpi05childdev (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 * FROM(SELECT n.HOSPCODE,n.PID,p.CID,p.BIRTH,n.DATE_SERV
,p.NATION,YEAR(n.DATE_SERV) - YEAR(p.BIRTH) AS yeardateserv,n.CHILDDEVELOP
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.CHILDDEVELOP IN(1,2,3)
AND YEAR(n.DATE_SERV) - YEAR(p.BIRTH) BETWEEN 0 AND 5
UNION ALL
SELECT s.HOSPCODE,s.PID,p.CID,p.BIRTH,s.DATE_SERV
,p.NATION,YEAR(s.DATE_SERV) - YEAR(p.BIRTH) AS yeardateserv
,s.PPSPECIAL AS CHILDDEVELOP
FROM specialpp s
INNER JOIN person p ON s.HOSPCODE = p.HOSPCODE AND s.PID = p.PID
WHERE s.DATE_SERV BETWEEN @start_d AND @end_d
AND LEFT(s.PPSPECIAL,3) = '1B2'
AND YEAR(s.DATE_SERV) - YEAR(p.BIRTH) BETWEEN 0 AND 5) AS childdev
GROUP BY HOSPCODE,PID ;

ALTER TABLE _zone6_tmp_kpi05childdev ADD COLUMN UC_CID varchar(15) DEFAULT NULL ; -- เพ่ม ฟิลด์สิทธิ เพื่อตรวจสอทธิ

/*ปรับปรุงสิทธิ*/
UPDATE _zone6_tmp_kpi05childdev n ,dbpop d
SET n.UC_CID =d.MainInScl
WHERE n.cid = d.PID;
/*ปรับปรุงสิทธิที่ไม่ใช่คนไทย*/
UPDATE _zone6_tmp_kpi05childdev 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_kpi05childdev
GROUP BY HOSPCODE;

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