NHSO_PP60-KPI09-DM_HT_Screen

Update Delete

ID784
Parent ID52
Table Name_pp60_kpi09_dm_ht
TitleNHSO_PP60-KPI09-DM_HT_Screen
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 @date_begin:=('2017-04-01');#Count 1 April
SET @date_end:=('2018-03-31');

SELECT
t.HOSPCODE
,COUNT(DISTINCT t.cid) AS 'total'
,COUNT(DISTINCT IF(t.NATION='099',t.cid,NULL)) AS 'th_total'
,COUNT(DISTINCT IF(t.NATION='099' AND s.INSTYPE='0100',t.cid,NULL)) AS 'th_uc'
,COUNT(DISTINCT IF(t.NATION='099' AND s.INSTYPE<>'0100' AND s.INSTYPE IS NOT NULL,t.cid,NULL)) AS 'th_nonuc'
,COUNT(DISTINCT IF(t.NATION='099' AND s.INSTYPE IS NULL,t.cid,NULL)) AS 'th_na'
,COUNT(DISTINCT IF(t.NATION<>'099',t.cid,NULL)) AS 'foreign'
FROM
(SELECT * FROM
(SELECT n.hospcode,n.pid,p.cid,n.seq,p.nation,n.DATE_SERV,p.BIRTH
FROM ncdscreen n
INNER JOIN t_person_db p ON n.hospcode=p.hospcode AND n.pid=p.pid
WHERE n.DATE_SERV BETWEEN @date_begin AND @date_end
AND ((date_format(n.DATE_SERV, '%Y')- date_format(p.BIRTH, '%Y'))-(date_format(n.DATE_SERV,'00-%m-%d') < date_format(p.BIRTH, '00-%m-%d')))BETWEEN 30 and 120 #คำนวณอายุจาก n,DATE_SERV-p.BIRTH
AND p.CID NOT IN(SELECT cid from t_dmht)
ORDER BY n.DATE_SERV DESC) t1 #เรียงให้วันบริการสุดท้าย
GROUP BY t1.cid #ตัดคนซ้ำ
)t
LEFT JOIN service s ON t.HOSPCODE = s.HOSPCODE AND t.PID = s.PID AND t.SEQ = s.SEQ #เชื่อมกับแฟ้มservice เพื่อเอาสิทธิรักษา
WHERE s.DATE_SERV BETWEEN @date_begin AND @date_end
GROUP BY t.HOSPCODE;

#PROVIDER1_SQL#
Script Cron
Force Script Cron0
Active1
Client Office Type
Create Date2017-12-13 02:37:30
Last Update2018-05-18 11:29:54