Clear Rates

Infusion Centers in Clear Rates

As of v2.3.0 we added 7,067 Infusion Center provider IDs to the rate object space. These are classified under the Infusion Center provider_subtype, with Physician Group or ASC maintained as the umbrella provider_type.

Steps in provider.sql:

• Array associated NPIs from the two spines tables to the provider_id level.

infusion_centers_array_npi as (
    SELECT
        provider_id,
        ARRAY_SORT(ARRAY_AGG(DISTINCT cast(npi as varchar))) as npi
    FROM (
        SELECT
            provider_id,
            npi
        FROM {{ cld_params.Tables.SPINES_PROVIDER_INFUSION_CENTERS.value }}
        UNION
        SELECT
            provider_id,
            npi
        FROM {{ cld_params.Tables.SPINES_PROVIDER_INFUSION_CENTERS_ADDITIONAL_NPIS.value }}
    )
    WHERE npi IS NOT NULL
    GROUP BY provider_id
),

• Array associated EINs from the two spines tables at the provider_id level. This allows looking up rates reported at the EIN level in core_rates_sense_physician_groups.

infusion_centers_array_ein as (
    SELECT
        provider_id,
        ARRAY_SORT(ARRAY_AGG(DISTINCT cast(ein as varchar))) as ein
    FROM (
        SELECT
            provider_id,
            ein
        FROM {{ cld_params.Tables.SPINES_PROVIDER_INFUSION_CENTERS.value }}
        UNION
        SELECT
            provider_id,
            ein
        FROM {{ cld_params.Tables.SPINES_PROVIDER_INFUSION_CENTERS_ADDITIONAL_NPIS.value }}
    )
    WHERE ein IS NOT NULL
    GROUP BY provider_id
),

• Exclude these provider_ids from the asc and physician_groups CTEs.

-- not an infusion center
    AND pg.provider_id NOT IN (
        SELECT DISTINCT provider_id
        FROM {{ cld_params.Tables.SPINES_PROVIDER_INFUSION_CENTERS.value }}

• Create final infusion_centers CTE.

infusion_centers AS (
    SELECT
        p.provider_id,
        CASE
            WHEN npis.npi[1] IS NULL THEN NULL
            ELSE npis.npi
        END as npi,
        NULL as definitive_id,
        p.provider_name as provider_name,
        NULL as medicare_provider_id,
        CASE
            WHEN eins.ein[1] IS NULL THEN NULL
            ELSE eins.ein
        END as ein,
        p.provider_city AS city,
        p.provider_state AS state,
        geo_state.state_name || ' (' || p.provider_state || ')' as state_name_full,
        NULL AS cbsa,
        NULL AS cbsa_name,
        NULL AS county,
        p.provider_zip5 as zip_code,
        p.provider_zip3 as zip3,
        NULL as carrier_locality,
        NULL as street_address,
        NULL as hq_latitude,
        NULL as hq_longitude,
        NULL as website_url,
        NULL as health_system_id,
        NULL as health_system_name,
        NULL as health_system_type,
        NULL as health_system_city,
        NULL as health_system_state,
        p.provider_type as provider_type,
        p.provider_type AS provider_type_new,
        p.provider_subtype,
        p.taxonomy_code,
        NULL AS taxonomy_grouping,
        NULL as total_beds,
        NULL as operating_room_count,
        NULL as services_lab,
        NULL as services_radiology,
        NULL as services_cardiology,
        NULL as services_dental,
        NULL as services_dermatology,
        NULL as services_ent,
        NULL as services_general_other,
        NULL as services_endoscopy,
        NULL as services_nephrology,
        NULL as services_neurology,
        NULL as services_obgyn,
        NULL as services_opthalmology,
        NULL as services_orthopedic,
        NULL as services_pain,
        NULL as services_plastic_surgery,
        NULL as services_podiatry,
        NULL as services_pulmonary,
        NULL as services_urology,
        NULL as services_mra,
        NULL as services_ct,
        NULL as services_mri,
        NULL as services_mammography,
        NULL as services_cta,
        NULL as services_xray,
        NULL as services_ultrasound,
        NULL as services_spect,
        NULL as services_pet,
        NULL as services_imrt,
        FALSE as national_payer_coverage
    FROM {{ cld_params.Tables.SPINES_PROVIDER_INFUSION_CENTERS.value }} p
    LEFT JOIN geo_state
        ON p.provider_state = geo_state.state_abbreviation
    LEFT JOIN infusion_centers_array_npi as npis
        ON p.provider_id = npis.provider_id
    LEFT JOIN infusion_centers_array_ein as eins
        ON p.provider_id = eins.provider_id
),