API tools faq
paste
Advertisement
temaon_lieto

cascade_lvl00_progress_disease_codes

temaon_lieto
Jul 4th, 2025
95
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 7.88 KB | None | 0 0
raw download clone embed print report
  1. -- Table creation script for cascade_lvl00_progress_disease_codes
  2. -- Issue: memory limit
  3.  
  4. CREATE 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()
  18. ORDER BY tuple()
  19.  
  20. -- This SQL script is used to import records into the `cascade_lvl00_progress_disease_codes` table.
  21. INSERT INTO cascade_lvl00_progress_disease_codes
  22. WITH 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. )
  116. SELECT
  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
  129. FROM condition_icpc2_codes cic2c
  130. UNION ALL
  131. SELECT
  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
  144. FROM condition_icd10_codes ci10c
  145. UNION ALL
  146. SELECT
  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
  159. FROM reasons r
  160.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!