NHSO_PP60-KPI03-Postnatal
ID | 778 |
---|---|
Parent ID | 52 |
Table Name | _pp60_kpi03_postnatal |
Title | NHSO_PP60-KPI03-Postnatal |
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 @start_d:=('2017-04-01');#Count 1 April SET @end_d:=('2018-03-31'); -- ---------------------------- -- สร้างตารางเก็บข้อมูลบริการหญิงหลังคลอด -- ---------------------------- DROP TABLE IF EXISTS _pp_care; CREATE TABLE IF NOT EXISTS _pp_care ( hospcode varchar(5) DEFAULT NULL, pid VARCHAR(10) DEFAULT NULL, cid VARCHAR(13) DEFAULT NULL, nation VARCHAR(3) DEFAULT NULL, maininscl VARCHAR(10) DEFAULT NULL, ppcare VARCHAR(10) DEFAULT NULL, PRIMARY KEY (hospcode,pid,cid,nation,maininscl,ppcare) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- ---------------------------- -- นำเข้าข้อมูลบริการ -- ---------------------------- INSERT IGNORE INTO _pp_care( hospcode,pid,cid,nation,maininscl,ppcare ) SELECT p.HOSPCODE ,p.pid,p.CID,p.NATION,d.MainInScl ,COUNT(PPCARE) FROM labor l INNER JOIN person p ON l.pid=p.pid AND l.HOSPCODE=p.HOSPCODE INNER JOIN chospital h ON p.HOSPCODE=h.hoscode LEFT JOIN postnatal a ON l.pid=a.pid AND l.HOSPCODE=a.HOSPCODE LEFT JOIN dbpop d ON p.CID=d.PID WHERE l.BDATE BETWEEN '20160401' AND '20170331' AND l.BTYPE NOT IN(6) AND p.typearea in(1,3) #AND p.discharge IN(9) #AND p.NATION <> '099' #AND getAgeDayNum(a.BDATE,a.PPCARE) <= '42' AND(TIMESTAMPDIFF(YEAR,a.BDATE,a.PPCARE) BETWEEN 0 AND 42) GROUP BY p.PID ORDER BY p.CID; -- ---------------------------- -- STEP สุดท้าย -- นับจำนวนข้อมูล แยกรายสถานบริการ -- ---------------------------- SELECT CONCAT(@prov_c,h.distcode) AS 'code_ampher', p.HOSPCODE AS 'hospcode',h.hosname AS 'hosname', COUNT(case when p.nation = '099' AND p.maininscl in ('ucs','wel') AND p.ppcare >= '2' THEN p.pid end) AS 'uc_and_wel', COUNT(case when p.nation = '099' AND p.maininscl not in ('ucs','wel') AND p.maininscl <> '' AND p.ppcare >= '2' THEN p.pid end) AS 'non_uc', COUNT(case when p.nation = '099' AND p.maininscl = '' AND p.ppcare >= '2' THEN p.pid end) AS 'other_area', COUNT(case when p.nation <> '099' AND p.ppcare >= '2' THEN p.pid end) AS 'foreigner', COUNT(case when p.ppcare >= '2' THEN p.pid end) AS 'total' FROM _pp_care p INNER JOIN chospital h ON p.HOSPCODE=h.hoscode #INNER JOIN campur a on h.distcode=a.ampurcode GROUP BY p.HOSPCODE; DROP TABLE IF EXISTS _pp_care; #--------------Individual Record------------------------ /* SELECT p.HOSPCODE ,p.cid ,concat(p.`NAME`,' ',p.LNAME) AS 'name' ,d.MainInScl ,p.NATION ,l.BDATE ,l.BHOSP ,l.BRESULT ,a.BDATE ,a.PPCARE ,a.PPPLACE FROM labor l INNER JOIN person p ON l.pid=p.pid AND l.HOSPCODE=p.HOSPCODE INNER JOIN chospital h ON p.HOSPCODE=h.hoscode LEFT JOIN postnatal a ON l.pid=a.pid AND l.HOSPCODE=a.HOSPCODE LEFT JOIN dbpop d ON p.CID=d.PID WHERE l.BDATE BETWEEN '20160401' AND '20170331' AND l.BTYPE NOT IN(6) AND p.typearea in(1,3) AND p.discharge IN(9) #AND p.NATION <> '099' */ #PROVIDER1_SQL# |
Script Cron | |
Force Script Cron | 0 |
Active | 1 |
Client Office Type | |
Create Date | 2017-12-13 02:23:56 |
Last Update | 2018-05-18 11:28:45 |
- Home
- About
- Privacy Policy
- Terms of Use
- Contact Us
© 2015. All Rights Reserved.