Hospital MRF Data

ComponentSchema

---

SQL

-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_raw_mrf_hospital_rates_{{n_chunk}}_{{ sub_version }}
-- Subversion: {{ sub_version }}
-- n_chunk: {{ n_chunk }}

{% set common_columns = [
    'payer_id',
    'network_id',
    'provider_id',
    'hospital_rates_id',
    'bill_type',
    'billing_code',
    'apr_drg_formatted_billing_code',
    'apc_formatted_billing_code',
    'billing_code_type',
    'description',
    'revenue_code',
    'billing_code_modifiers',
    'billing_class',
    'drug_billing_class',
    'setting',
    'negotiated_dollar',
    'negotiated_percentage',
    'estimated_allowed_amount',
    'negotiated_rate',
    'min_standard_charge',
    'max_standard_charge',
    'gross_charge',
    'discounted_cash_rate',
    'negotiated_algorithm',
    'contract_methodology',
    'additional_generic_notes',
    'additional_payer_notes',
    'file_id',
    'ingested_on',
    'loaded_on',
    'plan_name',
    'raw_payer_name',
] %}

CREATE TABLE IF NOT EXISTS {{ schema_name }}.tmp_raw_mrf_hospital_rates_{{n_chunk}}_{{ sub_version }}
WITH (
    PARTITIONING = ARRAY['payer_id']
)
AS
WITH 

------------------------------------
-- Spines and Rate Object Space
------------------------------------
plan_bridge AS (
    SELECT 
        id,
        payer_id,
        provider_id as pb_provider_id,
        network_name
    FROM {{ schema_name }}.tmp_ref_plan_bridge_{{ sub_version }}
),
network_spine AS (
    SELECT 
        p.payer_id,
        n.network_id,
        n.network_name
    FROM {{ schema_name }}.tmp_ref_network_spine_{{ sub_version }} n
    LEFT JOIN {{ schema_name }}.tmp_ref_payer_spine_{{ sub_version }} p
        ON n.payer_id = p.payer_id
),

apc_xwalk AS (
    SELECT 
        DISTINCT
        apc,
        hcpcs
    FROM {{ cld_params.Tables.OPPS_ADDENDUM_B.value }} opps
    WHERE is_latest_start_effective_date = true
    AND apc != '0'
),

eapg_xwalk AS (
    SELECT 
        DISTINCT
        eapg,
        hcpcs
    FROM {{ cld_params.Tables.HCPCS_EAPG_CROSSWALK_TABLE.value }}
),

aprdrg_xwalk AS (
    SELECT *
    FROM {{ cld_params.Tables.APR_DRG_XWALK_TABLE.value }}
),

rate_object_space AS (
    SELECT 
        ros.roid,
        ros.payer_id,
        ros.network_id,
        ros.provider_id,
        ros.bill_type,
        ros.billing_code,
        ros.billing_code_type
    FROM {{ schema_name }}.tmp_rate_object_space_{{ sub_version }} ros
    WHERE provider_type LIKE '%Hospital%'
),

-- drug codes
drug_codes AS (
    SELECT billing_code
    FROM {{ schema_name }}.tmp_ref_code_spine_{{ sub_version }}
    WHERE is_drug_code = True
),

------------------------------------
-- Hospital Rates Preprocessing
------------------------------------

-- add new columns
hospital_rates_with_new_columns_added AS (
    SELECT 
        {% set excluded_columns = [
            'hospital_rates_id', 'payer_id', 'network_id', 'billing_code', 'billing_code_type',
            'apr_drg_formatted_billing_code', 'apc_formatted_billing_code',
            'negotiated_rate', 'bill_type', 'drug_billing_class',
            'ingested_on', 'loaded_on'
        ] %}

        {% for column in common_columns %}
            {% if column not in excluded_columns %}
                {{ column }},
            {% endif %}
        {% endfor %}

        rates.id as hospital_rates_id,
        pb.payer_id,
        ns.network_id,
        CASE
            WHEN billing_code_type = 'DRG' THEN 'MS-DRG'
            ELSE billing_code_type
        END as billing_code_type,
        CASE    
            WHEN billing_code_type = 'MS-DRG' THEN lpad(billing_code,3,'0') 
            ELSE billing_code
        END as billing_code,
        
        LPAD(SPLIT_PART(billing_code, '-', 1), 4, '0') ||
            CASE 
                WHEN REGEXP_LIKE(SPLIT_PART(billing_code, '-', 2), '^[0-9]+$') 
                THEN '-' || SPLIT_PART(billing_code, '-', 2)
                ELSE ''
        END AS apr_drg_formatted_billing_code,

        CASE
            WHEN REGEXP_LIKE(replace(billing_code, 'APC-', ''), '^[0-9.]+$')
            THEN LPAD(CAST(CAST(replace(billing_code, 'APC-', '') AS DECIMAL(8,0)) AS VARCHAR), 4, '0')
            ELSE NULL
        END as apc_formatted_billing_code,
        
        CASE 
            WHEN billing_code_type IN ('MS-DRG','APR-DRG','DRG')
                THEN 'Inpatient'
            WHEN COALESCE(billing_class, '') != 'Professional' AND billing_code_type IN ('CPT', 'HCPCS', 'APC', 'EAPG')
                THEN 'Outpatient'
            WHEN billing_code IN (SELECT billing_code FROM drug_codes)
                THEN 'Outpatient'
            WHEN billing_class = 'Professional' AND billing_code_type IN ('CPT', 'HCPCS', 'APC', 'EAPG')
                THEN 'Professional'
        END as bill_type,
        CASE
            WHEN billing_code IN (SELECT billing_code FROM drug_codes) 
                THEN COALESCE(billing_class, 'Outpatient')
            ELSE NULL
        END AS drug_billing_class,
        CASE
            WHEN negotiated_dollar IS NOT NULL THEN negotiated_dollar
            WHEN negotiated_percentage IS NOT NULL THEN negotiated_percentage
            WHEN (estimated_allowed_amount > 0 AND estimated_allowed_amount < 10000000) IS NOT NULL THEN estimated_allowed_amount
            ELSE NULL
        END as negotiated_rate,

        CAST(ingested_on AS TIMESTAMP) AS ingested_on,
        CAST(loaded_on AS TIMESTAMP) AS loaded_on

    FROM {{ hospital_schema }}.hospital_rates rates
    JOIN plan_bridge pb
        ON rates.id = pb.id
        AND rates.provider_id = pb.pb_provider_id
        AND rates.payer_id = CAST(pb.payer_id AS INT)
    JOIN network_spine ns
        ON pb.payer_id = ns.payer_id
        AND pb.network_name = ns.network_name
    WHERE payer_class_name = 'Commercial'
    AND (
        negotiated_dollar > 0
        OR negotiated_percentage > 0
        OR (estimated_allowed_amount > 0 AND estimated_allowed_amount < 10000000)
    )
    -- setting
    AND (
        (
            billing_code_type IN ('HCPCS', 'APC', 'EAPG') AND 
            setting != 'Inpatient'
        )
        OR (
            billing_code_type IN ('MS-DRG', 'APR-DRG', 'DRG') 
            AND setting != 'Outpatient'
        )
    )
    AND rates.provider_id IN ({{ provider_ids }})
),


-- HCPCS + MS-DRG + APR-DRG: no action required
hcpcs_msdrg AS (
    SELECT 
        {% for column in common_columns %}
            {{ column }} {{ ', ' if not loop.last }}
        {% endfor %}
    FROM hospital_rates_with_new_columns_added
    WHERE billing_code_type IN ('MS-DRG', 'APR-DRG', 'HCPCS')
),

-- MS-DRG: use many-to-one mapping of MS-DRG to APR-DRG
-- compute the average negotiated dollar and percentage for each APR-DRG
ms_drg_to_apr_drg AS (
    SELECT 
        payer_id,
        provider_id,
        network_id,
        dc.apr_drg as billing_code,
        'APR-DRG' as billing_code_type,
        contract_methodology,
        avg(negotiated_dollar) as negotiated_dollar,
        avg(negotiated_percentage) as negotiated_percentage,
        avg(estimated_allowed_amount) as estimated_allowed_amount,
        array_join(array_agg(hospital_rates_id),',') as hospital_rates_id,
        array_distinct(array_agg(billing_code)) as original_billing_codes,
        'MS-DRG' as original_billing_code_type,

        {% set exclude_apr_drg_cte_cols = [
            'payer_id', 'provider_id', 'network_id', 'billing_code', 'billing_code_type',
            'contract_methodology', 'negotiated_dollar', 'negotiated_percentage',
            'estimated_allowed_amount', 'hospital_rates_id',
        ] %}

        {% for column in common_columns %}
            {% if column not in exclude_apr_drg_cte_cols %}
                ANY_VALUE({{ column }}) AS {{ column }} {{ ', ' if not loop.last }}
            {% endif %}
        {% endfor %}

    FROM hospital_rates_with_new_columns_added r
    JOIN aprdrg_xwalk dc
        ON r.billing_code = dc.ms_drg
    WHERE billing_code_type = 'MS-DRG'
    GROUP BY
        payer_id,
        provider_id,
        network_id,
        dc.apr_drg,
        contract_methodology
),

-- APR-DRG: use many-to-one mapping of APR-DRG to MS-DRG
-- compute the average negotiated dollar and percentage for each MS-DRG
apr_drg_to_ms_drg AS (
    SELECT 
        payer_id,
        provider_id,
        network_id,
        dc.ms_drg as billing_code,
        'MS-DRG' as billing_code_type,
        contract_methodology,
        avg(negotiated_dollar) as negotiated_dollar,
        avg(negotiated_percentage) as negotiated_percentage,
        avg(estimated_allowed_amount) as estimated_allowed_amount,
        array_join(array_agg(hospital_rates_id),',') as hospital_rates_id,
        array_distinct(array_agg(billing_code)) as original_billing_codes,
        'APR-DRG' as original_billing_code_type,

        {% set exclude_apr_drg_cte_cols = [
            'payer_id', 'provider_id', 'network_id', 'billing_code', 'billing_code_type',
            'contract_methodology', 'negotiated_dollar', 'negotiated_percentage',
            'estimated_allowed_amount', 'hospital_rates_id',
        ] %}

        {% for column in common_columns %}
            {% if column not in exclude_apr_drg_cte_cols %}
                ANY_VALUE({{ column }}) AS {{ column }} {{ ', ' if not loop.last }}
            {% endif %}
        {% endfor %}

    FROM hospital_rates_with_new_columns_added r
    JOIN aprdrg_xwalk dc
        ON r.apr_drg_formatted_billing_code = dc.apr_drg
    WHERE billing_code_type = 'APR-DRG'
    GROUP BY
        payer_id,
        provider_id,
        network_id,
        dc.ms_drg,
        contract_methodology
),


-- APC: use one-to-many mapping of APC to HCPCS
-- each HCPCS will be assigned the rate value of the APC
apc AS (
    SELECT 
        xwalk.hcpcs as billing_code,
        'HCPCS' as billing_code_type,
        ARRAY[billing_code] as original_billing_codes,
        'APC' as original_billing_code_type,

        {% set exclude_apc_cte_cols = [
            'billing_code', 'billing_code_type',
        ] %}
        {% for column in common_columns %}
            {% if column not in exclude_apc_cte_cols %}
                r.{{ column }} {{ ', ' if not loop.last }}
            {% endif %}
        {% endfor %}
    FROM hospital_rates_with_new_columns_added r
    JOIN apc_xwalk xwalk
        ON r.apc_formatted_billing_code = xwalk.apc
    WHERE billing_code_type = 'APC'
),

-- EAPG: use one-to-many mapping of EAPG to HCPCS
-- each HCPCS will be assigned the rate value of the EAPG
eapg AS (
    SELECT 
        xwalk.hcpcs as billing_code,
        'HCPCS' as billing_code_type,
        ARRAY[billing_code] as original_billing_codes,
        'EAPG' as original_billing_code_type,

        {% set exclude_eapg_cte_cols = [
            'billing_code', 'billing_code_type',
        ] %}
        {% for column in common_columns %}
            {% if column not in exclude_eapg_cte_cols %}
                r.{{ column }} {{ ', ' if not loop.last }}
            {% endif %}
        {% endfor %}
    FROM hospital_rates_with_new_columns_added r
    JOIN eapg_xwalk xwalk
        ON r.billing_code = xwalk.eapg
    WHERE billing_code_type = 'EAPG'
),

-- combine HCPCS + MS-DRG and APR-DRG and APC and EAPG
rates AS (
    SELECT 
        {% for column in common_columns %}
            {{ column }} {{ ', ' if not loop.last }}
        {% endfor %},
        NULL as original_billing_codes,
        NULL as original_billing_code_type
    FROM hcpcs_msdrg
    UNION ALL
    SELECT 
        {% for column in common_columns %}
            {{ column }} {{ ', ' if not loop.last }}
        {% endfor %},
        original_billing_codes,
        original_billing_code_type
    FROM ms_drg_to_apr_drg
    UNION ALL
    SELECT 
        {% for column in common_columns %}
            {{ column }} {{ ', ' if not loop.last }}
        {% endfor %},
        original_billing_codes,
        original_billing_code_type
    FROM apr_drg_to_ms_drg
    UNION ALL
    SELECT 
        {% for column in common_columns %}
            {{ column }} {{ ', ' if not loop.last }}
        {% endfor %},
        original_billing_codes,
        original_billing_code_type
    FROM apc
    UNION ALL
    SELECT 
        {% for column in common_columns %}
            {{ column }} {{ ', ' if not loop.last }}
        {% endfor %},
        original_billing_codes,
        original_billing_code_type
    FROM eapg
),

-- filter raw data to rate object space
rates_filtered AS (
    SELECT 
        ros.roid,
        {% for column in common_columns %}
            rates.{{ column }},
        {% endfor %}
        original_billing_codes,
        original_billing_code_type,
        mrf.filename,
        mrf.version
    FROM rates
    JOIN tq_production.hospital_data.hospital_provider prov
        ON rates.provider_id = prov.id
    JOIN rate_object_space ros
        ON rates.payer_id = ros.payer_id
        AND rates.network_id = ros.network_id
        AND rates.provider_id = ros.provider_id
        AND rates.bill_type = ros.bill_type
        AND rates.billing_code = ros.billing_code
        AND rates.billing_code_type = ros.billing_code_type
    -- for traceability columns
    LEFT JOIN tq_production.hospital_data.hospital_mrf_history mrf
        ON rates.file_id = mrf.id
),

------------------------------------
-- Deduplicate Rates
------------------------------------

-- prioritize rates with NULL billing_code_modifiers and revenue_code
rates_ranked AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY 
                payer_id,
                network_id,
                provider_id,
                bill_type,
                billing_code,
                billing_code_type,
                contract_methodology
            ORDER BY 
                drug_billing_class ASC,
                CASE 
                    WHEN (billing_code_modifiers IS NULL AND revenue_code IS NULL) THEN -1
                    WHEN billing_code_modifiers IS NULL THEN 0 
                    WHEN revenue_code IS NULL THEN 0
                    ELSE 1 
                END ASC,
                negotiated_rate DESC
        ) as rank
    FROM rates_filtered
)

------------------------------------
-- Output
------------------------------------
SELECT 
    rr.hospital_rates_id,
    rr.roid,
    rr.provider_id,
    rr.network_id,
    rr.bill_type,
    rr.billing_code,
    rr.billing_code_type,
    rr.original_billing_codes,
    rr.original_billing_code_type,
    rr.description,
    rr.revenue_code,
    rr.billing_code_modifiers,
    rr.billing_class,
    rr.setting,
    rr.negotiated_dollar,
    CASE
        WHEN rr.negotiated_percentage < 1 THEN rr.negotiated_percentage * 100
        WHEN rr.negotiated_percentage > 1000 THEN rr.negotiated_percentage / 100
        ELSE rr.negotiated_percentage
    END AS negotiated_percentage,
    CASE    
        WHEN rr.estimated_allowed_amount >= 10000000 THEN NULL
        ELSE rr.estimated_allowed_amount
    END as estimated_allowed_amount,
    rr.negotiated_rate,
    rr.min_standard_charge,
    rr.max_standard_charge,
    rr.gross_charge,
    rr.discounted_cash_rate,
    rr.negotiated_algorithm,
    rr.contract_methodology,
    rr.additional_generic_notes,
    rr.additional_payer_notes,
    rr.file_id,
    rr.ingested_on,
    rr.loaded_on,
    rr.filename,
    rr.version,
    rr.plan_name,
    rr.raw_payer_name,
    rr.payer_id
FROM rates_ranked rr
WHERE rank = 1