clear_rates

clear_rates

-- 🏥🏥🏥
-- Consumer Pricing Service 
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ ps_schema }}.clear_rates_enhanced_{{ ps_month }} 
-- Subversion: {{ ps_month }}

--------------------------------
-- Purpose:
-- This table pulls all CLD data and filters it to only include lines relevant to the SSPs used in the pricing service.
-- It also enhances the CLD data with SSP information such as average units and fee type.
-- It also creates validation flags that are used in Consumer App.
--------------------------------

{% set cld_cols = [
    'roid',
    'payer_id',
    'payer_name',
    'network_id',
    'network_name',
    'provider_id',
    'provider_type',
    'npi',
    'state',
    'cbsa',
    'cbsa_name',
    'facility',
    'bill_type',
    'billing_code',
    'billing_code_type',
    'service_description',
    'discounted_cash_rate',
    'canonical_rate_source',
    'canonical_contract_methodology',
    'canonical_gross_charge',
    'canonical_rate_score',
    'state_avg_medicare_rate',
    'lower_bound',
    'upper_bound'
] %}

{% set cld_outlier_filter = '''
(
    canonical_rate_score > 1
    AND
    (canonical_rate BETWEEN COALESCE(lower_bound, state_avg_medicare_rate * 0.9) AND COALESCE(upper_bound, state_avg_medicare_rate * 10))
)
''' %}

CREATE TABLE {{ ps_schema }}.clear_rates_enhanced_{{ ps_month }} AS
WITH

--------------------------------
-- SSP: get relevant SSP lines
--------------------------------
ssp_contents AS (
    SELECT 
        l.ssp_id, 
        l.ssp_name,
        l.sub_package_id,
        l.line_code,
        l.line_code_type,
        l.average_units,
        l.fee_type,
        CASE
            WHEN c.line_code = c.base_code THEN 1
            ELSE 0
        END as is_package_base_code
    FROM {{ ps_schema }}.pricing_service_inputs_ssp_lines_{{ ps_month }} l
    LEFT JOIN {{ tables.SUB_PACKAGE_CONTENTS.value }} c
        ON l.sub_package_id = c.sub_package_id
        AND l.line_code = c.line_code
        AND l.fee_type = CASE
            WHEN c.fee_type = 'Professional Fee' THEN 'professional'
            ELSE 'facility'
        END
),

--------------------------------
-- CLD: get all CLD rows
-- we pull in PG professional fees here too, since we'll use them to map professional fees to facilities
--------------------------------
cld AS (
    -- NYC-DOH CUSTOM
    SELECT 
        {% for col in cld_cols -%}
            {{ col }}, 
        {%- endfor %}
        CASE
            WHEN {{ cld_outlier_filter }} THEN canonical_rate
            ELSE NULL
        END AS canonical_rate,
        CASE
            WHEN bill_type IN ('Inpatient', 'Outpatient') THEN 'facility'
            WHEN bill_type = 'Professional' THEN 'professional'
        END AS fee_type
    FROM {{ tables.CLD_CUSTOM_NYCDOH.value }}
    UNION ALL

    -- CLD
    SELECT 
        {% for col in cld_cols -%}
            {% if col in ('lower_bound', 'upper_bound') -%}
                {{ col }},
            {%- else -%}
                {{ col }}, 
            {%- endif -%}
        {%- endfor %}
        CASE
            WHEN {{ cld_outlier_filter }} THEN canonical_rate
            ELSE NULL
        END AS canonical_rate,
        CASE
            WHEN bill_type IN ('Inpatient', 'Outpatient') THEN 'facility'
            WHEN bill_type = 'Professional' THEN 'professional'
        END AS fee_type
    FROM {{ cld_schema }}.prod_combined_all

),
cld_enhanced AS (
    SELECT 
        from_big_endian_64(xxhash64(CAST(
            CAST(c.roid AS VARCHAR) || 
            provider_id ||
            ssp.ssp_id ||
            ssp.sub_package_id ||
            ssp.fee_type
            AS varbinary
        ))) AS id,
        c.roid,
        ssp.ssp_id,
        ssp.ssp_name,
        ssp.sub_package_id,
        c.payer_id,
        c.payer_name,
        c.network_id,
        c.network_name,
        c.provider_id,
        c.provider_type,
        c.npi,
        c.state,
        c.cbsa,
        c.cbsa_name,
        c.fee_type,
        ssp.is_package_base_code,
        c.bill_type,
        c.facility,
        c.billing_code,
        c.billing_code_type,
        ssp.average_units,
        CASE 
            WHEN c.billing_code IN (
                SELECT distinct billing_code
                FROM {{ tables.REF_ANESTHESIA_BASE_UNITS.value }}
            )
            THEN 0.067
            ELSE 1
        END as anesthesia_unit_conversion,
        c.service_description as billing_code_description,
        CASE
            WHEN canonical_rate_score = 5 THEN True
            ELSE False
        END AS validated_rate,
        True AS validated_prof_fee_rate,
        True AS claims_validated_rate,
        True as commercial_validated_reimbursement_rate,
        True as commercial_validated_cash_rate,
        state_avg_medicare_rate,
        lower_bound,
        upper_bound,
        canonical_rate AS negotiated_rate,
        discounted_cash_rate,
        canonical_contract_methodology as contract_methodology,
        canonical_gross_charge as gross_charge,
        canonical_rate_source AS source
    FROM cld c
    JOIN ssp_contents ssp
        ON c.fee_type = ssp.fee_type
        AND c.billing_code = ssp.line_code
        AND c.billing_code_type = ssp.line_code_type
    WHERE
        (
            provider_type != 'Physician Group'
            OR
            -- place of setting filter for physician groups
            (provider_type = 'Physician Group' AND facility = True)
        )
)
SELECT *
FROM cld_enhanced