Transformations

ComponentSchema

---

SQL

-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_int_transformations_{{ sub_version }} 
-- Subversion: {{ sub_version }}


-- Define gross charge sources and suffixes
{% set gross_charge_cols = [
    ("mrf_gross_charge_provider", "gc_hosp"),
    ("mrf_gross_charge_cbsa_median", "gc_hosp_cbsa"),
    ("mrf_gross_charge_state_median", "gc_hosp_state"),
    ("komodo_gross_charge_provider", "gc_komodo"),
    ("komodo_gross_charge_cbsa_median", "gc_komodo_cbsa"),
    ("komodo_gross_charge_state_median", "gc_komodo_state"),
] %}

{% set gross_charge_stats = cld_params.GROSS_CHARGE_STATS %}

-- Define rate columns and prefixes
{% set rate_cols = [
    ("payer_percentage_rate", "payer"),
    ("hospital_case_rate_percentage", "hospital_case_rate"),
    ("hospital_percent_of_total_billed_charges_percentage", "hospital_perc_of_total_billed_charges"),
    ("hospital_fee_schedule_percentage", "hospital_fee_schedule"),
    ("hospital_other_percentage", "hospital_other"),
    ("hospital_null_methodology_percentage", "hospital_null_methodology")
] %}


CREATE TABLE {{ schema_name }}.tmp_int_transformations_{{ sub_version }} 
WITH (
    PARTITIONING = ARRAY['payer_id']
)
AS
WITH

{%- set contract_methodology = ['case rate', 'percent of total billed charges', 'fee schedule', 'other', None] %}
{%- set negotiated_types = ['negotiated', 'derived', 'fee_schedule'] %}

-- Per Diem Transformation
per_diem_transformation AS (
    SELECT 
        msdrg as billing_code, 
        'MS-DRG' as billing_code_type,
        weights, 
        geometric_mean_los as glos, 
        arithmetic_mean_los as alos
    FROM {{ cld_params.Tables.REF_CMS_MSDRG_WEIGHTS.value }}
    WHERE is_latest_start_effective_date = true
    UNION ALL
    SELECT 
        c.apr_drg,
        'APR-DRG' as billing_code_type,
        avg(r.weights) as weights,
        avg(r.geometric_mean_los) as glos,
        avg(r.arithmetic_mean_los) as alos
    FROM {{ cld_params.Tables.REF_CMS_MSDRG_WEIGHTS.value }} r
    JOIN {{ cld_params.Tables.APR_DRG_XWALK_TABLE.value }} c
        ON r.msdrg = c.ms_drg
    WHERE r.is_latest_start_effective_date = true
    GROUP BY 1
),

-- Drug Dosage Transformation
asp AS (
    SELECT
        hcpcs, 
        quantity,
        unit_label,
        CAST(payment_limit AS DOUBLE) as asp_payment_limit
    FROM (
        SELECT 
            hcpcs, 
            REPLACE(COALESCE(REGEXP_EXTRACT(dosage, '[0-9.]+'), '1'),',','') as quantity,
            COALESCE(REGEXP_REPLACE(dosage, '[0-9.]+', ''), '') as unit_label,
            payment_limit
        FROM {{ cld_params.Tables.ASP_PRICING_TABLE.value }}
        WHERE is_latest_start_effective_date = true
    )
),
drug_transformation AS (
    SELECT 
        r.hospital_rates_id,
        r.roid,
        r.description,
        r.negotiated_dollar,
        r.contract_methodology,
        dd.parsed_quantity,
        dd.parsed_unit_label,
        asp.quantity as asp_quantity,
        asp.unit_label as asp_unit_label,
        CASE
            WHEN (
                    dd.parsed_quantity IS NOT NULL AND 
                    CAST(dd.parsed_quantity AS DECIMAL(18, 2)) > 0
                )
                THEN (
                    -- dosage standardize
                    (r.negotiated_dollar / CAST(dd.parsed_quantity AS DECIMAL(18, 2))) * CAST(asp.quantity as DECIMAL(18, 2))
                )
            ELSE NULL
        END as dosage_standardized_dollar,
        CASE
            WHEN (
                    dd.parsed_quantity IS NOT NULL AND 
                    CAST(dd.parsed_quantity AS DECIMAL(18, 2)) > 0
                )
                THEN (
                    -- dosage standardize
                    (.01 * gc.mrf_gross_charge_provider * r.negotiated_percentage / CAST(dd.parsed_quantity AS DECIMAL(18, 2))) * CAST(asp.quantity as DECIMAL(18, 2))
                )
            ELSE NULL
        END as dosage_standardized_percentage
    FROM {{ schema_name }}.tmp_raw_mrf_hospital_rates_{{ sub_version }} r
    LEFT JOIN {{ schema_name }}.tmp_raw_gross_charges_{{ sub_version }} gc
        ON r.roid = gc.roid
        AND r.payer_id = gc.payer_id
    JOIN (
        SELECT 
            description, 
            code, 
            any_value(parsed_quantity) as parsed_quantity, 
            any_value(parsed_unit_label) as parsed_unit_label
        FROM {{ drug_dosage_std_table }}
        WHERE parsed_quantity != ''
        GROUP BY description, code
    ) dd
        ON r.description = dd.description
        AND r.billing_code = dd.code
    JOIN asp
        on asp.hcpcs = r.billing_code
    WHERE negotiated_dollar > 0 OR negotiated_percentage > 0
),

{%- for method in contract_methodology %}
drug_transformation_{{ (method if method else 'null_methodology') | replace(' ', '_') }} AS (
    SELECT 
        hospital_rates_id, 
        parsed_quantity,
        asp_quantity,
        parsed_unit_label,
        asp_unit_label,
        roid, 
        dosage_standardized_dollar, 
        dosage_standardized_percentage
    FROM drug_transformation
    WHERE contract_methodology {{ 'IS NULL' if method is none else "= '" + method + "'" }}
){% if not loop.last %},{% endif %}
{%- endfor %}


SELECT 
    r.roid,
    r.billing_code,
    r.billing_code_type,
    r.bill_type,
    r.provider_id,
    r.provider_type,
    
    -- Gross Charges
    {% for gross_col, suffix in gross_charge_cols %}
        r.{{ gross_col }},
    {% endfor %}

    -- Gross Charge Stats
    {% for stat in gross_charge_stats %}
        r.{{ stat }} AS {{ stat }},
    {% endfor %}

    pdt.glos as medicare_glos,

    -- percentages 
    -- IMPORTANT! - columns must end with "_perc_to_dol!" for accuracy score computations
    -- Generate all combinations
    {% for rate_col, rate_prefix in rate_cols %}
        {% for gross_col, suffix in gross_charge_cols %}
            {% if '_cbsa_' in gross_col %}
                .01 * r.{{ gross_col }} * r.{{ rate_col }} * r.gross_charge_cbsa_provider_adjustment
            {% elif '_state_' in gross_col %}
                .01 * r.{{ gross_col }} * r.{{ rate_col }} * r.gross_charge_state_provider_adjustment
            {% else %}
                .01 * r.{{ gross_col }} * r.{{ rate_col }}
            {% endif %}
            as {{ rate_prefix }}_{{ suffix }}_perc_to_dol,
        {% endfor %}
    {% endfor %}

    -- per diem + percentages
    {% set per_diem_rate = "hospital_per_diem_percentage" %}
    {% set per_diem_prefix = "hosp_gc_per_diem" %}

    {% for gross_col, suffix in gross_charge_cols %}
        {% if '_cbsa_' in gross_col %}
            .01 * r.{{ gross_col }} * r.{{ per_diem_rate }} * r.gross_charge_cbsa_provider_adjustment
        {% elif '_state_' in gross_col %}
            .01 * r.{{ gross_col }} * r.{{ per_diem_rate }} * r.gross_charge_state_provider_adjustment
        {% else %}
            .01 * r.{{ gross_col }} * r.{{ per_diem_rate }}
        {% endif %}
        as hosp_{{ suffix }}_per_diem_perc_to_dol_mult_glos,
    {% endfor %}

    -- per diem
    COALESCE(pdt.glos, 1) * r.payer_per_diem_rate as payer_per_diem_mult_glos,
    COALESCE(pdt.glos, 1) * r.hospital_per_diem_dollar as hosp_per_diem_mult_glos,

    -- drug dosage standardized
    {%- for method in contract_methodology %}
        dt{{ loop.index }}.dosage_standardized_dollar AS drug_dosage_std_{{ 
                (method if method else 'null_methodology') | replace(' ', '_') 
            }}_dollar,
        dt{{ loop.index }}.dosage_standardized_percentage AS drug_dosage_std_{{
            (method if method else 'null_methodology') | replace(' ', '_')
        }}_perc_to_dol,
        dt{{ loop.index }}.parsed_quantity AS drug_dosage_std_{{
            (method if method else 'null_methodology') | replace(' ', '_')
        }}_parsed_quantity,
        dt{{ loop.index }}.asp_quantity AS drug_dosage_std_{{
            (method if method else 'null_methodology') | replace(' ', '_')
        }}_asp_quantity,
        dt{{ loop.index }}.parsed_unit_label AS drug_dosage_std_{{
            (method if method else 'null_methodology') | replace(' ', '_')
        }}_parsed_unit_label,
        dt{{ loop.index }}.asp_unit_label AS drug_dosage_std_{{
            (method if method else 'null_methodology') | replace(' ', '_')
        }}_asp_unit_label,
        dt{{ loop.index }}.hospital_rates_id AS drug_dosage_std_{{ 
            (method if method else 'null_methodology') | replace(' ', '_') 
        }}_dollar_id
        {% if not loop.last %},{% endif %}
    {%- endfor %},

    -- parsed_quantity, asp_quantity, and unit labels
    COALESCE(
        {%- for method in contract_methodology %}
            dt{{ loop.index }}.parsed_quantity
            {% if not loop.last %},{% endif %}
        {%- endfor %}
    ) as drug_parsed_quantity,
    COALESCE(
        {%- for method in contract_methodology %}
            dt{{ loop.index }}.asp_quantity
            {% if not loop.last %},{% endif %}
        {%- endfor %}
    ) as drug_asp_quantity,
    COALESCE(
        {%- for method in contract_methodology %}
            dt{{ loop.index }}.parsed_unit_label
            {% if not loop.last %},{% endif %}
        {%- endfor %}
    ) as drug_parsed_unit_label,
    COALESCE(
        {%- for method in contract_methodology %}
            dt{{ loop.index }}.asp_unit_label
            {% if not loop.last %},{% endif %}
        {%- endfor %}
    ) as drug_asp_unit_label,

    -- Anesthesia Conversion
    {% for method in negotiated_types %}
        CASE
            WHEN 
                r.payer_id in ('47','391','643','567')
                AND an.base_units IS NOT NULL 
            THEN payer_{{method}}_rate 
            WHEN 
                r.payer_id = '76' 
                AND an.base_units IS NOT NULL 
                AND r.billing_code = '99100'  
            THEN payer_{{method}}_rate
            WHEN 
                r.payer_id = '44' 
                AND an.base_units IS NOT NULL 
            THEN payer_{{method}}_rate * 15
            WHEN 
                r.payer_id IN ('7','42','160','389','392','229','53','54','403','76','388','461','111','61','56','51','390') 
                AND an.base_units IS NOT NULL 
            THEN payer_{{method}}_rate/(an.base_units + 1)
            WHEN 
                r.payer_id IN ('299') 
                AND an.base_units IS NOT NULL 
            THEN payer_{{method}}_rate/(an.base_units + 15)
            WHEN 
                r.payer_id in('43','101','168','169','628','638','49')
                AND an.base_units IS NOT NULL 
            THEN payer_{{method}}_rate/an.base_units
            ELSE NULL
        END as payer_{{method}}_rate_anesthesia_cf,
    {% endfor %}
    {% for method in negotiated_types %}
        CASE
            WHEN 
                r.payer_id in('47','391','643','567')
                AND an.base_units IS NOT NULL 
            THEN '{rate}' 
            WHEN 
                r.payer_id = '76' 
                AND an.base_units IS NOT NULL 
                AND r.billing_code = '99100'  
            THEN '{rate}'
            WHEN 
                r.payer_id = '44' 
                AND an.base_units IS NOT NULL 
            THEN '{rate} * 15'
            WHEN 
                r.payer_id IN ('7','42','160','389','392','229','53','54','403','76','388','461','111','61','56','51','390') 
                AND an.base_units IS NOT NULL 
            THEN '{rate} / ({anesthesia_base_units} + 1)'
            WHEN 
                r.payer_id IN ('299') 
                AND an.base_units IS NOT NULL 
            THEN '{rate} / ({anesthesia_base_units} + 15)'
            WHEN 
                r.payer_id in('43','101','168','169','628','638','49')
                AND an.base_units IS NOT NULL 
            THEN '{rate} / {anesthesia_base_units}'
            ELSE NULL
        END as payer_{{method}}_anesthesia_conversion_method,
    {% endfor %}
    an.base_units AS anesthesia_base_units,

    r.payer_id

FROM {{ schema_name }}.tmp_int_combined_raw_{{ sub_version }} r
LEFT JOIN per_diem_transformation pdt
    ON r.billing_code = pdt.billing_code
    AND r.billing_code_type = pdt.billing_code_type

{%- for method in contract_methodology %}
LEFT JOIN drug_transformation_{{ 
        (method if method else 'null_methodology') | replace(' ', '_') 
    }} dt{{ loop.index }} 
    ON r.roid = dt{{ loop.index }}.roid
{%- endfor %}

LEFT JOIN (
    SELECT billing_code, ANY_VALUE(base_units) as base_units
    FROM {{ cld_params.Tables.ANESTHESIA_BASE_UNITS.value }}
    WHERE base_units > 0
    GROUP BY 1
) an
    ON r.billing_code = an.billing_code