NHSO_PP60-KPI04-FP
ID | 779 |
---|---|
Parent ID | 52 |
Table Name | _pp60_kpi04_fp |
Title | NHSO_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 Cron | 0 |
Active | 1 |
Client Office Type | |
Create Date | 2017-12-13 02:26:14 |
Last Update | 2018-05-18 11:29:08 |
- Home
- About
- Privacy Policy
- Terms of Use
- Contact Us
© 2015. All Rights Reserved.