• Home
  • My Scripts
  • รายงาน ANC5 ครั้งตามเกณฑ์

รายงาน ANC5 ครั้งตามเกณฑ์

Update Delete

ID721
Parent ID37
Table Nameanc5region2
Titleรายงาน ANC5 ครั้งตามเกณฑ์
Description
Script#SQL_OPTIONS#
PROVIDERS=1
PROVIDER1=HDC
PROVIDER1_VALIDATE_TABLES=anc,person,chospital
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;

/*SQL Script สำหรับ JHCIS เริ่มที่นี่ */
SET @ds1:='20151001', /* Start date */
@ds2:='20160930', /* End date */
@year_fiscal:='2559',
@codeprocess:='Q0002_tri' ; /* Code Process*/

SELECT @year_fiscal year_fiscal
,b.hospcode
,b.hospname
,b.target
,a.result_t1+a.result_t2+a.result_t3+a.result_t4 resulttotal
,a.result_t1
,a.result_t2
,a.result_t3
,a.result_t4
,@codeprocess process_code
,now() query_datetime
FROM (
/* ตัวตั้ง */
SELECT h.hoscode hospcode
,h.hosname hospname
,a.t1 result_t1
,a.t2 result_t2
,a.t3 result_t3
,a.t4 result_t4
FROM chospital h
INNER JOIN (
select hospcode
,count(distinct if(t1 = '1' and ifnull(t1,0) + ifnull(t2,0) + ifnull(t3,0) + ifnull(t4,0) = 1, t.cid, null)) t1
,count(distinct if(t2 = '1' and ifnull(t2,0) + ifnull(t3,0) + ifnull(t4,0) = 1, t.cid, null)) t2
,count(distinct if(t3 = '1' and ifnull(t3,0) + ifnull(t4,0) = 1, t.cid, null)) t3
,count(distinct if(t4 = '1' , t.cid, null)) t4
from nhso_tmp_anc5time_trimart t
group by hospcode
) a ON a.hospcode=h.hoscode
) a
RIGHT JOIN (
/* ตัวหาร */
SELECT h.hoscode hospcode
,h.hosname hospname
,a.target
FROM chospital h
INNER JOIN (
SELECT p.hospcode hospcode
,COUNT(DISTINCT l.cid) target
FROM nhso_tmp_labor_anc5time l
LEFT JOIN person p ON p.cid=l.cid
WHERE p.typearea IN ('1','3')
AND p.DISCHARGE = '9'
GROUP BY p.hospcode
) a ON a.hospcode=h.hoscode
) b ON a.hospcode = b.hospcode
;
/*SQL Script สำหรับ JHCIS สิ้นสุดที่นี่ */


#PROVIDER1_SQL#

Script Cron
Force Script Cron0
Active1
Client Office Type
Create Date2016-11-23 09:49:07
Last Update2016-11-23 01:07:12