issued-sql

INSERT INTO cascade_lvl02_discharge_encounters(
    id,
    history_number,
    order_via_history_number,
    line_number,
    major,
    minor,
    encounter_id,
    discharge_disposition_code,
    asserted_date,
    period_start_date,
    main_icd10_code,
    birth_date,
    bed_days_counter,
    legal_entity_id,
    patient_id,
    patient_age,
    patient_age_month,
    patient_weight,
    is_plurality_pregnancies
)
WITH patients AS (SELECT id,
                         birth_date,
                         legal_entity_id
                  FROM cascade_lvl01_patients),
    bed_days_encounters AS (
        SELECT
            bde.history_number,
            toDateTime(MIN(bde.period_start), 'Europe/Kyiv') AS period_start,
            toDateTime(MAX(bde.asserted_date), 'Europe/Kyiv') AS asserted_date,
        CASE
            WHEN period_start IS NULL THEN 0
            ELSE
                CASE
                    WHEN asserted_date IS NULL OR asserted_date < period_start THEN 0
                    ELSE
                        CASE dateDiff('day', period_start, asserted_date)
                            WHEN 0 THEN 1
                            ELSE dateDiff('day', period_start, asserted_date)
                            END
                    END
            END as bed_days_counter,
            bde.patient_id,
            argMin(bde.encounter_id, bde.order_via_history_number) AS encounter_id,
            argMin(bde.episode_id, bde.order_via_history_number) AS episode_id,
            argMin(bde.discharge_disposition_code, bde.order_via_history_number) AS discharge_disposition_code,
            MIN(bde.order_via_history_number) AS order_via_history_number,
            any(bde.legal_entity_id) AS legal_entity_id
        FROM cascade_lvl00_bed_days_encounters bde
        WHERE bde.legal_entity_id = 3387
        GROUP BY bde.patient_id, bde.history_number
    )
SELECT
    first_value(e.episode_id)                                                    AS id,
    e.history_number                                                              AS history_number,
    e.order_via_history_number                                                    AS order_via_history_number,
    nullIf(dt3220i10m.line_number, '')                                            AS line_number,
    CAST(splitByString('.', CAST(dt3220i10m.line_number AS String))[1] AS UInt16) AS major,
    CAST(
            splitByString('.', CAST(dt3220i10m.line_number AS String))[2] AS
        Nullable(UInt16))                                                     AS minor,
    e.encounter_id                                                                AS encounter_id,
    e.discharge_disposition_code                                                  as discharge_disposition_code,
    toDate(e.asserted_date)                                                       AS asserted_date,
    e.period_start                                                                AS period_start_date,
    CASE
        WHEN discharge_disposition_code = 'death'
            THEN coalesce(nullIf(fic.icd10_code, ''), nullIf(ic.icd10_code, ''))
        ELSE nullIf(ic.icd10_code, '')
        END                                                                               AS main_icd10_code,
    p.birth_date                                                                  AS birth_date,
    e.bed_days_counter                                                            AS bed_days_counter,
    e.legal_entity_id                                                             AS legal_entity_id,
    e.patient_id                                                                  AS patient_id,
    CASE
        WHEN birth_date IS NULL OR toDate(birth_date) > toDate(now()) THEN 199
        ELSE age('year', toDate(birth_date), toDate(COALESCE(period_start_date, asserted_date)))
        END AS patient_age,
    CASE
        WHEN birth_date IS NULL OR toDate(birth_date) > toDate(now()) THEN 2388
        ELSE age('month', toDate(birth_date), toDate(COALESCE(period_start_date, asserted_date)))
        END AS patient_age_month,
    iwo.value                                                                     AS patient_weight,
    iwo.is_plurality_pregnancies                                                  AS is_plurality_pregnancies
FROM bed_days_encounters e
         LEFT JOIN cascade_lvl00_form066_icd10_codes fic ON fic.episode_id = e.episode_id
         LEFT JOIN cascade_lvl00_primary_icd10_codes ic ON ic.id = e.encounter_id
         LEFT JOIN cascade_lvl00_infant_weight_observations iwo
                   ON iwo.episode_id = e.episode_id
         LEFT JOIN dict_table3220_icd10_mappers dt3220i10m
                   ON ic.icd10_code = dt3220i10m.code OR fic.icd10_code = dt3220i10m.code
         JOIN patients p ON e.patient_id = p.id
WHERE main_icd10_code IS NOT NULL AND line_number IS NOT NULL
GROUP BY
    e.history_number,
    e.order_via_history_number,
    dt3220i10m.line_number,
    major,
    minor,
    e.encounter_id,
    e.discharge_disposition_code,
    asserted_date,
    e.period_start,
    main_icd10_code,
    p.birth_date,
    e.bed_days_counter,
    e.legal_entity_id,
    e.patient_id,
    patient_age,
    patient_age_month,
    iwo.value,
    iwo.is_plurality_pregnancies