NHSO_PP60-KPI03-Postnatal

Update Delete

ID778
Parent ID52
Table Name_pp60_kpi03_postnatal
TitleNHSO_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 Cron0
Active1
Client Office Type
Create Date2017-12-13 02:23:56
Last Update2018-05-18 11:28:45