· 3 months ago · Jul 04, 2025, 10:10 AM
1-- Table creation script for cascade_lvl00_progress_disease_codes
2-- Issue: memory limit
3
4CREATE TABLE IF NOT EXISTS cascade_lvl00_progress_disease_codes (
5 id Int32,
6 code_type String,
7 encounter_id Int32,
8 patient_id Int32,
9 employee_id Int32 DEFAULT NULL,
10 code String DEFAULT NULL,
11 title String,
12 role String,
13 diagnose_id Int32 DEFAULT NULL,
14 legal_entity_id Int32 DEFAULT NULL,
15 asserted_date DateTime,
16 ehealth_status Enum('not_sent'=0,'finished'=1, 'entered_in_error'=2, 'failed'=3, 'pending'=4, 'failed_with_error'=5)
17) ENGINE = MergeTree()
18ORDER BY tuple()
19
20-- This SQL script is used to import records into the `cascade_lvl00_progress_disease_codes` table.
21INSERT INTO cascade_lvl00_progress_disease_codes
22WITH icpc2_codes AS (
23 SELECT
24 buffer_classification_icpc2_items.*
25 FROM buffer_classification_icpc2_items
26 WHERE buffer_classification_icpc2_items.code IN
27 [
28 'A15', 'A17', 'A19', 'A30.1', 'A30.2', 'A63', 'A70', 'A79', 'A98', 'B16.0', 'B16.1', 'B16.2', 'B16.9',
29 'B17.1', 'B18.0', 'B18.1', 'B18.2', 'B72', 'B74', 'B90', 'D72', 'D74', 'D75', 'D76',
30 'D77', 'D78', 'E10', 'E11', 'E13', 'E14', 'E66', 'F10', 'G31.2',
31 'J65', 'K22', 'K23.0', 'K67.3', 'K74', 'K76', 'K85', 'K86',
32 'K87', 'L71', 'M01', 'M49', 'M90', 'N33.0', 'N74', 'N74.0', 'N74.1', 'N97',
33 'O00', 'O01', 'O02', 'O03', 'O04', 'O05', 'O06', 'O07', 'O08', 'O09', 'O24.4',
34 'O30', 'O98.0', 'P15', 'P16', 'P17', 'P19', 'P37.0', 'R03.0', 'R63.5', 'R79',
35 'R81', 'R82', 'R84', 'R85', 'R95', 'R96', 'T05', 'T07', 'T08', 'T65.2', 'T71',
36 'T82', 'T83', 'T89', 'T90', 'U28', 'U75', 'U76', 'U77', 'W71', 'W72', 'W75',
37 'W76', 'W78', 'W79', 'W80', 'W81', 'W82', 'W83', 'W84', 'W85', 'W90', 'W91',
38 'W92', 'W93', 'X70', 'X71', 'X72', 'X73', 'X74', 'X75', 'X76', 'X77', 'X90',
39 'X91', 'X92', 'Y70', 'Y71', 'Y72', 'Y73', 'Y74', 'Y75', 'Y76', 'Y77', 'Y78',
40 'Z01', 'Z02', 'Z03', 'Z03.0', 'Z06', 'Z20.6', 'Z58.7', 'Z72.0', 'Z72.5',
41 'Z80.0', 'Z80.3', 'Z80.4', 'Z82.4', 'Z83.3', 'Z86.43'
42 ]
43), icd10_codes AS (
44 SELECT buffer_classification_icd10_items.* FROM buffer_classification_icd10_items
45 WHERE buffer_classification_icd10_items.code IN [
46 'A15', 'A16', 'A17', 'A18', 'A19', 'A30.1', 'A30.2', 'A50', 'A51', 'A52',
47 'A53', 'A54', 'A55', 'A56', 'A57', 'A58', 'A59', 'A60', 'A61', 'A62', 'A63',
48 'A64', 'A70', 'A79', 'A98', 'B16.0', 'B16.1', 'B16.2', 'B16.9', 'B17.1', 'B18.0',
49 'B18.1', 'B18.2', 'B72', 'B74', 'B90', 'D12', 'D72', 'D74', 'D75', 'D76',
50 'D77', 'D78', 'E10', 'E11', 'E13', 'E14', 'E66', 'F10', 'F17', 'G31.2',
51 'J65', 'K22', 'K23.0', 'K50', 'K51', 'K67.3', 'K74', 'K76', 'K85', 'K86',
52 'K87', 'L71', 'M01', 'M49', 'M90', 'N33.0', 'N74', 'N74.0', 'N74.1', 'N97',
53 'O00', 'O01', 'O02', 'O03', 'O04', 'O05', 'O06', 'O07', 'O08', 'O09', 'O24.4',
54 'O30', 'O98.0', 'P15', 'P16', 'P17', 'P19', 'P37.0', 'R03.0', 'R63.5', 'R79',
55 'R81', 'R82', 'R84', 'R85', 'R95', 'R96', 'T05', 'T07', 'T08', 'T65.2', 'T71',
56 'T82', 'T83', 'T89', 'T90', 'U28', 'U75', 'U76', 'U77', 'W71', 'W72', 'W75',
57 'W76', 'W78', 'W79', 'W80', 'W81', 'W82', 'W83', 'W84', 'W85', 'W90', 'W91',
58 'W92', 'W93', 'X70', 'X71', 'X72', 'X73', 'X74', 'X75', 'X76', 'X77', 'X90',
59 'X91', 'X92', 'Y70', 'Y71', 'Y72', 'Y73', 'Y74', 'Y75', 'Y76', 'Y77', 'Y78',
60 'Z01', 'Z02', 'Z03', 'Z03.0', 'Z06', 'Z20.6', 'Z58.7', 'Z72.0', 'Z72.5',
61 'Z80.0', 'Z80.3', 'Z80.4', 'Z82.4', 'Z83.3', 'Z86.43'
62 ]
63), condition_icpc2_codes AS (
64 SELECT DISTINCT ON (id, diagnose_id, encounter_id)
65 ehr_conditions.id AS id,
66 ehr_diagnoses.encounter_id AS encounter_id,
67 ehr_conditions.patient_id AS patient_id,
68 ehr_conditions.employee_id AS employee_id,
69 classification_icpc2_items.code AS code,
70 classification_icpc2_items.title AS title,
71 classification_fhir_items.code AS role,
72 ehr_diagnoses.id as diagnose_id,
73 receptions.legal_entity_id AS legal_entity_id,
74 receptions.asserted_date AS asserted_date,
75 receptions.ehealth_status AS ehealth_status
76 FROM buffer_diagnoses ehr_diagnoses
77 JOIN buffer_conditions ehr_conditions ON ehr_diagnoses.condition_id = ehr_conditions.id
78 JOIN buffer_encounters receptions ON ehr_diagnoses.encounter_id = receptions.id
79 JOIN buffer_classification_fhir_items classification_fhir_items ON classification_fhir_items.type IN ('Classification::Fhir::ValueSet::DiagnoseRole') AND classification_fhir_items.id = ehr_diagnoses.role_id
80 JOIN icpc2_codes classification_icpc2_items ON classification_icpc2_items.id = ehr_conditions.icpc2_code_id
81 AND classification_icpc2_items.type = 'Classification::Icpc2::Condition'
82), condition_icd10_codes AS (
83 SELECT DISTINCT ON (id, diagnose_id, encounter_id)
84 ehr_conditions.id AS id,
85 ehr_diagnoses.encounter_id AS encounter_id,
86 ehr_conditions.patient_id AS patient_id,
87 ehr_conditions.employee_id AS employee_id,
88 cicd10i.code AS code,
89 cicd10i.title AS title,
90 classification_fhir_items.code as role,
91 ehr_diagnoses.id as diagnose_id,
92 receptions.legal_entity_id AS legal_entity_id,
93 receptions.asserted_date AS asserted_date,
94 receptions.ehealth_status AS ehealth_status
95 FROM buffer_diagnoses ehr_diagnoses
96 JOIN buffer_conditions ehr_conditions ON ehr_diagnoses.condition_id = ehr_conditions.id
97 JOIN buffer_encounters receptions ON ehr_diagnoses.encounter_id = receptions.id
98 JOIN buffer_classification_fhir_items classification_fhir_items ON classification_fhir_items.type IN ('Classification::Fhir::ValueSet::DiagnoseRole') AND classification_fhir_items.id = ehr_diagnoses.role_id
99 JOIN icd10_codes cicd10i ON cicd10i.id = ehr_conditions.icd10_code_id
100), reasons AS (
101 SELECT
102 br.id AS id,
103 br.encounter_id AS encounter_id,
104 be.patient_id AS patient_id,
105 be.employee_id AS employee_id,
106 bci2i.code AS code,
107 bci2i.title AS title,
108 NULL AS diagnose_id,
109 be.legal_entity_id AS legal_entity_id,
110 be.asserted_date AS asserted_date,
111 be.ehealth_status AS ehealth_status
112 FROM buffer_reasons br
113 JOIN icpc2_codes bci2i ON br.icpc2_reason_id = bci2i.id
114 JOIN buffer_encounters be ON be.id = br.encounter_id
115)
116SELECT
117 cic2c.id AS id,
118 'icpc2' AS code_type,
119 cic2c.encounter_id AS encounter_id,
120 cic2c.patient_id AS patient_id,
121 cic2c.employee_id AS employee_id,
122 cic2c.code AS code,
123 cic2c.title AS title,
124 cic2c.role AS role,
125 cic2c.diagnose_id AS diagnose_id,
126 cic2c.legal_entity_id AS legal_entity_id,
127 cic2c.asserted_date AS asserted_date,
128 cic2c.ehealth_status AS ehealth_status
129FROM condition_icpc2_codes cic2c
130UNION ALL
131SELECT
132 condition_icd10_codes.id AS id,
133 'icd10' AS code_type,
134 ci10c.encounter_id AS encounter_id,
135 ci10c.patient_id AS patient_id,
136 ci10c.employee_id AS employee_id,
137 ci10c.code AS code,
138 ci10c.title AS title,
139 ci10c.role AS role,
140 ci10c.diagnose_id AS diagnose_id,
141 ci10c.legal_entity_id AS legal_entity_id,
142 ci10c.asserted_date AS asserted_date,
143 ci10c.ehealth_status AS ehealth_status
144FROM condition_icd10_codes ci10c
145UNION ALL
146SELECT
147 r.id,
148 'reason_icpc2' AS code_type,
149 r.encounter_id AS encounter_id,
150 r.patient_id AS patient_id,
151 r.employee_id AS employee_id,
152 r.code AS code,
153 r.title AS title,
154 'reason' AS role,
155 r.diagnose_id,
156 r.legal_entity_id AS legal_entity_id,
157 r.asserted_date AS asserted_date,
158 r.ehealth_status AS ehealth_status
159FROM reasons r
160