NHSO_PP60-KPI04-FP

Update Delete

ID779
Parent ID52
Table Name_pp60_kpi04_fp
TitleNHSO_PP60-KPI04-FP
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');

SELECT
#campur.ampurcodefull,campur.ampurname AS อำเภอ,chospital.hoscode,chospital.hosname AS ชื่อหน่วยบริการ,
CONCAT(@prov_c,chospital.distcode) AS 'code_ampher',
chospital.hoscode,chospital.hosname AS 'hosname',
count(DISTINCT fp.HOSPCODE,fp.PID,fp.SEQ, case when POP.nation = '099' AND DB.MainInScl in('UCS','Wel') THEN 1 END) AS 'uc_and_wel',
count(DISTINCT fp.HOSPCODE,fp.PID,fp.SEQ, case when POP.nation = '099' AND DB.MainInScl NOT in('UCS','Wel') AND DB.MainInScl is NOT NULL THEN 1 END) AS 'non_uc',
count(DISTINCT fp.HOSPCODE,fp.PID,fp.SEQ, case when POP.nation = '099' AND DB.MainInScl is NULL THEN 1 END) AS 'other_area',
count(DISTINCT fp.HOSPCODE,fp.PID,fp.SEQ, case when POP.nation <> '099' THEN 1 END) AS 'foreigner',
count(DISTINCT fp.HOSPCODE,fp.PID,fp.SEQ) AS 'total'
#----ปัญหาที่ผลรวมไม่เท่ากับบวกทั้ง 4 ช่องมาเนื่องจากปัญหา DBpop ติดปัญหา 1 CID มี 2 สิทธิ-------------

FROM fp
Left JOIN person POP ON fp.HOSPCODE = POP.HOSPCODE AND fp.PID=POP.PID
LEFT JOIN dbpop DB ON POP.cid = DB.PID
Left JOIN chospital on chospital.hoscode = POP.HOSPCODE
#Left JOIN campur on campur.ampurcodefull = CONCAT(@prov_c,chospital.distcode)


WHERE
fp.DATE_SERV BETWEEN @start_d AND @end_d
AND fp.FPTYPE in('1','2','3','4','5','6','7')
AND fp.HOSPCODE=fp.FPPLACE
AND (fp.SEQ <> '0' AND fp.SEQ IS NOT NULL AND fp.SEQ <>'')

#GROUP BY POP.hospcode WITH ROLLUP
group by fp.HOSPCODE
ORDER BY @prov_c,chospital.distcode,fp.HOSPCODE ASC;



#-------------Individual Record-------------------

/*
SET @prov_c := :provcode;
SET @start_d:=('2016-04-01');#Count 1 April
SET @end_d:=('2017-03-31');

SELECT
#campur.ampurcodefull,campur.ampurname AS อำเภอ,chospital.hoscode,chospital.hosname AS ชื่อหน่วยบริการ,
CONCAT(@prov_c,chospital.distcode) AS 'codeAmpher',fp.HOSPCODE,chospital.hosname AS ชื่อหน่วยบริการ,
fp.PID,fp.SEQ,fp.FPPLACE,fp.FPTYPE,
POP.CID,POP.`NAME`,POP.LNAME,POP.BIRTH,
POP.nation,POP.typearea,DB.MainInScl


FROM fp
Left JOIN person POP ON fp.HOSPCODE = POP.HOSPCODE AND fp.PID=POP.PID
LEFT JOIN dbpop DB ON POP.cid = DB.PID
Left JOIN chospital on chospital.hoscode = POP.HOSPCODE
#Left JOIN campur on campur.ampurcodefull = CONCAT(@prov_c,chospital.distcode)


WHERE
fp.DATE_SERV BETWEEN @start_d AND @end_d
AND fp.FPTYPE in('1','2','3','4','5','6','7')
AND fp.HOSPCODE=fp.FPPLACE
AND (fp.SEQ <> '0' AND fp.SEQ IS NOT NULL AND fp.SEQ <>'')

#AND POP.nation = '099'
#AND DB.MainInScl in('UCS','Wel')

#AND DB.MainInScl NOT in('UCS','Wel')
#AND DB.MainInScl IS NOT NULL

#AND DB.MainInScl IS NULL


#GROUP BY POP.hospcode WITH ROLLUP
group by fp.HOSPCODE,fp.PID,fp.SEQ
ORDER BY @prov_c,chospital.distcode,fp.HOSPCODE ASC;
*/

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