QOF 06 Pepsmear 30-60

Update Delete

ID744
Parent ID50
Table Nameqof62_kpi06_Pepsmear_30_60
TitleQOF 06 Pepsmear 30-60
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;


# QOF60 คัดกรองมะเร็งปาดมดลูกในสตรี 30 - 60 ปี

SET @start_a:='2015-04-01';
SET @end_a:='2020-03-31';


CREATE TEMPORARY TABLE `_tmp` (
`hospcode` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
`pid` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
`seq` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`date_serv` date NULL DEFAULT '0000-00-00',
`diagcode` varchar(6) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
`file` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
`cid` char(13) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
INDEX `cid`(`cid`) USING BTREE
)

SELECT o.hospcode,o.pid,o.seq,o.date_serv,o.diagcode,'Diag' as file ,p.cid
FROM diagnosis_opd o INNER JOIN person p ON o.hospcode=p.hospcode AND o.pid=p.pid
WHERE o.DATE_SERV BETWEEN @start_a AND @end_a
AND SUBSTR(o.DIAGCODE,1,4) IN('Z014','Z124')

UNION
SELECT s.HOSPCODE ,s.pid ,s.SEQ,s.DATE_SERV,s.PPSPECIAL,'SPECIALPP' as file ,p.cid
FROM specialpp s INNER JOIN person p ON s.hospcode=p.hospcode AND s.pid=p.pid
WHERE s.DATE_SERV BETWEEN @start_a AND @end_a
AND SUBSTR(s.ppspecial,1,5) IN('1B30','1B40','1B004') AND LENGTH(p.cid)=13;


SELECT c.HOSPCODE AS check_hosp,c.check_vhid,c.CID AS cid ,MAX(b.date_serv) AS DATE_SERV,b.diagcode AS DIAGCODE,c.NATION
from
t_person_cid c
LEFT OUTER JOIN _tmp b on c.CID = b.cid

WHERE c.BIRTH BETWEEN '1958-04-01' AND '1988-03-31' AND c.sex IN(2)


GROUP BY c.HOSPCODE,c.cid
ORDER BY b.date_serv DESC

#PROVIDER1_SQL#
Script Cron
Force Script Cron0
Active1
Client Office Type
Create Date2017-06-01 09:11:46
Last Update2020-04-21 01:56:30