final_ssp

final_ssp

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


{%- macro validated_condition() -%}
    r.validated_rate = True OR 
    r.claims_validated_rate = True OR
    r.commercial_validated_reimbursement_rate = True
{%- endmacro -%}

{%- macro professional_validated_condition() -%}
    r.validated_prof_fee_rate = True OR 
    r.claims_validated_rate = True OR
    r.commercial_validated_reimbursement_rate = True
{%- endmacro -%}

{%- macro ssp_columns() -%}
    roid,
    source,
    network_provider_ssp_id,
    network_provider_sub_package_id,
    ssp_id,
    ssp_name,
    provider_id,
    provider_type,
    state,
    cbsa,
    cbsa_name
{%- endmacro -%}


--
CREATE TABLE {{ ps_schema }}.pricing_service_ssp_{{ ps_month }} 
WITH (
    PARTITIONING = ARRAY['payer_id']
)
AS

WITH

--------------------------------
-- Pull Rates
--------------------------------
rates AS (
    SELECT 
        r.*,
        CASE
            WHEN fee_type = 'facility' THEN negotiated_rate
            ELSE NULL
        END as facility_fee,
        CASE
            WHEN fee_type = 'facility' THEN discounted_cash_rate
            ELSE NULL
        END as facility_cash_fee,
        CASE 
            WHEN (
                {{ validated_condition() }}
            ) AND is_package_base_code = 1
            AND fee_type = 'facility'
            THEN True
            ELSE False
        END as validated_base_code
    FROM {{ ps_schema }}.pricing_service_rates_{{ ps_month }}  r
),

--------------------------------
-- Sum Professional Subpackage Prices
--------------------------------
professional_subpackage_prices AS (
    SELECT 
        network_provider_sub_package_id,
        sum(negotiated_rate * average_units * anesthesia_unit_conversion) as subpackage_price,
        sum(discounted_cash_rate * average_units * anesthesia_unit_conversion) as subpackage_discounted_cash_price,
        array_agg(id) as professional_rate_ids
    FROM rates r
    WHERE (
        {{ professional_validated_condition() }}
    )
    AND fee_type = 'professional'
    GROUP BY
        network_provider_sub_package_id
),

--------------------------------
-- Filter to Facility Rates Then Append Professional Rates
--------------------------------
all_rates AS (
    SELECT 
        r.*,
        p.professional_rate_ids,
        subpackage_price as professional_fee,
        subpackage_discounted_cash_price as professional_cash_fee,
        
        -- if Imaging Center, only show professional
        -- else add facility + professional
        CASE    
            WHEN provider_type = 'Imaging Center' 
                THEN COALESCE(subpackage_price, 0)
            ELSE COALESCE(facility_fee, 0) + COALESCE(subpackage_price, 0) 
        END as total_price,
        
        -- if Imaging Center, only show professional
        -- else add facility + professional
        CASE    
            WHEN provider_type = 'Imaging Center' 
                THEN COALESCE(subpackage_discounted_cash_price, 0)
            ELSE COALESCE(facility_cash_fee, 0) + COALESCE(subpackage_discounted_cash_price, 0) 
        END as total_cash_price
    FROM rates r
    LEFT JOIN professional_subpackage_prices p
        ON r.network_provider_sub_package_id = p.network_provider_sub_package_id
    WHERE 
    -- if not imaging center, then make sure facility fee exists
    (
        provider_type != 'Imaging Center'
        AND (
            facility_fee > 1
            OR facility_cash_fee > 1
        )
    )
    -- if imaging center, only professional fee is required
    OR (
        provider_type = 'Imaging Center'
        AND subpackage_price > 1
    )
),

-------------------------------------------
-- CASE RATES
-- use "rank" to pull highest validated rate per payer-network/ssp/provider
-------------------------------------------

case_rates AS (
    SELECT *
    FROM (
        SELECT
            row_number() over (
                partition by network_provider_ssp_id
                order by total_price desc
            ) as rank,
            {{ ssp_columns() }},
            id,
            sub_package_id,
            billing_code,
            billing_code_type,
            facility_fee,
            professional_fee,
            professional_rate_ids,
            total_price as max_price,
            CASE
                WHEN 
                    validated_rate = True
                THEN True
                ELSE False
            END as max_price_validated,
            claims_validated_rate as max_price_claims_validated,
            contract_methodology,
            network_id,
            network_name,
            plan_alias,
            hosp_payer_id,
            payer_id,
            payer_name
        FROM all_rates r
        WHERE (
            {{ validated_condition() }}
        )
        AND is_package_base_code = 1
        AND validated_subpackage = True
        AND (
            billing_code_type = 'MS-DRG' OR
            professional_fee IS NOT NULL
        )
    )
    WHERE rank = 1
),

-------------------------------------------
-- CASE RATES: Cash Price
-- use "rank" to pull highest validated cash price per ssp/provider
-------------------------------------------

case_rates_cash AS (
    SELECT *
    FROM (
        SELECT
            row_number() over (
                partition by ssp_id, provider_id
                order by total_cash_price desc
            ) as rank,
            {{ ssp_columns() }},
            id,
            sub_package_id,
            billing_code,
            billing_code_type,
            facility_cash_fee as facility_fee,
            professional_cash_fee as professional_fee,
            professional_rate_ids,
            total_cash_price as max_price,
            NULL as max_price_validated,
            NULL as max_price_claims_validated,
            contract_methodology,
            0 as network_id,
            'cash_price' as network_name,
            'cash_price' as plan_alias,
            0 as hosp_payer_id,
            '0' as payer_id,
            'cash_price' as payer_name
        FROM all_rates r
        WHERE r.commercial_validated_cash_rate IS NOT NULL
        AND discounted_cash_rate < r.gross_charge
        AND is_package_base_code = 1
    )
    WHERE rank = 1
)

-- ===========================================
-- COMBINED
-- ===========================================

SELECT 
    {{ ssp_columns() }},
    id as max_price_id,
    facility_fee,
    professional_fee,
    professional_rate_ids,
    max_price,
    max_price_validated,
    max_price_claims_validated,
    sub_package_id as max_price_sub_package_id,
    billing_code as max_price_billing_code,
    billing_code_type as max_price_billing_code_type,
    contract_methodology,
    network_id,
    network_name,
    payer_id,
    payer_name,
    plan_alias,
    hosp_payer_id
FROM case_rates
UNION ALL
SELECT 
    {{ ssp_columns() }},
    id as max_price_id,
    facility_fee,
    professional_fee,
    professional_rate_ids,
    max_price,
    max_price_validated,
    max_price_claims_validated,
    sub_package_id as max_price_sub_package_id,
    billing_code as max_price_billing_code,
    billing_code_type as max_price_billing_code_type,
    contract_methodology,
    network_id,
    network_name,
    payer_id,
    payer_name,
    plan_alias,
    hosp_payer_id
FROM case_rates_cash