NHSO_PP60-KPI05-Childdev0-5Y
ID | 780 |
---|---|
Parent ID | 52 |
Table Name | _pp60_kpi05_childdev |
Title | NHSO_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 Cron | 0 |
Active | 1 |
Client Office Type | |
Create Date | 2017-12-13 02:29:50 |
Last Update | 2018-05-18 11:29:19 |
- Home
- About
- Privacy Policy
- Terms of Use
- Contact Us
© 2015. All Rights Reserved.