Version: 2.1
Komodo Claims Data

SQL

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


CREATE TABLE {{ schema_name }}.tmp_raw_komodo_allowables_{{ sub_version }} 
WITH (
    PARTITIONING = ARRAY['payer_id']
)
AS
WITH
komodo_allowed_amounts AS (
    SELECT
        tq_payer_id as payer_id,
        provider_id,
        billing_code,
        billing_code_type,
        case
            when claim_type_code = 'institutional'
                and setting = 'Inpatient'
                then 'Inpatient'
            when claim_type_code = 'institutional'
                and setting = 'Outpatient'
                then 'Outpatient'
            end as bill_type,
        avg(median_allowed_amount) filter (
            where billing_code_ranking = 'primary'
                and allowed_amount_type = 'claim'
            ) as komodo_allowed_amount_claim,
        avg(median_allowed_amount) filter (
            where billing_code_ranking = 'primary'
                and allowed_amount_type = 'line_item'
            ) as komodo_allowed_amount_line_item,
        sum(count_encounters) filter (
            where billing_code_ranking = 'primary'
                and allowed_amount_type = 'claim'
            ) as count_encounters_primary_claim,
        sum(count_encounters) filter (
            where billing_code_ranking = 'primary'
                and allowed_amount_type = 'line_item'
            ) as count_encounters_primary_line_item
    FROM {{ cld_params.Tables.ALLOWABLES_NPI_PAYER.value }}
    WHERE payer_channel = 'Commercial'
        AND claim_type_code IN ('institutional')
        AND median_allowed_amount IS NOT NULL
        AND npi_source = 'hco'
    GROUP BY 1, 2, 3, 4, 5
)
SELECT 
    ros.roid,
    ros.network_id,
    ros.provider_id,
    ros.bill_type,
    ros.billing_code,
    ros.billing_code_type,
    km.komodo_allowed_amount_claim,
    km.komodo_allowed_amount_line_item,
    km.count_encounters_primary_line_item,
    km.count_encounters_primary_claim,
    ros.payer_id
FROM {{ schema_name }}.tmp_rate_object_space_{{ sub_version }} ros
LEFT JOIN komodo_allowed_amounts km
    ON cast(km.payer_id as varchar) = ros.payer_id
    AND cast(km.provider_id as varchar) = ros.provider_id
    AND km.bill_type = ros.bill_type
    AND km.billing_code = ros.billing_code
    AND km.billing_code_type = ros.billing_code_type
WHERE (
    km.komodo_allowed_amount_line_item IS NOT NULL
    AND
    km.count_encounters_primary_line_item >= 11
) OR (
    km.komodo_allowed_amount_claim IS NOT NULL
    AND
    km.count_encounters_primary_claim >= 11
)
SQL​

On this page:

SQL
