Payer MRF Data
SQL​
Core Rates​
for each payer​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_raw_mrf_payer_rates_{{ n_chunk }}_{{ sub_version }}
-- Subversion: {{ sub_version }}
-- n_chunk: {{ n_chunk }}
{% set common_columns = [
'payer_id',
'provider_id',
'core_rates_id',
'file_hash',
'product_network_label',
'billing_code',
'billing_code_type',
'bill_type',
'billing_class',
'service_code',
'billing_code_modifier',
'modifier_count',
'drug_billing_class',
'payer_name',
'negotiation_arrangement',
'negotiated_type',
'negotiated_rate',
'additional_information',
'provider_group_id',
'provider_group_id_type',
'is_plausible',
'cbsa',
'state',
] %}
CREATE TABLE {{ schema_name }}.tmp_raw_mrf_payer_rates_{{ n_chunk }}_{{ sub_version }}
WITH (
PARTITIONING = ARRAY['payer_id']
)
AS
WITH
-- spines + rate object space
network_spine AS (
SELECT
network_id,
t.payer_data_network
FROM
{{ schema_name }}.tmp_ref_network_spine_{{ sub_version }},
UNNEST(payer_data_network) AS t(payer_data_network)
),
provider_spine AS (
SELECT
npi_value AS npi,
provider_id,
provider_type
FROM
{{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }},
UNNEST(npi) AS t(npi_value)
WHERE
provider_id IS NOT NULL
),
apc_xwalk AS (
SELECT
DISTINCT
apc,
hcpcs
FROM {{ cld_params.Tables.OPPS_ADDENDUM_B.value }} opps
WHERE is_latest_start_effective_date = true
AND apc != '0'
),
eapg_xwalk AS (
SELECT
DISTINCT
eapg,
hcpcs
FROM {{ cld_params.Tables.HCPCS_EAPG_CROSSWALK_TABLE.value }}
),
aprdrg_xwalk AS (
SELECT *
FROM {{ cld_params.Tables.APR_DRG_XWALK_TABLE.value }}
),
rate_object_space AS (
SELECT
ros.roid,
ros.payer_id,
ros.network_id,
ros.provider_id,
ros.billing_code,
ros.billing_code_type,
ros.bill_type,
rns.payer_data_network
FROM {{ schema_name }}.tmp_rate_object_space_{{ sub_version }} ros
LEFT JOIN network_spine rns
ON ros.network_id = rns.network_id
WHERE payer_id IN ({{ payer_ids }})
AND provider_type NOT IN ('Physician Group')
),
-- traceability columns
files AS (
SELECT
file_hash,
ARRAY_AGG(DISTINCT location_data_source_name) as location_data_source_name,
max(last_updated_on) as last_updated_on
FROM {{ payer_schema }}.compressed_idx_file_label
GROUP BY file_hash
),
-- drug codes
drug_codes AS (
SELECT billing_code
FROM {{ schema_name }}.tmp_ref_code_spine_{{ sub_version }}
WHERE is_drug_code = True
),
-- get raw data
core_rates AS (
SELECT
{% set excluded_columns = [
'provider_id', 'core_rates_id', 'bill_type', 'modifier_count',
'drug_billing_class', 'negotiated_rate'
] %}
{% for column in common_columns %}
{% if column not in excluded_columns %}
{{ column }},
{% endif %}
{% endfor %}
CASE
WHEN negotiated_type = 'percentage' AND negotiated_rate < 1 THEN negotiated_rate * 100
ELSE negotiated_rate
END as negotiated_rate,
ps.provider_id,
{% if payer_schema[-7:] >= '2024_12' %}
id as core_rates_id,
{% else %}
CAST(uuid() AS VARCHAR) as core_rates_id,
{% endif %}
CASE
WHEN billing_class = 'institutional' AND billing_code_type IN ('MS-DRG','APR-DRG')
THEN 'Inpatient'
WHEN billing_class = 'institutional' AND billing_code_type IN ('CPT', 'HCPCS', 'APC')
THEN 'Outpatient'
WHEN billing_code IN (SELECT billing_code FROM drug_codes)
THEN 'Outpatient'
-- Payers that post ASC rates as 'professional'
WHEN
ps.provider_type = 'ASC'
AND billing_class = 'professional'
AND payer_id IN ('7', '61', '44', '50', '456', '464', '383', '455')
AND billing_code_type IN ('CPT', 'HCPCS', 'APC')
THEN 'Outpatient'
WHEN billing_class = 'professional' AND billing_code_type IN ('CPT', 'HCPCS', 'APC')
THEN 'Professional'
ELSE NULL
END as bill_type,
CASE
WHEN CARDINALITY(SPLIT(billing_code_modifier,',')) > 1 AND CONTAINS(SPLIT(billing_code_modifier,','),'TC') THEN infinity()
WHEN CARDINALITY(SPLIT(billing_code_modifier,',')) = 1 AND SPLIT(billing_code_modifier,',')[1] = '26' THEN -1
WHEN CARDINALITY(SPLIT(billing_code_modifier,',')) = 0 THEN infinity()
WHEN CARDINALITY(SPLIT(billing_code_modifier,',')) = 1 AND SPLIT(billing_code_modifier,',')[1] = '' THEN infinity()
ELSE CARDINALITY(SPLIT(billing_code_modifier,','))
END AS modifier_count,
CASE
WHEN billing_code IN (SELECT billing_code FROM drug_codes)
THEN billing_class
ELSE NULL
END AS drug_billing_class
FROM {{ payer_schema }}.core_rates rates
JOIN provider_spine ps
ON ps.npi = rates.provider_npi
WHERE payer_id IN ({{ payer_ids }})
),
-- HCPCS + MS-DRG + APR-DRG: no action required
hcpcs_msdrg AS (
SELECT
{% for column in common_columns %}
{{ column }} {{ ', ' if not loop.last }}
{% endfor %}
FROM core_rates
WHERE billing_code_type IN ('MS-DRG', 'APR-DRG', 'HCPCS')
),
-- MS-DRG (from APR-DRG): use many-to-one mapping of MS-DRG to APR-DRG
-- compute the average negotiated dollar and percentage for each APR-DRG
ms_drg_to_apr_drg AS (
SELECT
payer_id,
provider_id,
product_network_label,
dc.apr_drg as billing_code,
'APR-DRG' as billing_code_type,
negotiated_type,
avg(negotiated_rate) as negotiated_rate,
array_join(array_agg(core_rates_id),',') as core_rates_id,
array_distinct(array_agg(billing_code)) as original_billing_codes,
'MS-DRG' as original_billing_code_type,
{% set exclude_apr_drg_cte_cols = [
'payer_id', 'provider_id', 'product_network_label', 'billing_code',
'billing_code_type', 'negotiated_type', 'negotiated_rate',
'core_rates_id',
] %}
{% for column in common_columns %}
{% if column not in exclude_apr_drg_cte_cols %}
ANY_VALUE({{ column }}) AS {{ column }} {{ ', ' if not loop.last }}
{% endif %}
{% endfor %}
FROM core_rates r
JOIN aprdrg_xwalk dc
ON r.billing_code = dc.ms_drg
WHERE billing_code_type = 'MS-DRG'
GROUP BY
payer_id,
provider_id,
product_network_label,
dc.apr_drg,
negotiated_type
),
-- APR-DRG (from MS-DRG): use many-to-one mapping of APR-DRG to MS-DRG
-- compute the average negotiated dollar and percentage for each MS-DRG
apr_drg_to_ms_drg AS (
SELECT
payer_id,
provider_id,
product_network_label,
dc.ms_drg as billing_code,
'MS-DRG' as billing_code_type,
negotiated_type,
avg(negotiated_rate) as negotiated_rate,
array_join(array_agg(core_rates_id),',') as core_rates_id,
array_distinct(array_agg(billing_code)) as original_billing_codes,
'APR-DRG' as original_billing_code_type,
{% set exclude_apr_drg_cte_cols = [
'payer_id', 'provider_id', 'product_network_label', 'billing_code',
'billing_code_type', 'negotiated_type', 'negotiated_rate',
'core_rates_id',
] %}
{% for column in common_columns %}
{% if column not in exclude_apr_drg_cte_cols %}
ANY_VALUE({{ column }}) AS {{ column }} {{ ', ' if not loop.last }}
{% endif %}
{% endfor %}
FROM core_rates r
JOIN aprdrg_xwalk dc
ON r.billing_code = dc.apr_drg
WHERE billing_code_type = 'APR-DRG'
GROUP BY
payer_id,
provider_id,
product_network_label,
dc.ms_drg,
negotiated_type
),
-- APC: use one-to-many mapping of APC to HCPCS
-- each HCPCS will be assigned the rate value of the APC
apc AS (
SELECT
xwalk.hcpcs as billing_code,
'HCPCS' as billing_code_type,
ARRAY[billing_code] as original_billing_codes,
'APC' as original_billing_code_type,
{% set exclude_apc_cte_cols = [
'billing_code', 'billing_code_type',
] %}
{% for column in common_columns %}
{% if column not in exclude_apc_cte_cols %}
r.{{ column }} {{ ', ' if not loop.last }}
{% endif %}
{% endfor %}
FROM core_rates r
JOIN apc_xwalk xwalk
ON r.billing_code = xwalk.apc
WHERE billing_code_type = 'APC'
),
-- EAPG: use one-to-many mapping of EAPG to HCPCS
-- each HCPCS will be assigned the rate value of the EAPG
eapg AS (
SELECT
xwalk.hcpcs as billing_code,
'HCPCS' as billing_code_type,
ARRAY[billing_code] as original_billing_codes,
'EAPG' as original_billing_code_type,
{% set exclude_eapg_cte_cols = [
'billing_code', 'billing_code_type',
] %}
{% for column in common_columns %}
{% if column not in exclude_eapg_cte_cols %}
r.{{ column }} {{ ', ' if not loop.last }}
{% endif %}
{% endfor %}
FROM core_rates r
JOIN eapg_xwalk xwalk
ON r.billing_code = xwalk.eapg
WHERE billing_code_type = 'EAPG'
),
-- combine HCPCS + MS-DRG and APR-DRG and APC and EAPG
rates AS (
SELECT
{% for column in common_columns %}
{{ column }} {{ ', ' if not loop.last }}
{% endfor %},
NULL as original_billing_codes,
NULL as original_billing_code_type
FROM hcpcs_msdrg
UNION ALL
SELECT
{% for column in common_columns %}
{{ column }} {{ ', ' if not loop.last }}
{% endfor %},
original_billing_codes,
original_billing_code_type
FROM ms_drg_to_apr_drg
UNION ALL
SELECT
{% for column in common_columns %}
{{ column }} {{ ', ' if not loop.last }}
{% endfor %},
original_billing_codes,
original_billing_code_type
FROM apr_drg_to_ms_drg
UNION ALL
SELECT
{% for column in common_columns %}
{{ column }} {{ ', ' if not loop.last }}
{% endfor %},
original_billing_codes,
original_billing_code_type
FROM apc
UNION ALL
SELECT
{% for column in common_columns %}
{{ column }} {{ ', ' if not loop.last }}
{% endfor %},
original_billing_codes,
original_billing_code_type
FROM eapg
),
-- filter raw data
rates_filtered AS (
SELECT
ros.roid,
ros.payer_id,
ros.network_id,
rates.provider_id,
rates.core_rates_id,
rates.billing_code,
rates.billing_code_type,
rates.original_billing_codes,
rates.original_billing_code_type,
rates.bill_type,
rates.billing_class,
rates.service_code,
rates.billing_code_modifier,
rates.modifier_count,
rates.drug_billing_class,
rates.payer_name,
rates.negotiation_arrangement,
rates.negotiated_type,
rates.negotiated_rate,
rates.additional_information,
rates.provider_group_id,
rates.provider_group_id_type,
rates.is_plausible,
rates.cbsa,
rates.state,
rates.product_network_label,
files.file_hash,
files.location_data_source_name,
files.last_updated_on
FROM rates
JOIN rate_object_space ros
ON rates.payer_id = ros.payer_id
AND rates.product_network_label = ros.payer_data_network
AND rates.provider_id = ros.provider_id
AND rates.billing_code = ros.billing_code
AND rates.billing_code_type = ros.billing_code_type
AND rates.bill_type = ros.bill_type
JOIN files
ON files.file_hash = rates.file_hash
WHERE is_plausible = True
),
-- get rate values
rates_ordered AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY
payer_id,
network_id,
provider_id,
negotiated_type,
bill_type,
billing_code,
billing_code_type
ORDER BY drug_billing_class ASC, modifier_count DESC, negotiated_rate DESC
) as rank
FROM rates_filtered
)
-- export
SELECT
rates.roid,
rates.core_rates_id,
rates.provider_id,
rates.network_id,
rates.billing_code,
rates.billing_code_type,
rates.billing_code_modifier,
rates.original_billing_codes,
rates.original_billing_code_type,
rates.bill_type,
CAST(NULL AS BOOLEAN) as facility,
rates.billing_class,
rates.negotiated_rate as rate,
rates.negotiated_type,
rates.negotiation_arrangement,
rates.service_code,
'{{ payer_schema.split("_",1)[-1] }}' as core_rates_month,
rates.file_hash,
rates.location_data_source_name,
rates.last_updated_on,
CAST(NULL AS DOUBLE) AS jaccard,
CAST(NULL AS INTEGER) AS provider_npi_count,
rates.payer_id
FROM rates_ordered rates
WHERE rank = 1
Physician Groups​
for each payer​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_raw_pg_rates_prebuild_{{ n_chunk }}_{{ sub_version }}
-- Subversion: {{ sub_version }}
CREATE TABLE {{ schema_name }}.tmp_raw_pg_rates_prebuild_{{ n_chunk }}_{{ sub_version }} AS
WITH
network_spine AS (
SELECT
network_id,
t.payer_data_network
FROM
{{ schema_name }}.tmp_ref_network_spine_{{ sub_version }},
UNNEST(payer_data_network) AS t(payer_data_network)
),
rate_object_space AS (
SELECT
DISTINCT
ros.payer_id,
rns.payer_data_network
FROM {{ schema_name }}.tmp_rate_object_space_{{ sub_version }} ros
LEFT JOIN network_spine rns
ON ros.network_id = rns.network_id
WHERE ros.payer_id IN ({{ payer_ids }})
AND bill_type IN ('Professional')
),
rates AS (
SELECT
{% if payer_schema[-7:] >= '2024_12' %}
a.id,
{% else %}
CAST(uuid() AS VARCHAR) as id,
{% endif %}
COALESCE(b.provider_id, c.provider_id) as provider_id,
payer_name,
a.payer_id,
product_network_label,
ein,
npi,
ptes.jaccard,
{% if sub_version > '2024_12' %}
provider_npi_count,
{% else %}
1 as provider_npi_count,
{% endif %}
COALESCE(b.health_system_id, c.health_system_id) as health_system_id,
COALESCE(b.state, c.state) as group_state,
billing_code,
billing_code_type,
billing_code_type_version,
negotiated_type,
negotiated_rate,
expiration_date,
billing_class,
service_code,
case
when a.payer_id = '643' AND coalesce(service_code, '') LIKE '%11%' then 'Global'
WHEN coalesce(service_code, '') LIKE '%11%' AND coalesce(service_code, '') LIKE '%22%' THEN 'Global' -- both present
WHEN coalesce(service_code, '') LIKE '%11%' THEN 'Non-Facility'
WHEN coalesce(service_code, '') LIKE '%22%' THEN 'Facility'
ELSE 'Global'
END AS service_code_category
FROM {{ payer_schema }}.core_rates_physician_groups a
LEFT JOIN (
SELECT DISTINCT provider_id, state, t.ein, health_system_id
FROM {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }}, UNNEST(ein) AS t(ein)
WHERE provider_type = 'Physician Group'
) b
ON a.provider_group_id = b.ein
AND a.provider_group_id_type = 'ein'
LEFT JOIN (
SELECT DISTINCT provider_id, state, t.npi, health_system_id
FROM {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }}, UNNEST(npi) AS t(npi)
WHERE provider_type = 'Physician Group'
) c
ON a.provider_group_id = c.npi
AND a.provider_group_id_type = 'npi'
LEFT JOIN {{ cld_params.Tables.CLD_PAC_TIN_EDGE_STATS.value }} ptes
ON b.provider_id = ptes.provider_id
AND a.provider_group_id = ptes.tin
JOIN rate_object_space ros
ON a.payer_id = ros.payer_id
AND a.product_network_label = ros.payer_data_network
WHERE
billing_class = 'professional'
AND negotiation_arrangement = 'ffs'
AND coalesce(billing_code_modifier,'') = ''
AND
(
coalesce (service_code,'11,22') like '%11%'
or coalesce(service_code,'11,22') like '%22%'
)
AND coalesce(billing_code_modifier,'') = ''
AND regexp_like(billing_code, '^[0-9][0-9]*[^a-zA-Z]?$')
AND billing_code_type = 'HCPCS'
AND negotiated_type IN ('negotiated','derived','fee schedule')
AND a.payer_id IN ({{ payer_ids }})
AND (
b.provider_id IS NOT NULL
OR c.provider_id IS NOT NULL
)
),
nonfacility AS (
SELECT
payer_name,
payer_id,
product_network_label,
provider_id,
billing_code,
billing_code_type,
'Non-Facility' as pos_designation,
negotiated_rate as best_rate,
ROW_NUMBER() OVER (
PARTITION BY
payer_name,
payer_id,
product_network_label,
provider_id,
billing_code,
billing_code_type
ORDER BY
CASE
WHEN service_code_category = 'Non-Facility' THEN 1
WHEN service_code_category = 'Global' THEN 2
WHEN service_code_category = 'Facility' THEN 3
ELSE 4
END,
jaccard DESC,
provider_npi_count DESC,
negotiated_rate DESC
) as rank
FROM rates
WHERE
billing_code not in (
SELECT distinct -- removing facility only codes (can't be done in office)
hcpcs
FROM {{ cld_params.Tables.MPFS_RVU.value }}
WHERE
REGEXP_LIKE(hcpcs, '^[0-9][0-9]*[^a-zA-Z]?$')
AND eff_start_dt = DATE('2024-04-01')
AND non_fac_pe_rvu = facility_pe_rvu
AND status_code = 'A'
)
),
facility AS (
SELECT
payer_name,
payer_id,
product_network_label,
provider_id,
billing_code,
billing_code_type,
'Facility' as pos_designation,
negotiated_rate as best_rate,
ROW_NUMBER() OVER (
PARTITION BY
payer_name,
payer_id,
product_network_label,
provider_id,
billing_code,
billing_code_type
ORDER BY
CASE
WHEN service_code_category = 'Facility' THEN 1
WHEN service_code_category = 'Global' THEN 2
WHEN service_code_category = 'Non-Facility' THEN 3
ELSE 4
END,
jaccard DESC,
provider_npi_count DESC,
negotiated_rate DESC
) as rank
FROM rates
)
SELECT
a.id,
a.payer_name,
a.payer_id,
a.product_network_label,
a.provider_id,
a.health_system_id,
a.group_state,
a.billing_code,
a.billing_code_type,
a.billing_code_type_version,
a.jaccard,
a.provider_npi_count,
a.negotiated_type,
a.negotiated_rate,
a.expiration_date,
a.billing_class,
b.pos_designation
FROM rates a
JOIN (
SELECT * FROM facility
WHERE rank = 1
UNION
SELECT * FROM nonfacility
WHERE rank = 1
) AS b
ON a.payer_id = b.payer_id
AND a.payer_name = b.payer_name
AND a.product_network_label = b.product_network_label
AND a.provider_id = b.provider_id
AND a.billing_code = b.billing_code
AND a.billing_code_type = b.billing_code_type
AND a.negotiated_rate = b.best_rate
for each payer​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_raw_mrf_payer_physician_group_rates_{{ n_chunk }}_{{ sub_version }}
-- Subversion: {{ sub_version }}
-- n_chunk: {{ n_chunk }}
{% set common_columns = [
'payer_id',
'provider_id',
'core_rates_id',
'product_network_label',
'billing_code',
'billing_code_type',
'bill_type',
'facility',
'billing_class',
'service_code',
'billing_code_modifier',
'modifier_count',
'jaccard',
'provider_npi_count',
'payer_name',
'negotiation_arrangement',
'negotiated_type',
'negotiated_rate',
'additional_information',
'provider_group_id',
'provider_group_id_type',
'is_plausible',
'cbsa',
'state',
] %}
CREATE TABLE {{ schema_name }}.tmp_raw_mrf_payer_physician_group_rates_{{ n_chunk }}_{{ sub_version }}
WITH (
PARTITIONING = ARRAY['payer_id']
)
AS
WITH
-- spines + rate object space
pg_prebuild AS (
SELECT
id,
payer_name,
payer_id,
product_network_label,
provider_id,
health_system_id,
group_state as state,
billing_code,
billing_code_type,
billing_code_type_version,
jaccard,
provider_npi_count,
negotiated_type,
negotiated_rate,
expiration_date,
billing_class,
pos_designation,
CASE
WHEN pos_designation = 'Facility' THEN true
WHEN pos_designation = 'Non-Facility' THEN false
END AS facility,
True as is_plausible,
CAST(NULL AS VARCHAR) as service_code,
CAST(NULL AS VARCHAR) as billing_code_modifier,
CAST(NULL AS VARCHAR) as negotiation_arrangement,
CAST(NULL AS VARCHAR) as additional_information,
CAST(NULL AS VARCHAR) as provider_group_id,
CAST(NULL AS VARCHAR) as provider_group_id_type,
CAST(NULL AS VARCHAR) as cbsa
FROM {{ schema_name }}.tmp_raw_pg_rates_prebuild_{{ n_chunk }}_{{ sub_version }}
),
network_spine AS (
SELECT
network_id,
t.payer_data_network
FROM
{{ schema_name }}.tmp_ref_network_spine_{{ sub_version }},
UNNEST(payer_data_network) AS t(payer_data_network)
),
rate_object_space AS (
SELECT
ros.roid,
ros.payer_id,
ros.network_id,
ros.provider_id,
ros.billing_code,
ros.billing_code_type,
ros.bill_type,
ros.facility,
rns.payer_data_network
FROM {{ schema_name }}.tmp_rate_object_space_{{ sub_version }} ros
LEFT JOIN network_spine rns
ON ros.network_id = rns.network_id
WHERE payer_id IN ({{ payer_ids }})
AND bill_type IN ('Professional')
),
-- get raw data
core_rates AS (
SELECT
{% set excluded_columns = [
'core_rates_id', 'bill_type', 'modifier_count', 'negotiated_rate'
] %}
{% for column in common_columns %}
{% if column not in excluded_columns %}
{{ column }},
{% endif %}
{% endfor %}
CASE
WHEN negotiated_type = 'percentage' AND negotiated_rate < 1 THEN negotiated_rate * 100
ELSE negotiated_rate
END as negotiated_rate,
{% if payer_schema[-7:] >= '2024_12' %}
id as core_rates_id,
{% else %}
CAST(uuid() AS VARCHAR) as core_rates_id,
{% endif %}
'Professional' as bill_type,
CASE
WHEN CARDINALITY(SPLIT(billing_code_modifier,',')) > 1 AND CONTAINS(SPLIT(billing_code_modifier,','),'TC') THEN infinity()
WHEN CARDINALITY(SPLIT(billing_code_modifier,',')) = 1 AND SPLIT(billing_code_modifier,',')[1] = '26' THEN -1
WHEN CARDINALITY(SPLIT(billing_code_modifier,',')) = 0 THEN infinity()
WHEN CARDINALITY(SPLIT(billing_code_modifier,',')) = 1 AND SPLIT(billing_code_modifier,',')[1] = '' THEN infinity()
ELSE CARDINALITY(SPLIT(billing_code_modifier,','))
END AS modifier_count
FROM pg_prebuild rates
WHERE payer_id IN ({{ payer_ids }})
),
-- HCPCS + MS-DRG: no action required
hcpcs_msdrg AS (
SELECT
{% for column in common_columns %}
{{ column }} {{ ', ' if not loop.last }}
{% endfor %}
FROM core_rates
WHERE billing_code_type IN ('MS-DRG', 'HCPCS')
),
-- combine HCPCS + MS-DRG and APR-DRG
rates AS (
SELECT
{% for column in common_columns %}
{{ column }} {{ ', ' if not loop.last }}
{% endfor %},
CAST(NULL AS ARRAY(VARCHAR)) as original_billing_codes,
CAST(NULL AS VARCHAR) as original_billing_code_type
FROM hcpcs_msdrg
),
-- filter raw data
rates_filtered AS (
SELECT
ros.roid,
ros.payer_id,
ros.network_id,
rates.provider_id,
rates.core_rates_id,
rates.billing_code,
rates.billing_code_type,
rates.original_billing_codes,
rates.original_billing_code_type,
rates.bill_type,
rates.facility,
rates.billing_class,
rates.service_code,
rates.billing_code_modifier,
rates.modifier_count,
rates.jaccard,
rates.provider_npi_count,
rates.payer_name,
rates.negotiation_arrangement,
rates.negotiated_type,
rates.negotiated_rate,
rates.additional_information,
rates.provider_group_id,
rates.provider_group_id_type,
rates.is_plausible,
rates.cbsa,
rates.state,
rates.product_network_label,
CAST(NULL AS VARCHAR) AS file_hash,
ARRAY[''] AS location_data_source_name,
CAST(NULL AS VARCHAR) AS last_updated_on
FROM rates
JOIN rate_object_space ros
ON rates.payer_id = ros.payer_id
AND rates.product_network_label = ros.payer_data_network
AND rates.provider_id = ros.provider_id
AND rates.billing_code = ros.billing_code
AND rates.billing_code_type = ros.billing_code_type
AND rates.bill_type = ros.bill_type
AND rates.facility = ros.facility
WHERE rates.is_plausible = True
),
-- get rate values
rates_ordered AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY
payer_id,
network_id,
provider_id,
negotiated_type,
bill_type,
billing_code,
billing_code_type,
facility
ORDER BY jaccard DESC, modifier_count DESC, provider_npi_count DESC, negotiated_rate DESC
) as rank
FROM rates_filtered
)
-- export
SELECT
rates.roid,
rates.core_rates_id,
rates.provider_id,
rates.network_id,
rates.billing_code,
rates.billing_code_type,
rates.billing_code_modifier,
rates.original_billing_codes,
rates.original_billing_code_type,
rates.bill_type,
rates.facility,
rates.billing_class,
rates.negotiated_rate as rate,
rates.negotiated_type,
rates.negotiation_arrangement,
rates.service_code,
'{{ payer_schema.split("_",1)[-1] }}' as core_rates_month,
rates.file_hash,
rates.location_data_source_name,
rates.last_updated_on,
rates.jaccard,
rates.provider_npi_count,
rates.payer_id
FROM rates_ordered rates
WHERE rank = 1
union​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_raw_mrf_payer_rates_{{ sub_version }}
-- Subversion: {{ sub_version }}
CREATE TABLE {{ schema_name }}.tmp_raw_mrf_payer_rates_{{ sub_version }}
WITH (
PARTITIONING = ARRAY['payer_id']
)
AS
{% for n_chunk in n_chunks %}
SELECT * FROM {{ schema_name }}.tmp_raw_mrf_payer_rates_{{ n_chunk }}_{{ sub_version }}
UNION ALL
{% endfor %}
{% for n_chunk in n_chunks %}
SELECT * FROM {{ schema_name }}.tmp_raw_mrf_payer_physician_group_rates_with_custom_logic_{{ n_chunk }}_{{ sub_version }}
{% if not loop.last %}UNION ALL{% endif %}
{% endfor %}
Combine All​
union​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_raw_mrf_payer_rates_{{ sub_version }}
-- Subversion: {{ sub_version }}
CREATE TABLE {{ schema_name }}.tmp_raw_mrf_payer_rates_{{ sub_version }}
WITH (
PARTITIONING = ARRAY['payer_id']
)
AS
{% for n_chunk in n_chunks %}
SELECT * FROM {{ schema_name }}.tmp_raw_mrf_payer_rates_{{ n_chunk }}_{{ sub_version }}
UNION ALL
{% endfor %}
{% for n_chunk in n_chunks %}
SELECT * FROM {{ schema_name }}.tmp_raw_mrf_payer_physician_group_rates_with_custom_logic_{{ n_chunk }}_{{ sub_version }}
{% if not loop.last %}UNION ALL{% endif %}
{% endfor %}