NHSO_PP60-KPI08-pap_smear

Update Delete

ID762
Parent ID52
Table Name_pp60_kpi08_pap_smear
TitleNHSO_PP60-KPI08-pap_smear
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.HOSPCODE,t.PID,t.DIAGCODE) AS 'total'
,COUNT(DISTINCT IF(t.NATION='099',CONCAT(t.HOSPCODE,t.PID,t.DIAGCODE),NULL)) AS 'th_total'
,COUNT(DISTINCT IF(t.NATION='099' AND t.INSTYPE='0100',CONCAT(t.HOSPCODE,t.PID,t.DIAGCODE),NULL)) AS 'th_uc'
,COUNT(DISTINCT IF(t.NATION='099' AND t.INSTYPE<>'0100' AND t.INSTYPE IS NOT NULL,CONCAT(t.HOSPCODE,t.PID,t.DIAGCODE),NULL)) AS 'th_nonuc'
,COUNT(DISTINCT IF(t.NATION='099' AND t.INSTYPE IS NULL,CONCAT(t.HOSPCODE,t.PID,t.DIAGCODE),NULL)) AS 'th_na'
,COUNT(DISTINCT IF(t.NATION<>'099',CONCAT(t.HOSPCODE,t.PID,t.DIAGCODE),NULL)) AS 'foreign'
FROM
(SELECT
a.*,
s.INSTYPE,
cin.instypename,
cin.instypegroup
FROM
((
SELECT
o.hospcode,
p.cid,
p.nation,
o.pid,
o.DIAGCODE,
o.DATE_SERV
FROM diagnosis_opd o
INNER JOIN t_person_db p ON o.hospcode=p.hospcode AND o.pid=p.pid
WHERE o.DATE_SERV BETWEEN @date_begin AND @date_end
AND SUBSTR(o.DIAGCODE,1,4) IN('Z014','Z124')
GROUP BY o.HOSPCODE,o.PID,o.SEQ )
union
(SELECT sp.hospcode,tp.cid,tp.nation,sp.pid,sp.PPSPECIAL,sp.DATE_SERV
FROM tmp_specialpp sp
INNER JOIN t_person_db tp ON sp.hospcode = tp.hospcode AND sp.pid = tp.pid
WHERE sp.DATE_SERV BETWEEN @date_begin AND @date_end
AND SUBSTR(sp.ppspecial,1,5) IN('1B004','IB30','1B40') AND LENGTH(sp.cid)=13
GROUP BY sp.CID ))a
LEFT JOIN service s ON a.HOSPCODE = s.HOSPCODE AND a.PID = s.PID
LEFT JOIN cinstype_new cin ON s.INSTYPE=cin.instypecode
group by a.cid
ORDER BY a.CID,s.instype ASC) t
GROUP BY t.HOSPCODE;



#PROVIDER1_SQL#
Script Cron
Force Script Cron0
Active1
Client Office Type
Create Date2017-08-15 03:14:37
Last Update2018-05-21 02:59:21