QOF-64 (4)Stroke

Update Delete

ID821
Parent ID61
Table Nameqof64_kpi04_stroke
TitleQOF-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 Cron0
Active1
Client Office Type
Create Date2020-09-22 09:44:04
Last Update2020-10-27 09:56:39