Combine Raw Data

Schema

---

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


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

-- Declare Variables
{%- set payer_types = ['percentage', 'negotiated', 'fee schedule', 'per diem', 'derived'] %}
{%- set hospital_dollar_methods = ['case rate', 'percent of total billed charges', 'fee schedule', 'other', 'per diem', None] %}
{%- set hospital_percentage_methods = ['case rate', 'percent of total billed charges', 'fee schedule', 'other', 'per diem', None] %}
{%- set hospital_allowed_amount_methods = ['case rate', 'percent of total billed charges', 'fee schedule', 'other', 'per diem', None] %}

-- CHARGES + ALLOWED AMOUNTS
{% set gross_charges = cld_params.GROSS_CHARGE_COLS + cld_params.GROSS_CHARGE_STATS %}
{% set cash_charges = cld_params.CASH_COLS %}


-- Create CTEs for payer negotiated types
{%- for payer_type in payer_types %}
payer_{{ payer_type | replace(' ', '_') }} AS (
    SELECT 
        roid, 
        payer_id, 
        core_rates_id,
        original_billing_codes, 
        original_billing_code_type, 
        rate
    FROM {{ schema_name }}.tmp_raw_mrf_payer_rates_{{ sub_version }}
    WHERE negotiated_type = '{{ payer_type }}'
),
{%- endfor %}

-- Create CTEs for hospital contract methodologies (dollar)
{%- for method in hospital_dollar_methods %}
hospital_{{ (method if method else 'null_methodology') | replace(' ', '_') }}_dollar AS (
    SELECT 
        roid, 
        payer_id, 
        hospital_rates_id,
        original_billing_codes, 
        original_billing_code_type, 
        negotiated_dollar
    FROM {{ schema_name }}.tmp_raw_mrf_hospital_rates_{{ sub_version }}
    WHERE contract_methodology {{ 'IS NULL' if method is none else "= '" + method + "'" }}
    AND negotiated_dollar > 0
),
{%- endfor %}

-- Create CTEs for hospital contract methodologies (percentage)
{%- for method in hospital_percentage_methods %}
hospital_{{ (method if method else 'null_methodology') | replace(' ', '_') }}_percentage AS (
    SELECT 
        roid, 
        payer_id, 
        hospital_rates_id,
        original_billing_codes, 
        original_billing_code_type, 
        negotiated_percentage
    FROM {{ schema_name }}.tmp_raw_mrf_hospital_rates_{{ sub_version }}
    WHERE contract_methodology {{ 'IS NULL' if method is none else "= '" + method + "'" }}
    AND negotiated_percentage > 0
),
{%- endfor %}

-- Create CTE for hospital estimated allowed amount
{%- for method in hospital_allowed_amount_methods %}
hospital_{{ (method if method else 'null_methodology') | replace(' ', '_') }}_allowed_amount AS (
    SELECT 
        roid, 
        payer_id, 
        hospital_rates_id,
        original_billing_codes, 
        original_billing_code_type, 
        estimated_allowed_amount
    FROM {{ schema_name }}.tmp_raw_mrf_hospital_rates_{{ sub_version }}
    WHERE contract_methodology {{ 'IS NULL' if method is none else "= '" + method + "'" }}
    AND estimated_allowed_amount > 0
),
{%- endfor %}

-- Create CTEs for hospital gross charge and Komodo average allowed amount
hospital_discounted_cash_rate AS (
    SELECT 
        hr.provider_id, 
        hr.billing_code_type, 
        hr.billing_code, 
        approx_percentile(hr.discounted_cash_rate,0.5) as discounted_cash_rate
    FROM {{ hospital_schema }}.hospital_rates hr
    WHERE hr.discounted_cash_rate >= 0.1
    AND hr.discounted_cash_rate <= 50000000
    GROUP BY 1, 2, 3
)

-- Combine all raw data into a single table
SELECT 
    r.roid,
    r.network_id,
    r.provider_id,
    r.bill_type,
    r.billing_code,
    r.billing_code_type,
    r.facility,
    r.provider_type,
    r.is_plausible,
    cs.is_surg_code,
    cs.is_drug_code,
    
    {% for col in cash_charges %}
        CASE
            WHEN hdcr.{{ col }} BETWEEN co.lower_bound AND co.upper_bound
            THEN hdcr.{{ col }}
            ELSE NULL
        END AS {{ col }},
    {% endfor %}
    
    {% for col in gross_charges %}
        gc.{{ col }},
    {% endfor %}
    
    {%- for payer_type in payer_types %}
        p{{ loop.index }}.rate AS payer_{{ payer_type | replace(' ', '_') }}_rate,
        p{{ loop.index }}.core_rates_id AS payer_{{ payer_type | replace(' ', '_') }}_rate_id,
        p{{ loop.index }}.original_billing_codes AS payer_{{ payer_type | replace(' ', '_') }}_rate_original_billing_codes,
        p{{ loop.index }}.original_billing_code_type AS payer_{{ payer_type | replace(' ', '_') }}_rate_original_billing_code_type,
        CASE 
            WHEN p{{ loop.index }}.original_billing_codes IS NULL 
            THEN p{{ loop.index }}.rate 
            ELSE NULL
        END AS payer_{{ payer_type | replace(' ', '_') }}_untransformed_rate,
        CASE 
            WHEN p{{ loop.index }}.original_billing_codes IS NULL 
            THEN p{{ loop.index }}.core_rates_id 
            ELSE NULL
        END AS payer_{{ payer_type | replace(' ', '_') }}_untransformed_rate_id,
        CAST(NULL AS array(varchar)) AS payer_{{ payer_type | replace(' ', '_') }}_untransformed_rate_original_billing_codes,
        CAST(NULL AS varchar) AS payer_{{ payer_type | replace(' ', '_') }}_untransformed_rate_original_billing_code_type,
    {%- endfor %}
    
    {%- for method in hospital_dollar_methods %}
        h{{ loop.index }}.negotiated_dollar AS hospital_{{ (method if method else 'null_methodology') | replace(' ', '_') }}_dollar,
        h{{ loop.index }}.hospital_rates_id AS hospital_{{ (method if method else 'null_methodology') | replace(' ', '_') }}_dollar_id,
        h{{ loop.index }}.original_billing_codes AS hospital_{{ (method if method else 'null_methodology') | replace(' ', '_') }}_dollar_original_billing_codes,
        h{{ loop.index }}.original_billing_code_type AS hospital_{{ (method if method else 'null_methodology') | replace(' ', '_') }}_dollar_original_billing_code_type,
        CASE 
            WHEN h{{ loop.index }}.original_billing_codes IS NULL 
            THEN h{{ loop.index }}.negotiated_dollar 
            ELSE NULL
        END AS hospital_{{ (method if method else 'null_methodology') | replace(' ', '_') }}_untransformed_dollar,
        CASE 
            WHEN h{{ loop.index }}.original_billing_codes IS NULL 
            THEN h{{ loop.index }}.hospital_rates_id 
            ELSE NULL
        END AS hospital_{{ (method if method else 'null_methodology') | replace(' ', '_') }}_untransformed_dollar_id,
        CAST(NULL AS array(varchar)) AS hospital_{{ (method if method else 'null_methodology') | replace(' ', '_') }}_untransformed_dollar_original_billing_codes,
        CAST(NULL AS varchar) AS hospital_{{ (method if method else 'null_methodology') | replace(' ', '_') }}_untransformed_dollar_original_billing_code_type,
    {%- endfor %}
    
    {%- for method in hospital_percentage_methods %}
        h{{ loop.index + hospital_dollar_methods | length }}.negotiated_percentage AS hospital_{{ (method if method else 'null_methodology') | replace(' ', '_') }}_percentage,
        h{{ loop.index + hospital_dollar_methods | length }}.hospital_rates_id AS hospital_{{ (method if method else 'null_methodology') | replace(' ', '_') }}_percentage_id,
        h{{ loop.index + hospital_dollar_methods | length }}.original_billing_codes AS hospital_{{ (method if method else 'null_methodology') | replace(' ', '_') }}_percentage_original_billing_codes,
        h{{ loop.index + hospital_dollar_methods | length }}.original_billing_code_type AS hospital_{{ (method if method else 'null_methodology') | replace(' ', '_') }}_percentage_original_billing_code_type,
    {%- endfor %}

    {%- for method in hospital_allowed_amount_methods %}
        haa{{ loop.index }}.estimated_allowed_amount AS hospital_{{ (method if method else 'null_methodology') | replace(' ', '_') }}_allowed_amount,
        haa{{ loop.index }}.hospital_rates_id AS hospital_{{ (method if method else 'null_methodology') | replace(' ', '_') }}_allowed_amount_id,
        haa{{ loop.index }}.original_billing_codes AS hospital_{{ (method if method else 'null_methodology') | replace(' ', '_') }}_allowed_amount_original_billing_codes,
        haa{{ loop.index }}.original_billing_code_type AS hospital_{{ (method if method else 'null_methodology') | replace(' ', '_') }}_allowed_amount_original_billing_code_type,
        CASE 
            WHEN haa{{ loop.index }}.original_billing_codes IS NULL 
            THEN haa{{ loop.index }}.estimated_allowed_amount 
            ELSE NULL
        END AS hospital_{{ (method if method else 'null_methodology') | replace(' ', '_') }}_untransformed_allowed_amount,
        CASE 
            WHEN haa{{ loop.index }}.original_billing_codes IS NULL 
            THEN haa{{ loop.index }}.hospital_rates_id 
            ELSE NULL
        END AS hospital_{{ (method if method else 'null_methodology') | replace(' ', '_') }}_untransformed_allowed_amount_id,
        CAST(NULL AS array(varchar)) AS hospital_{{ (method if method else 'null_methodology') | replace(' ', '_') }}_untransformed_allowed_amount_original_billing_codes,
        CAST(NULL AS varchar) AS hospital_{{ (method if method else 'null_methodology') | replace(' ', '_') }}_untransformed_allowed_amount_original_billing_code_type,
    {%- endfor %}

    r.payer_id

FROM 
    {{ schema_name }}.tmp_rate_object_space_{{ sub_version }} r

{%- for payer_type in payer_types %}
LEFT JOIN payer_{{ payer_type | replace(' ', '_') }} p{{ loop.index }} 
    ON r.roid = p{{ loop.index }}.roid
    AND r.payer_id = p{{ loop.index }}.payer_id
{%- endfor %}

{%- for method in hospital_dollar_methods %}
LEFT JOIN hospital_{{ (method if method else 'null_methodology') | replace(' ', '_') }}_dollar h{{ loop.index }} 
    ON r.roid = h{{ loop.index }}.roid
    AND r.payer_id = h{{ loop.index }}.payer_id
{%- endfor %}

{%- for method in hospital_percentage_methods %}
LEFT JOIN hospital_{{ (method if method else 'null_methodology') | replace(' ', '_') }}_percentage h{{ loop.index + hospital_dollar_methods | length }} 
    ON r.roid = h{{ loop.index + hospital_dollar_methods | length }}.roid
    AND r.payer_id = h{{ loop.index + hospital_dollar_methods | length }}.payer_id
{%- endfor %}

{%- for method in hospital_allowed_amount_methods %}
LEFT JOIN hospital_{{ (method if method else 'null_methodology') | replace(' ', '_') }}_allowed_amount haa{{ loop.index }}
    ON r.roid = haa{{ loop.index }}.roid
    AND r.payer_id = haa{{ loop.index }}.payer_id
{%- endfor %}

LEFT JOIN {{ schema_name }}.tmp_raw_gross_charges_{{ sub_version }} gc
    ON r.roid = gc.roid
    AND r.payer_id = gc.payer_id
LEFT JOIN {{ schema_name }}.tmp_ref_code_spine_{{ sub_version }} cs
    ON r.bill_type = cs.bill_type
    AND r.billing_code_type = cs.billing_code_type
    AND r.billing_code = cs.billing_code
    AND (
        r.facility = cs.facility
        OR (r.facility IS NULL AND cs.facility IS NULL)
    )
LEFT JOIN hospital_discounted_cash_rate hdcr
    ON r.provider_id = hdcr.provider_id
    AND r.billing_code_type = hdcr.billing_code_type
    AND r.billing_code = hdcr.billing_code
    AND COALESCE(r.facility, true) = true
LEFT JOIN (
    SELECT 
        bill_type, 
        billing_code_type, 
        billing_code, 
        avg(lower_bound) as lower_bound, 
        avg(upper_bound) as upper_bound
    FROM {{ cld_params.Tables.CASH_OUTLIER_TABLE.value }}
    GROUP BY 1, 2, 3
) co
    ON r.billing_code_type = co.billing_code_type
    AND r.billing_code = co.billing_code
    AND r.bill_type = co.bill_type