QOF-64 (4)Stroke
ID | 821 |
---|---|
Parent ID | 61 |
Table Name | qof64_kpi04_stroke |
Title | QOF-64 (4)Stroke |
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_s = '2020-04-01'; SET @date_e = '2021-03-31'; SET SESSION group_concat_max_len = 1000000; DROP TABLE IF EXISTS _qof64_stroke_001_opd_now; CREATE TABLE IF NOT EXISTS _qof64_stroke_001_opd_now SELECT d.CID, MIN(d.DATE_SERV) AS DATE_SERV, group_concat(concat(d.hospcode,'(',d.DIAGTYPE,')','(',d.DIAGCODE,')') ORDER BY hospcode) as dx FROM diagnosis_opd AS d INNER JOIN chospital h ON d.hospcode=h.hoscode WHERE d.DATE_SERV BETWEEN @date_s AND @date_e AND SUBSTR(d.DIAGCODE FROM 1 FOR 3) BETWEEN 'i60' AND 'i69' AND d.cid IS NOT NULL AND h.hostype in('05','06','07','12','15') GROUP BY d.CID ; DROP TABLE IF EXISTS _qof64_stroke_002_opd_old; CREATE TABLE IF NOT EXISTS _qof64_stroke_002_opd_old SELECT d.CID, MIN(d.DATE_SERV) AS DATE_SERV, group_concat(concat(d.hospcode,'(',d.DIAGTYPE,')','(',d.DIAGCODE,')') ORDER BY hospcode) as dx FROM diagnosis_opd AS d INNER JOIN chospital h ON d.hospcode=h.hoscode WHERE d.DATE_SERV < @date_s AND SUBSTR(d.DIAGCODE FROM 1 FOR 3) BETWEEN 'i60' AND 'i69' AND d.cid IS NOT NULL AND h.hostype in('05','06','07','12','15') GROUP BY d.CID ; DROP TABLE IF EXISTS _qof64_stroke_003_chronic; CREATE TABLE IF NOT EXISTS _qof64_stroke_003_chronic SELECT d.CID, MIN(d.DATE_DIAG) AS dx, group_concat(concat(d.hospcode,'(',d.hosp_dx,')','(',d.hosp_rx,')') ORDER BY d.hospcode) as hosdx FROM chronic AS d INNER JOIN chospital h ON d.hospcode=h.hoscode WHERE SUBSTR(d.CHRONIC FROM 1 FOR 3) BETWEEN 'i60' AND 'i69' AND d.cid IS NOT NULL AND h.hostype in('05','06','07','12','15') GROUP BY d.CID ; DROP TABLE IF EXISTS _qof64_stroke_004_ht; CREATE TABLE IF NOT EXISTS _qof64_stroke_004_ht SELECT c.cid, c.birth, c.age_y, c.age_y_dx, c.sex, c.nation, c.p_hospcode, c.d_hospcode, c.p_pt_vhid, c.d_pt_vhid, c.p_typearea, c.d_typearea, c.input_hosp, c.input_pid, c.source_tb, c.diagcode, min(c.date_dx) as date_dx, c.hosp_dx, c.hosp_rx, c.typedisch, CASE WHEN c.date_dx <@date_s THEN 'oldcase' ELSE 'newcase' END as pt_status FROM t_chronic AS c WHERE SUBSTRING(c.diagcode FROM 1 FOR 3) BETWEEN 'i10' AND 'i15' AND mod11(c.cid)=1 AND c.source_tb='chronic' AND c.date_dx<@date_s AND c.typedisch='03' AND c.nation in('99','099') GROUP BY c.cid ; DROP TABLE IF EXISTS _qof64_stroke_004_opd_new; CREATE TABLE IF NOT EXISTS _qof64_stroke_004_opd_new SELECT n.CID, n.DATE_SERV, n.dx FROM _qof64_stroke_001_opd_now AS n LEFT JOIN _qof64_stroke_002_opd_old AS o ON n.CID = o.CID WHERE o.DATE_SERV IS NULL ; DROP TABLE IF EXISTS _qof64_stroke_005_opd_newest; CREATE TABLE IF NOT EXISTS _qof64_stroke_005_opd_newest SELECT o.CID, o.DATE_SERV, o.dx FROM _qof64_stroke_004_opd_new AS o WHERE o.CID NOT IN ( SELECT c.CID FROM _qof64_stroke_003_chronic AS c WHERE c.dx < @date_s ) ; INSERT INTO _qof64_stroke_005_opd_newest ( _qof64_stroke_005_opd_newest.CID, _qof64_stroke_005_opd_newest.DATE_SERV, _qof64_stroke_005_opd_newest.dx ) SELECT _qof64_stroke_003_chronic.CID, _qof64_stroke_003_chronic.dx, _qof64_stroke_003_chronic.hosdx FROM _qof64_stroke_003_chronic WHERE _qof64_stroke_003_chronic.dx >= @date_s ; DROP TABLE IF EXISTS _qof64_stroke_006_x; CREATE TABLE IF NOT EXISTS _qof64_stroke_006_x SELECT ht.cid, ht.p_hospcode, ht.birth, ht.age_y, ht.nation, ht.p_typearea, ht.date_dx, n.DATE_SERV AS date_serv_new, n.dx AS hospcode_dx_new, o.DATE_SERV AS date_serv_old, o.dx AS hospcode_dx_old FROM _qof64_stroke_004_ht ht LEFT JOIN _qof64_stroke_005_opd_newest n ON ht.cid = n.CID LEFT JOIN _qof64_stroke_002_opd_old o ON ht.cid = o.CID ; DROP TABLE IF EXISTS _qof64_stroke_001_opd_now; DROP TABLE IF EXISTS _qof64_stroke_002_opd_old; DROP TABLE IF EXISTS _qof64_stroke_003_chronic; DROP TABLE IF EXISTS _qof64_stroke_004_ht; DROP TABLE IF EXISTS _qof64_stroke_004_opd_new; DROP TABLE IF EXISTS _qof64_stroke_005_opd_newest; SELECT * FROM _qof64_stroke_006_x; # test summary #Complete #PROVIDER1_SQL# |
Script Cron | |
Force Script Cron | 0 |
Active | 1 |
Client Office Type | |
Create Date | 2020-09-22 09:44:04 |
Last Update | 2020-10-27 09:56:39 |
- Home
- About
- Privacy Policy
- Terms of Use
- Contact Us
© 2015. All Rights Reserved.