Custom Logic
Custom logic for specific data sources and edge cases.
Payer MRF​
Custom PG Logic​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_raw_mrf_payer_physician_group_rates_with_custom_logic_{{ n_chunk }}_{{ sub_version }}
-- Subversion: {{ sub_version }}
-- n_chunk: {{ n_chunk }}
CREATE OR REPLACE TABLE {{ schema_name }}.tmp_raw_mrf_payer_physician_group_rates_with_custom_logic_{{ n_chunk }}_{{ sub_version }}
WITH (
PARTITIONING = ARRAY['payer_id']
)
AS
WITH
all AS (
SELECT *
FROM {{ schema_name }}.tmp_raw_mrf_payer_physician_group_rates_{{ n_chunk }}_{{ sub_version }}
)
SELECT *
FROM all
WHERE NOT (
payer_id = '42'
AND
network_id = 3269223941459339425
)
UNION ALL
-- MAP EMBLEM GHI / CBP to ANTHEM GHI / CBP
SELECT
to_hex(substr(
sha256(CAST(json_format(CAST(ARRAY[
'Physician Group',
CASE
WHEN payer_id = '97' THEN '42'
ELSE payer_id
END,
CAST(
CASE
WHEN network_id = 5605126640932710930 THEN 3269223941459339425
ELSE network_id
END AS VARCHAR
),
CAST(provider_id AS VARCHAR),
CAST(bill_type AS VARCHAR),
CAST(billing_code AS VARCHAR),
CAST(billing_code_type AS VARCHAR),
COALESCE(CAST(facility AS VARCHAR), '')
] AS JSON)) AS VARBINARY)),
1, 12
)) AS roid,
core_rates_id,
provider_id,
CASE
WHEN network_id = 5605126640932710930 THEN 3269223941459339425
ELSE network_id
END AS network_id,
billing_code,
billing_code_type,
billing_code_modifier,
original_billing_codes,
original_billing_code_type,
bill_type,
facility,
billing_class,
rate,
negotiated_type,
negotiation_arrangement,
service_code,
'{{ payer_schema.split("_",1)[-1] }}' as core_rates_month,
file_hash,
location_data_source_name,
last_updated_on,
jaccard,
provider_npi_count,
CASE
WHEN payer_id = '97' THEN '42'
ELSE payer_id
END AS payer_id
FROM all
WHERE (
payer_id = '97'
AND
network_id = 5605126640932710930
)
Plan Bridge​
Custom Build Plan Bridge​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_ref_plan_bridge_custom_{{ n_chunk }}_{{ sub_version }}
-- Subversion: {{ sub_version }}
CREATE OR REPLACE TABLE {{ schema_name }}.tmp_ref_plan_bridge_custom_{{ n_chunk }}_{{ sub_version }}
WITH (
PARTITIONING = ARRAY['payer_id']
)
AS
WITH
extracted_ppo_data AS (
SELECT
payer_id,
network_name,
provider_id,
'PPO' as network_type,
ids AS array_ids
--ids AS array_ids,
-- CASE
-- WHEN has_ppo_plan THEN ids_with_ppo_plan
-- ELSE ids
-- END AS array_ids
FROM {{ schema_name }}.tmp_ref_plan_bridge_analysis_ppo_custom_{{ n_chunk }}_{{ sub_version }}
WHERE 1=1
AND is_mapped = True
),
extracted_hmo_data AS (
SELECT
payer_id,
network_name,
provider_id,
'HMO' as network_type,
ids AS array_ids
-- ids AS array_ids,
-- CASE
-- WHEN has_hmo_plan THEN ids_with_hmo_plan
-- ELSE ids
-- END AS array_ids
FROM {{ schema_name }}.tmp_ref_plan_bridge_analysis_hmo_custom_{{ n_chunk }}_{{ sub_version }}
WHERE 1=1
AND is_mapped = True
)
SELECT
distinct
network_name,
id,
provider_id,
payer_id,
network_type
FROM extracted_ppo_data,
UNNEST(array_ids) AS t(id)
WHERE
network_name IS NOT NULL
AND id IS NOT NULL
UNION ALL
SELECT
distinct
network_name,
id,
provider_id,
payer_id,
network_type
FROM extracted_hmo_data,
UNNEST(array_ids) AS t(id)
WHERE
network_name IS NOT NULL
AND id IS NOT NULL
Custom Prepare Plan Bridge - HMO​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_ref_plan_bridge_analysis_hmo_custom_{{ n_chunk }}_{{ sub_version }}
-- Subversion: {{ sub_version }}
-- n_chunk: {{ n_chunk }}
CREATE OR REPLACE TABLE {{ schema_name }}.tmp_ref_plan_bridge_analysis_hmo_custom_{{ n_chunk }}_{{ sub_version }}
WITH (
PARTITIONING = ARRAY['payer_id']
)
AS
WITH
-------------------------------------------------------
-- 0. Get Plans Existing Data for Emblem Network Logic
-- Generated here for computational reasons (avoids too many stages error)
-- https://github.com/turquoisehealth/cld-utils/blob/main/build/nycdoh/hospital_data.py
-------------------------------------------------------
base_hospital_data_hospital_rates AS (
SELECT b.*
FROM {{ schema_name }}.tmp_ref_plan_bridge_base_{{ sub_version }} b
LEFT JOIN {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }} ps
ON b.provider_id = ps.provider_id
WHERE
provider_state = 'NY'
AND payer_id IN (7, 97, 42, 958, 12, 26, 21)
AND COALESCE(billing_class, '') != 'Professional'
),
-------------------------------------------------------
-- 0. Get Plans Existing Data for Network Logic
-------------------------------------------------------
plans_existing AS (
SELECT DISTINCT
provider_id,
provider_name,
raw_payer_name,
plan_name,
payer_id,
payer_name,
payer_class_name
FROM base_hospital_data_hospital_rates
),
-------------------------------------------------------
-- 1. Emblem GHI HMO Network Function Logic (payer_id=97)
-------------------------------------------------------
emblem_ghi_hmo_logic AS (
SELECT
provider_id,
provider_state,
'Emblem' as payer_name,
raw_payer_name,
plan_name,
-- non_selected_plans,
-- Focused commercial value mapping (matches emblem_ghi_hmo_network_function.R)
CASE
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'GHI.*HMO|HMO.*GHI') THEN 0
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'HMO')
AND NOT regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'HIP') THEN 1
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'GHI')
AND NOT regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'PPO') THEN 2
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'GHI')
AND NOT regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'EPO') THEN 3
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'GHI') THEN 4
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'HMO') THEN 5
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'COMMERCIAL') THEN 6
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'ALL') THEN 7
ELSE 99
END as focused_commercial_value
FROM (
SELECT
pe.provider_id,
hr.provider_state,
CASE
WHEN pe.payer_id = 97 THEN pe.raw_payer_name
WHEN pe.payer_id = 12 AND (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%EMBLEM%'
OR (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%HIP%'
OR (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%GHI%'))) THEN pe.raw_payer_name
ELSE NULL
END as raw_payer_name,
CASE
WHEN pe.payer_id = 97 THEN pe.plan_name
WHEN pe.payer_id = 12 AND (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%EMBLEM%'
OR (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%HIP%'
OR (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%GHI%'))) THEN pe.plan_name
ELSE NULL
END as plan_name,
-- Flag excluded classes (includes CONNECTICUT for Emblem)
CASE
WHEN regexp_like(UPPER(COALESCE(pe.raw_payer_name, '') || COALESCE(pe.plan_name, '')), 'MEDICARE|MEDICAID|MCD|MCR|PACE|DUAL|MAP|SNP|CHILD|GOVERNMENT|CHP|FEDERAL|CONNECTICUT|BEHAVIOR|MENTAL')
OR pe.payer_class_name IN ('Medicare Advantage', 'Veterans Affairs', 'Managed Medicaid', 'Dual', 'Transplant', 'Dental', 'Automotive Medical Coverage', 'Workers'' Compensation')
THEN TRUE
ELSE FALSE
END as excluded_class,
-- Flag non-standard commercial (includes SELECT|ESS for Emblem)
CASE
WHEN regexp_like(UPPER(COALESCE(pe.raw_payer_name, '') || COALESCE(pe.plan_name, '')), 'HEALTHPLUS|ESSENTIAL|EXCHANGE|COMMUNITY|HIX|SELECT|ESS')
OR pe.payer_class_name = 'Exchange'
THEN TRUE
ELSE FALSE
END as non_standard_commercial
FROM plans_existing pe
LEFT JOIN base_hospital_data_hospital_rates hr
ON pe.provider_id = hr.provider_id
WHERE
(pe.payer_id = 97 OR (pe.payer_id = 12 AND ((UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%EMBLEM%'
OR (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%HIP%'
OR (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%GHI%'))))))
) combined_data
WHERE
excluded_class = FALSE
AND non_standard_commercial = FALSE
),
emblem_ghi_hmo_best_plans AS (
SELECT
provider_id,
provider_state,
raw_payer_name,
plan_name,
focused_commercial_value,
ROW_NUMBER() OVER (
PARTITION BY provider_id
ORDER BY focused_commercial_value ASC,
LENGTH(plan_name) DESC
) as rn
FROM emblem_ghi_hmo_logic
),
emblem_ghi_hmo_final AS (
SELECT
provider_id,
provider_state,
raw_payer_name,
plan_name,
-- Get non-selected plans
(SELECT array_join(array_agg(full_product ORDER BY full_product), ' | ')
FROM (
SELECT DISTINCT
COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '') as full_product
FROM emblem_ghi_hmo_logic e2
WHERE e2.provider_id = e1.provider_id
AND NOT (e2.raw_payer_name = e1.raw_payer_name AND e2.plan_name = e1.plan_name)
) non_selected
) as non_selected_plans
FROM emblem_ghi_hmo_best_plans e1
WHERE rn = 1
),
-------------------------------------------------------
-- 2. Emblem HIP HMO Network Function Logic (payer_id=97)
-------------------------------------------------------
emblem_hip_hmo_logic AS (
SELECT
provider_id,
provider_state,
'Emblem' as payer_name,
raw_payer_name,
plan_name,
-- non_selected_plans,
-- Focused commercial value mapping
CASE
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'HIP.*HMO|HMO.*HIP') THEN 0
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'HMO')
AND NOT regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'GHI') THEN 1
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'HIP')
AND NOT regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'PPO') THEN 2
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'HIP')
AND NOT regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'EPO') THEN 3
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'HIP') THEN 4
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'HMO') THEN 5
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'PRIME') THEN 6
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'COMMERCIAL') THEN 7
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'ALL') THEN 8
ELSE 99
END as focused_commercial_value
FROM (
SELECT
pe.provider_id,
hr.provider_state,
CASE
WHEN pe.payer_id = 97 THEN pe.raw_payer_name
WHEN pe.payer_id = 12 AND (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%EMBLEM%'
OR (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%HIP%'
OR (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%GHI%'))) THEN pe.raw_payer_name
ELSE NULL
END as raw_payer_name,
CASE
WHEN pe.payer_id = 97 THEN pe.plan_name
WHEN pe.payer_id = 12 AND (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%EMBLEM%'
OR (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%HIP%'
OR (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%GHI%'))) THEN pe.plan_name
ELSE NULL
END as plan_name,
-- Flag excluded classes (includes CONNECTICUT for Emblem)
CASE
WHEN regexp_like(UPPER(COALESCE(pe.raw_payer_name, '') || COALESCE(pe.plan_name, '')), 'MEDICARE|MEDICAID|MCD|MCR|PACE|DUAL|MAP|SNP|CHILD|GOVERNMENT|CHP|FEDERAL|CONNECTICUT|BEHAVIOR|MENTAL')
OR pe.payer_class_name IN ('Medicare Advantage', 'Veterans Affairs', 'Managed Medicaid', 'Dual', 'Transplant', 'Dental', 'Automotive Medical Coverage', 'Workers'' Compensation')
THEN TRUE
ELSE FALSE
END as excluded_class,
-- Flag non-standard commercial (includes SELECT|ESS for Emblem)
CASE
WHEN regexp_like(UPPER(COALESCE(pe.raw_payer_name, '') || COALESCE(pe.plan_name, '')), 'HEALTHPLUS|ESSENTIAL|EXCHANGE|COMMUNITY|HIX|SELECT|ESS')
OR pe.payer_class_name = 'Exchange'
THEN TRUE
ELSE FALSE
END as non_standard_commercial
FROM plans_existing pe
LEFT JOIN base_hospital_data_hospital_rates hr
ON pe.provider_id = hr.provider_id
WHERE
(pe.payer_id = 97 OR (pe.payer_id = 12 AND ((UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%EMBLEM%'
OR (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%HIP%'
OR (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%GHI%'))))))
) combined_data
WHERE
excluded_class = FALSE
AND non_standard_commercial = FALSE
),
emblem_hip_hmo_best_plans AS (
SELECT
provider_id,
provider_state,
raw_payer_name,
plan_name,
focused_commercial_value,
ROW_NUMBER() OVER (
PARTITION BY provider_id
ORDER BY focused_commercial_value ASC,
LENGTH(plan_name) DESC
) as rn
FROM emblem_hip_hmo_logic
),
emblem_hip_hmo_final AS (
SELECT
provider_id,
provider_state,
raw_payer_name,
plan_name,
-- Get non-selected plans
(SELECT array_join(array_agg(full_product ORDER BY full_product), ' | ')
FROM (
SELECT DISTINCT
COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '') as full_product
FROM emblem_hip_hmo_logic e2
WHERE e2.provider_id = e1.provider_id
AND NOT (e2.raw_payer_name = e1.raw_payer_name AND e2.plan_name = e1.plan_name)
) non_selected
) as non_selected_plans
FROM emblem_hip_hmo_best_plans e1
WHERE rn = 1
),
-------------------------------------------------------
-- 3. Emblem HIP Prime POS Network Function Logic (payer_id=97)
-------------------------------------------------------
emblem_hip_prime_logic AS (
SELECT
provider_id,
provider_state,
'Emblem' as payer_name,
raw_payer_name,
plan_name,
-- non_selected_plans,
-- Focused commercial value mapping
CASE
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'HIP.*PRIME|PRIME.*HIP') THEN 0
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'HIP.*POS|POS.*HIP') THEN 1
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'POS')
AND NOT regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'GHI') THEN 2
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'HIP')
AND NOT regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'EPO') THEN 3
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'HIP')
AND NOT regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'PPO') THEN 4
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'HIP')
AND NOT regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'HMO') THEN 5
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'HIP') THEN 6
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'PRIME') THEN 7
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'POS') THEN 8
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'COMMERCIAL') THEN 9
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'ALL') THEN 10
ELSE 99
END as focused_commercial_value
FROM (
SELECT
pe.provider_id,
hr.provider_state,
CASE
WHEN pe.payer_id = 97 THEN pe.raw_payer_name
WHEN pe.payer_id = 12 AND (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%EMBLEM%'
OR (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%HIP%'
OR (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%GHI%'))) THEN pe.raw_payer_name
ELSE NULL
END as raw_payer_name,
CASE
WHEN pe.payer_id = 97 THEN pe.plan_name
WHEN pe.payer_id = 12 AND (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%EMBLEM%'
OR (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%HIP%'
OR (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%GHI%'))) THEN pe.plan_name
ELSE NULL
END as plan_name,
-- Flag excluded classes (includes CONNECTICUT for Emblem)
CASE
WHEN regexp_like(UPPER(COALESCE(pe.raw_payer_name, '') || COALESCE(pe.plan_name, '')), 'MEDICARE|MEDICAID|MCD|MCR|PACE|DUAL|MAP|SNP|CHILD|GOVERNMENT|CHP|FEDERAL|CONNECTICUT|BEHAVIOR|MENTAL')
OR pe.payer_class_name IN ('Medicare Advantage', 'Veterans Affairs', 'Managed Medicaid', 'Dual', 'Transplant', 'Dental', 'Automotive Medical Coverage', 'Workers'' Compensation')
THEN TRUE
ELSE FALSE
END as excluded_class,
-- Flag non-standard commercial (includes SELECT|ESS for Emblem)
CASE
WHEN regexp_like(UPPER(COALESCE(pe.raw_payer_name, '') || COALESCE(pe.plan_name, '')), 'HEALTHPLUS|ESSENTIAL|EXCHANGE|COMMUNITY|HIX|SELECT|ESS')
OR pe.payer_class_name = 'Exchange'
THEN TRUE
ELSE FALSE
END as non_standard_commercial
FROM plans_existing pe
LEFT JOIN base_hospital_data_hospital_rates hr
ON pe.provider_id = hr.provider_id
WHERE
(pe.payer_id = 97 OR (pe.payer_id = 12 AND ((UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%EMBLEM%'
OR (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%HIP%'
OR (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%GHI%'))))))
) combined_data
WHERE
excluded_class = FALSE
AND non_standard_commercial = FALSE
),
emblem_hip_prime_best_plans AS (
SELECT
provider_id,
provider_state,
raw_payer_name,
plan_name,
focused_commercial_value,
ROW_NUMBER() OVER (
PARTITION BY provider_id
ORDER BY focused_commercial_value ASC,
LENGTH(plan_name) DESC
) as rn
FROM emblem_hip_prime_logic
),
emblem_hip_prime_final AS (
SELECT
provider_id,
provider_state,
raw_payer_name,
plan_name,
-- Get non-selected plans
(SELECT array_join(array_agg(full_product ORDER BY full_product), ' | ')
FROM (
SELECT DISTINCT
COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '') as full_product
FROM emblem_hip_prime_logic e2
WHERE e2.provider_id = e1.provider_id
AND NOT (e2.raw_payer_name = e1.raw_payer_name AND e2.plan_name = e1.plan_name)
) non_selected
) as non_selected_plans
FROM emblem_hip_prime_best_plans e1
WHERE rn = 1
),
-------------------------------------------------------
-- 4. Anthem EPO (payer_id=42) -- for GHI-CBP/Anthem Blue Cross Blue Shield
-------------------------------------------------------
anthem_epo_unsorted_data AS (
SELECT
pe.provider_id,
hr.provider_state,
pe.raw_payer_name,
pe.plan_name,
pe.payer_class_name
FROM plans_existing pe
LEFT JOIN base_hospital_data_hospital_rates hr
ON pe.provider_id = hr.provider_id
WHERE
pe.payer_id in (12,26,21)
AND (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%EMPIRE%'
OR UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%ANTHEM%')
),
anthem_epo_sorted_data AS (
SELECT
pe.provider_id,
hr.provider_state,
pe.raw_payer_name,
pe.plan_name,
pe.payer_class_name
FROM plans_existing pe
LEFT JOIN base_hospital_data_hospital_rates hr
ON pe.provider_id = hr.provider_id
WHERE pe.payer_id = 42
),
anthem_epo_combined_data AS (
SELECT * FROM anthem_epo_sorted_data
UNION ALL
SELECT * FROM anthem_epo_unsorted_data
),
anthem_epo_logic AS (
SELECT
provider_id,
provider_state,
'Anthem' as payer_name,
raw_payer_name,
plan_name,
CASE
WHEN (UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MEDICARE%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MEDICAID%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MCD%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MCR%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%PACE%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%DUAL%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MAP%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%SNP%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%CHILD%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%GOVERNMENT%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%CHP%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%FEDERAL%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%CONNECTICUT%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%BEHAVIOR%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MENTAL%')
OR payer_class_name IN ('Medicare Advantage', 'Veterans Affairs', 'Managed Medicaid', 'Dual', 'Transplant', 'Dental', 'Automotive Medical Coverage', 'Workers'' Compensation')
THEN TRUE
ELSE FALSE
END as excluded_class,
CASE
WHEN (UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%HEALTHPLUS%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%ESSENTIAL%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%EXCHANGE%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%COMMUNITY%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%HIX%')
OR payer_class_name = 'Exchange'
THEN TRUE
ELSE FALSE
END as non_standard_commercial,
CASE
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%ANTHEM EPO%' THEN 0
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%EMPIRE EPO%' THEN 1
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%BLUE ACCESS EPO LARGE%' THEN 2
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%BLUE ACCESS EPO%' THEN 3
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%EPO%' THEN 4
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%BLUE ACCESS%' THEN 5
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%BLUE_ACCESS%' THEN 6
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%PPO%' THEN 7
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%PREFERRED%' THEN 8
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%COMMERCIAL%' THEN 9
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%ALL%' THEN 10
ELSE 99
END as focused_commercial_value
FROM anthem_epo_combined_data
),
anthem_epo_best_plans AS (
SELECT
provider_id,
provider_state,
raw_payer_name,
plan_name,
focused_commercial_value,
ROW_NUMBER() OVER (
PARTITION BY provider_id
ORDER BY focused_commercial_value ASC,
LENGTH(plan_name) DESC
) as rn
FROM anthem_epo_logic
WHERE
excluded_class = FALSE
AND non_standard_commercial = FALSE
), anthem_epo_final as (
SELECT
provider_id,
provider_state,
raw_payer_name,
plan_name,
(SELECT array_join(array_agg(full_product ORDER BY full_product), ' | ')
FROM (
SELECT DISTINCT
COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '') as full_product
FROM anthem_epo_logic e2
WHERE e2.provider_id = e1.provider_id
AND NOT (e2.raw_payer_name = e1.raw_payer_name AND e2.plan_name = e1.plan_name)
) non_selected
) as non_selected_plans
FROM anthem_epo_best_plans e1
WHERE rn = 1
),
emblem_ghi_hmo_logic_ghi_cbp AS (
SELECT
provider_id,
provider_state,
'Emblem' as payer_name,
raw_payer_name,
plan_name,
-- non_selected_plans,
-- Focused commercial value mapping (matches emblem_ghi_hmo_network_function.R)
case
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'CBP') THEN -1
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'GHI.*HMO|HMO.*GHI') THEN 0
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'HMO')
AND NOT regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'HIP') THEN 1
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'GHI')
AND NOT regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'PPO') THEN 2
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'GHI')
AND NOT regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'EPO') THEN 3
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'GHI') THEN 4
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'HMO') THEN 5
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'COMMERCIAL') THEN 6
WHEN regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'ALL') THEN 7
ELSE 99
END as focused_commercial_value
FROM (
SELECT
pe.provider_id,
hr.provider_state,
CASE
WHEN pe.payer_id = 97 THEN pe.raw_payer_name
WHEN pe.payer_id = 12 AND (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%EMBLEM%'
OR (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%HIP%'
OR (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%GHI%'))) THEN pe.raw_payer_name
ELSE NULL
END as raw_payer_name,
CASE
WHEN pe.payer_id = 97 THEN pe.plan_name
WHEN pe.payer_id = 12 AND (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%EMBLEM%'
OR (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%GHI%')) THEN pe.plan_name
ELSE NULL
END as plan_name,
-- Flag excluded classes (includes CONNECTICUT for Emblem)
CASE
WHEN regexp_like(UPPER(COALESCE(pe.raw_payer_name, '') || COALESCE(pe.plan_name, '')), 'MEDICARE|MEDICAID|MCD|MCR|PACE|DUAL|MAP|SNP|CHILD|GOVERNMENT|CHP|FEDERAL|CONNECTICUT|CONNECTICARE|BEHAVIOR|MENTAL')
OR pe.payer_class_name IN ('Medicare Advantage', 'Veterans Affairs', 'Managed Medicaid', 'Dual', 'Transplant', 'Dental', 'Automotive Medical Coverage', 'Workers'' Compensation')
THEN TRUE
ELSE FALSE
END as excluded_class,
-- Flag non-standard commercial (includes SELECT|ESS for Emblem)
CASE
WHEN regexp_like(UPPER(COALESCE(pe.raw_payer_name, '') || COALESCE(pe.plan_name, '')), 'HEALTHPLUS|ESSENTIAL|EXCHANGE|COMMUNITY|HIX|SELECT|ESS')
OR pe.payer_class_name = 'Exchange'
THEN TRUE
ELSE FALSE
END as non_standard_commercial
FROM plans_existing pe
LEFT JOIN base_hospital_data_hospital_rates hr
ON pe.provider_id = hr.provider_id
WHERE
(pe.payer_id = 97 OR (pe.payer_id = 12 AND ((UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%EMBLEM%'
OR (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%HIP%'
OR (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%GHI%'))))))
) combined_data
WHERE
excluded_class = FALSE
AND non_standard_commercial = FALSE
),
emblem_ghi_hmo_best_plans_ghi_cbp AS (
SELECT
provider_id,
provider_state,
raw_payer_name,
plan_name,
focused_commercial_value,
ROW_NUMBER() OVER (
PARTITION BY provider_id
ORDER BY focused_commercial_value ASC,
LENGTH(plan_name) DESC
) as rn
FROM emblem_ghi_hmo_logic_ghi_cbp
),
emblem_ghi_hmo_final_ghi_cbp AS (
SELECT
provider_id,
provider_state,
raw_payer_name,
plan_name,
-- Get non-selected plans
(SELECT array_join(array_agg(full_product ORDER BY full_product), ' | ')
FROM (
SELECT DISTINCT
COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '') as full_product
FROM emblem_ghi_hmo_logic_ghi_cbp e2
WHERE e2.provider_id = e1.provider_id
AND NOT (e2.raw_payer_name = e1.raw_payer_name AND e2.plan_name = e1.plan_name)
) non_selected
) as non_selected_plans
FROM emblem_ghi_hmo_best_plans_ghi_cbp e1
WHERE rn = 1
),
-------------------------------------------------------
-- 4. Compute Metrics For Each Provider-Code Combination
-------------------------------------------------------
df_with_flags AS (
SELECT
provider_id,
raw_payer_name,
plan_name,
array_agg(id) as ids
FROM base_hospital_data_hospital_rates
GROUP BY 1,2,3
)
-------------------------------------------------------
-- 5. Build Plan Bridge with Emblem Network Logic (UNION for separate rows)
-------------------------------------------------------
-- GHI HMO Network
SELECT
df_with_flags.provider_id,
df_with_flags.ids,
TRUE as is_mapped,
'GHI HMO' as network_name,
egh.raw_payer_name as selected_raw_payer_name,
egh.plan_name as selected_plan_name,
egh.non_selected_plans,
CAST(97 AS VARCHAR) as payer_id
FROM df_with_flags
INNER JOIN emblem_ghi_hmo_final egh
ON df_with_flags.provider_id = egh.provider_id
AND df_with_flags.plan_name = egh.plan_name
AND df_with_flags.raw_payer_name = egh.raw_payer_name
UNION ALL
-- HIP HMO Network
SELECT
df_with_flags.provider_id,
df_with_flags.ids,
TRUE as is_mapped,
'HIP HMO' as network_name,
ehh.raw_payer_name as selected_raw_payer_name,
ehh.plan_name as selected_plan_name,
ehh.non_selected_plans,
CAST(97 AS VARCHAR) as payer_id
FROM df_with_flags
INNER JOIN emblem_hip_hmo_final ehh
ON df_with_flags.provider_id = ehh.provider_id
AND df_with_flags.plan_name = ehh.plan_name
AND df_with_flags.raw_payer_name = ehh.raw_payer_name
UNION ALL
-- HIP Prime POS Network
SELECT
df_with_flags.provider_id,
df_with_flags.ids,
TRUE as is_mapped,
'HIP Prime POS' as network_name,
ehp.raw_payer_name as selected_raw_payer_name,
ehp.plan_name as selected_plan_name,
ehp.non_selected_plans,
CAST(97 AS VARCHAR) as payer_id
FROM df_with_flags
INNER JOIN emblem_hip_prime_final ehp
ON df_with_flags.provider_id = ehp.provider_id
AND df_with_flags.plan_name = ehp.plan_name
AND df_with_flags.raw_payer_name = ehp.raw_payer_name
UNION ALL
-- GHI-CBP/Anthem Blue Cross and Blue Shield
SELECT
provider_id,
ids,
TRUE as is_mapped,
'GHI / Anthem CBP' as network_name,
selected_raw_payer_name,
selected_plan_name,
non_selected_plans,
CAST(42 AS VARCHAR) as payer_id
FROM (
SELECT
df_with_flags.provider_id,
df_with_flags.ids,
ae.raw_payer_name as selected_raw_payer_name,
ae.plan_name as selected_plan_name,
ae.non_selected_plans
FROM df_with_flags
INNER JOIN anthem_epo_final ae
ON df_with_flags.provider_id = ae.provider_id
AND df_with_flags.plan_name = ae.plan_name
AND df_with_flags.raw_payer_name = ae.raw_payer_name
UNION ALL
SELECT
df_with_flags.provider_id,
df_with_flags.ids,
egh.raw_payer_name as selected_raw_payer_name,
egh.plan_name as selected_plan_name,
egh.non_selected_plans
FROM df_with_flags
INNER JOIN emblem_ghi_hmo_final_ghi_cbp egh
ON df_with_flags.provider_id = egh.provider_id
AND df_with_flags.plan_name = egh.plan_name
AND df_with_flags.raw_payer_name = egh.raw_payer_name
)
Custom Prepare Plan Bridge - PPO​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_ref_plan_bridge_analysis_ppo_custom_{{ n_chunk }}_{{ sub_version }}
-- Subversion: {{ sub_version }}
-- n_chunk: {{ n_chunk }}
CREATE OR REPLACE TABLE {{ schema_name }}.tmp_ref_plan_bridge_analysis_ppo_custom_{{ n_chunk }}_{{ sub_version }}
WITH (
PARTITIONING = ARRAY['payer_id']
)
AS
WITH
-- Generated here for computational reasons (avoids too many stages error)
-- https://github.com/turquoisehealth/cld-utils/blob/main/build/nycdoh/hospital_data.py
base_hospital_data_hospital_rates AS (
SELECT b.*
FROM {{ schema_name }}.tmp_ref_plan_bridge_base_{{ sub_version }} b
LEFT JOIN {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }} ps
ON b.provider_id = ps.provider_id
WHERE
provider_state = 'NY'
AND payer_id IN (7, 97, 42, 958, 12, 26, 21)
AND COALESCE(billing_class, '') != 'Professional'
),
-------------------------------------------------------
-- 0. Get Plans Existing Data for Network Logic
-------------------------------------------------------
plans_existing AS (
SELECT DISTINCT
provider_id,
provider_name,
raw_payer_name,
plan_name,
payer_id,
payer_name,
payer_class_name
FROM base_hospital_data_hospital_rates
),
-------------------------------------------------------
-- 1. Aetna EPO Network Function Logic (payer_id=7)
-------------------------------------------------------
aetna_epo_logic AS (
SELECT
provider_id,
provider_state,
'Aetna' as payer_name,
raw_payer_name,
plan_name,
-- non_selected_plans,
-- Focused commercial value mapping
CASE
WHEN (UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%OPEN ACCESS ELECT CHOICE%') THEN 0
WHEN (UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%OPEN ACCESS ELECT%') THEN 1
WHEN (UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%ELECT%') THEN 2
WHEN (UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%EPO%') THEN 3
WHEN (UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%OPEN ACCESS%') THEN 4
WHEN (UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%CHOICE%') THEN 5
WHEN (UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%PPO%') THEN 6
WHEN (UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%COMMERCIAL%') THEN 7
WHEN (UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%ALL%') THEN 8
ELSE 99
END as focused_commercial_value
FROM (
SELECT
pe.provider_id,
hr.provider_state,
CASE
WHEN pe.payer_id = 7 THEN pe.raw_payer_name
WHEN pe.payer_id = 12 AND (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%AETNA%') THEN pe.raw_payer_name
ELSE NULL
END as raw_payer_name,
CASE
WHEN pe.payer_id = 7 THEN pe.plan_name
WHEN pe.payer_id = 12 AND (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%AETNA%') THEN pe.plan_name
ELSE NULL
END as plan_name,
-- Flag excluded classes
CASE
WHEN (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%MEDICARE%'
OR UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%MEDICAID%'
OR UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%MCD%'
OR UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%MCR%'
OR UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%PACE%'
OR UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%DUAL%'
OR UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%MAP%'
OR UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%SNP%'
OR UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%CHILD%'
OR UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%GOVERNMENT%'
OR UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%CHP%'
OR UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%FEDERAL%'
OR UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%BEHAVIOR%'
OR UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%MENTAL%')
OR pe.payer_class_name IN ('Medicare Advantage', 'Veterans Affairs', 'Managed Medicaid', 'Dual', 'Transplant', 'Dental', 'Automotive Medical Coverage', 'Workers'' Compensation')
THEN TRUE
ELSE FALSE
END as excluded_class,
-- Flag non-standard commercial
CASE
WHEN (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%HEALTHPLUS%'
OR UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%ESSENTIAL%'
OR UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%EXCHANGE%'
OR UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%COMMUNITY%'
OR UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%HIX%')
OR pe.payer_class_name = 'Exchange'
THEN TRUE
ELSE FALSE
END as non_standard_commercial
FROM plans_existing pe
LEFT JOIN base_hospital_data_hospital_rates hr
ON pe.provider_id = hr.provider_id
WHERE
(pe.payer_id = 7 OR (pe.payer_id = 12 AND (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%AETNA%')))
) combined_data
WHERE
excluded_class = FALSE
AND non_standard_commercial = FALSE
),
aetna_epo_best_plans AS (
SELECT
provider_id,
provider_state,
raw_payer_name,
plan_name,
focused_commercial_value,
ROW_NUMBER() OVER (
PARTITION BY provider_id
ORDER BY focused_commercial_value ASC,
LENGTH(plan_name) DESC
) as rn
FROM aetna_epo_logic
),
aetna_epo_final AS (
SELECT
provider_id,
provider_state,
raw_payer_name,
plan_name,
-- Get non-selected plans
(SELECT array_join(array_agg(full_product ORDER BY full_product), ' | ')
FROM (
SELECT DISTINCT
COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '') as full_product
FROM aetna_epo_logic a2
WHERE a2.provider_id = a1.provider_id
AND NOT (a2.raw_payer_name = a1.raw_payer_name AND a2.plan_name = a1.plan_name)
) non_selected
) as non_selected_plans
FROM aetna_epo_best_plans a1
WHERE rn = 1
),
-------------------------------------------------------
-- 2. Emblem DC37 Network Function Logic (payer_id=97)
-------------------------------------------------------
-- Step 1: Find unsorted payers (payer_id = 12) with 'EMBLEM|DC37'
emblem_unsorted_data AS (
SELECT
pe.provider_id,
hr.provider_state,
pe.raw_payer_name,
pe.plan_name,
pe.payer_class_name
FROM plans_existing pe
LEFT JOIN base_hospital_data_hospital_rates hr
ON pe.provider_id = hr.provider_id
WHERE
pe.payer_id = 12
AND (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%EMBLEM%'
OR UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%DC37%')
),
-- Step 2: Find sorted data (payer_id = 97)
emblem_sorted_data AS (
SELECT
pe.provider_id,
hr.provider_state,
pe.raw_payer_name,
pe.plan_name,
pe.payer_class_name
FROM plans_existing pe
LEFT JOIN base_hospital_data_hospital_rates hr
ON pe.provider_id = hr.provider_id
WHERE pe.payer_id = 97
),
-- Step 3: Combine unsorted & sorted data
emblem_combined_data AS (
SELECT * FROM emblem_sorted_data
UNION ALL
SELECT * FROM emblem_unsorted_data
),
-- Step 4-6: Apply filtering and focused commercial value mapping
emblem_dc37_logic AS (
SELECT
provider_id,
provider_state,
'Emblem' as payer_name,
raw_payer_name,
plan_name,
-- Step 4: Flag excluded classes (includes CONNECTICUT for Emblem)
CASE
WHEN (UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MEDICARE%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MEDICAID%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MCD%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MCR%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%PACE%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%DUAL%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MAP%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%SNP%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%CHILD%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%GOVERNMENT%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%CHP%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%FEDERAL%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%CONNECTICUT%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%BEHAVIOR%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MENTAL%')
OR payer_class_name IN ('Medicare Advantage', 'Veterans Affairs', 'Managed Medicaid', 'Dual', 'Transplant', 'Dental', 'Automotive Medical Coverage', 'Workers'' Compensation')
THEN TRUE
ELSE FALSE
END as excluded_class,
-- Step 5: Flag non-standard commercial (includes SELECT|ESS for Emblem)
CASE
WHEN (UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%HEALTHPLUS%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%ESSENTIAL%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%EXCHANGE%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%COMMUNITY%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%HIX%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%SELECT%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%ESS%')
OR payer_class_name = 'Exchange'
THEN TRUE
ELSE FALSE
END as non_standard_commercial,
-- Step 6: Focused commercial value mapping
CASE
WHEN (UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%DC37%') THEN 0
WHEN (UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%PPO%') THEN 1
WHEN (UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%PREFERRED%') THEN 2
WHEN (UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%COMMERCIAL%') THEN 3
WHEN (UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%ALL%') THEN 4
ELSE 99
END as focused_commercial_value
FROM emblem_combined_data
),
-- Step 7: Determine the final utilized plan (eligible only)
emblem_dc37_best_plans AS (
SELECT
provider_id,
provider_state,
raw_payer_name,
plan_name,
focused_commercial_value,
ROW_NUMBER() OVER (
PARTITION BY provider_id
ORDER BY focused_commercial_value ASC,
LENGTH(plan_name) DESC
) as rn
FROM emblem_dc37_logic
WHERE
excluded_class = FALSE
AND non_standard_commercial = FALSE
),
emblem_dc37_final AS (
SELECT
provider_id,
provider_state,
raw_payer_name,
plan_name,
-- Step 8: Get non-selected plans (all plans except the selected one)
(SELECT array_join(array_agg(full_product ORDER BY full_product), ' | ')
FROM (
SELECT DISTINCT
COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '') as full_product
FROM emblem_dc37_logic e2
WHERE e2.provider_id = e1.provider_id
AND NOT (e2.raw_payer_name = e1.raw_payer_name AND e2.plan_name = e1.plan_name)
) non_selected
) as non_selected_plans
FROM emblem_dc37_best_plans e1
WHERE rn = 1
),
-------------------------------------------------------
-- 3. Anthem EPO (payer_id=42)
-------------------------------------------------------
anthem_epo_unsorted_data AS (
SELECT
pe.provider_id,
hr.provider_state,
pe.raw_payer_name,
pe.plan_name,
pe.payer_class_name
FROM plans_existing pe
LEFT JOIN base_hospital_data_hospital_rates hr
ON pe.provider_id = hr.provider_id
WHERE
pe.payer_id in (12,26,21)
AND (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%EMPIRE%'
OR UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%ANTHEM%')
),
anthem_epo_sorted_data AS (
SELECT
pe.provider_id,
hr.provider_state,
pe.raw_payer_name,
pe.plan_name,
pe.payer_class_name
FROM plans_existing pe
LEFT JOIN base_hospital_data_hospital_rates hr
ON pe.provider_id = hr.provider_id
WHERE pe.payer_id = 42
),
anthem_epo_combined_data AS (
SELECT * FROM anthem_epo_sorted_data
UNION ALL
SELECT * FROM anthem_epo_unsorted_data
),
anthem_epo_logic AS (
SELECT
provider_id,
provider_state,
'Anthem' as payer_name,
raw_payer_name,
plan_name,
CASE
WHEN (UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MEDICARE%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MEDICAID%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MCD%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MCR%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%PACE%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%DUAL%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MAP%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%SNP%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%CHILD%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%GOVERNMENT%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%CHP%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%FEDERAL%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%CONNECTICUT%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%BEHAVIOR%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MENTAL%')
OR payer_class_name IN ('Medicare Advantage', 'Veterans Affairs', 'Managed Medicaid', 'Dual', 'Transplant', 'Dental', 'Automotive Medical Coverage', 'Workers'' Compensation')
THEN TRUE
ELSE FALSE
END as excluded_class,
CASE
WHEN (UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%HEALTHPLUS%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%ESSENTIAL%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%EXCHANGE%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%COMMUNITY%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%HIX%')
OR payer_class_name = 'Exchange'
THEN TRUE
ELSE FALSE
END as non_standard_commercial,
CASE
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%ANTHEM EPO%' THEN 0
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%EMPIRE EPO%' THEN 1
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%BLUE ACCESS EPO LARGE%' THEN 2
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%BLUE ACCESS EPO%' THEN 3
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%EPO%' THEN 4
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%BLUE ACCESS%' THEN 5
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%BLUE_ACCESS%' THEN 6
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%PPO%' THEN 7
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%PREFERRED%' THEN 8
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%COMMERCIAL%' THEN 9
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%ALL%' THEN 10
ELSE 99
END as focused_commercial_value
FROM anthem_epo_combined_data
),
anthem_epo_best_plans AS (
SELECT
provider_id,
provider_state,
raw_payer_name,
plan_name,
focused_commercial_value,
ROW_NUMBER() OVER (
PARTITION BY provider_id
ORDER BY focused_commercial_value ASC,
LENGTH(plan_name) DESC
) as rn
FROM anthem_epo_logic
WHERE
excluded_class = FALSE
AND non_standard_commercial = FALSE
), anthem_epo_final as (
SELECT
provider_id,
provider_state,
raw_payer_name,
plan_name,
(SELECT array_join(array_agg(full_product ORDER BY full_product), ' | ')
FROM (
SELECT DISTINCT
COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '') as full_product
FROM anthem_epo_logic e2
WHERE e2.provider_id = e1.provider_id
AND NOT (e2.raw_payer_name = e1.raw_payer_name AND e2.plan_name = e1.plan_name)
) non_selected
) as non_selected_plans
FROM anthem_epo_best_plans e1
WHERE rn = 1
),
-------------------------------------------------------
-- 4. Anthem Blue Access Gated EPO (payer_id=42)
-------------------------------------------------------
anthem_blueaccess_unsorted_data AS (
SELECT
pe.provider_id,
hr.provider_state,
pe.raw_payer_name,
pe.plan_name,
pe.payer_class_name
FROM plans_existing pe
LEFT JOIN base_hospital_data_hospital_rates hr
ON pe.provider_id = hr.provider_id
WHERE
pe.payer_id in (12,26,21)
AND (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%EMPIRE%'
OR UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%ANTHEM%')
),
anthem_blueaccess_sorted_data AS (
SELECT
pe.provider_id,
hr.provider_state,
pe.raw_payer_name,
pe.plan_name,
pe.payer_class_name
FROM plans_existing pe
LEFT JOIN base_hospital_data_hospital_rates hr
ON pe.provider_id = hr.provider_id
WHERE pe.payer_id = 42
),
anthem_blueaccess_combined_data AS (
SELECT * FROM anthem_blueaccess_unsorted_data
UNION ALL
SELECT * FROM anthem_blueaccess_sorted_data
),
anthem_blueaccess_logic AS (
SELECT
provider_id,
provider_state,
'Anthem' as payer_name,
raw_payer_name,
plan_name,
CASE
WHEN (UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MEDICARE%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MEDICAID%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MCD%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MCR%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%PACE%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%DUAL%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MAP%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%SNP%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%CHILD%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%GOVERNMENT%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%CHP%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%FEDERAL%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%CONNECTICUT%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%BEHAVIOR%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MENTAL%')
OR payer_class_name IN ('Medicare Advantage', 'Veterans Affairs', 'Managed Medicaid', 'Dual', 'Transplant', 'Dental', 'Automotive Medical Coverage', 'Workers'' Compensation')
THEN TRUE
ELSE FALSE
END as excluded_class,
CASE
WHEN (UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%HEALTHPLUS%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%ESSENTIAL%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%EXCHANGE%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%COMMUNITY%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%HIX%')
OR payer_class_name = 'Exchange'
THEN TRUE
ELSE FALSE
END as non_standard_commercial,
case
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%EPO GATED%' THEN 0
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%BLUE ACCESS GATED%' THEN 1
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%GATED EPO%' THEN 2
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%BLUE ACCESS EPO LARGE%' THEN 3
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%BLUE ACCESS EPO%' THEN 4
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%BLUE ACCESS%' THEN 5
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%BLUE_ACCESS%' THEN 6
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%ANTHEM EPO%' THEN 7
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%EMPIRE EPO%' THEN 8
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%EPO%' THEN 9
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%PPO%' THEN 10
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%PREFERRED%' THEN 11
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%COMMERCIAL%' THEN 12
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%ALL%' THEN 13
ELSE 99
END as focused_commercial_value
FROM anthem_blueaccess_combined_data
),
anthem_blueaccess_best_plans AS (
SELECT
provider_id,
provider_state,
raw_payer_name,
plan_name,
focused_commercial_value,
ROW_NUMBER() OVER (
PARTITION BY provider_id
ORDER BY focused_commercial_value ASC,
LENGTH(plan_name) DESC
) as rn
FROM anthem_blueaccess_logic
WHERE
excluded_class = FALSE
AND non_standard_commercial = FALSE
), anthem_blueaccess_final as (
SELECT
provider_id,
provider_state,
raw_payer_name,
plan_name,
(SELECT array_join(array_agg(full_product ORDER BY full_product), ' | ')
FROM (
SELECT DISTINCT
COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '') as full_product
FROM anthem_blueaccess_logic e2
WHERE e2.provider_id = e1.provider_id
AND NOT (e2.raw_payer_name = e1.raw_payer_name AND e2.plan_name = e1.plan_name)
) non_selected
) as non_selected_plans
FROM anthem_blueaccess_best_plans e1
WHERE rn = 1
),
-------------------------------------------------------
-- 5. MetroPlus Health Gold (payer_id=958)
-------------------------------------------------------
metroplus_unsorted_data AS (
SELECT
pe.provider_id,
hr.provider_state,
pe.raw_payer_name,
pe.plan_name,
pe.payer_class_name
FROM plans_existing pe
LEFT JOIN base_hospital_data_hospital_rates hr
ON pe.provider_id = hr.provider_id
WHERE
pe.payer_id in (12)
AND (UPPER(COALESCE(pe.raw_payer_name, '') || ' ' || COALESCE(pe.plan_name, '')) LIKE '%METROPLUS%')
),
metroplus_sorted_data AS (
SELECT
pe.provider_id,
hr.provider_state,
pe.raw_payer_name,
pe.plan_name,
pe.payer_class_name
FROM plans_existing pe
LEFT JOIN base_hospital_data_hospital_rates hr
ON pe.provider_id = hr.provider_id
WHERE pe.payer_id = 958
),
metroplus_combined_data AS (
SELECT * FROM metroplus_sorted_data
UNION ALL
SELECT * FROM metroplus_unsorted_data
),
metroplus_logic AS (
SELECT
provider_id,
provider_state,
'MetroPlus Health' as payer_name,
raw_payer_name,
plan_name,
CASE
WHEN (UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MEDICARE%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MEDICAID%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MCD%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MCR%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%PACE%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%DUAL%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MAP%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%SNP%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%CHILD%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%GOVERNMENT%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%CHP%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%FEDERAL%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%CONNECTICUT%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%BEHAVIOR%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MENTAL%')
OR payer_class_name IN ('Medicare Advantage', 'Veterans Affairs', 'Managed Medicaid', 'Dual', 'Transplant', 'Dental', 'Automotive Medical Coverage', 'Workers'' Compensation')
THEN TRUE
ELSE FALSE
END as excluded_class,
CASE
WHEN (UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%HEALTHPLUS%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%ESSENTIAL%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%EXCHANGE%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%COMMUNITY%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%HIX%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%MAP%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%SENIOR%'
OR UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%ULTRA%'
)
OR payer_class_name = 'Exchange'
THEN TRUE
ELSE FALSE
END as non_standard_commercial,
case
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%GOLD%'
AND NOT regexp_like(UPPER(COALESCE(raw_payer_name, '') || COALESCE(plan_name, '')), 'CARE') THEN 0
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%GOLD%' THEN 1
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%COMMERCIAL%' THEN 2
WHEN UPPER(COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '')) LIKE '%ALL%' THEN 3
ELSE 99
END as focused_commercial_value
FROM metroplus_combined_data
),
metroplus_best_plans AS (
SELECT
provider_id,
provider_state,
raw_payer_name,
plan_name,
focused_commercial_value,
ROW_NUMBER() OVER (
PARTITION BY provider_id
ORDER BY focused_commercial_value ASC,
LENGTH(plan_name) DESC
) as rn
FROM metroplus_logic
WHERE
excluded_class = FALSE
AND non_standard_commercial = FALSE
), metroplus_final as (
SELECT
provider_id,
provider_state,
raw_payer_name,
plan_name,
(SELECT array_join(array_agg(full_product ORDER BY full_product), ' | ')
FROM (
SELECT DISTINCT
COALESCE(raw_payer_name, '') || ' ' || COALESCE(plan_name, '') as full_product
FROM metroplus_logic e2
WHERE e2.provider_id = e1.provider_id
AND NOT (e2.raw_payer_name = e1.raw_payer_name AND e2.plan_name = e1.plan_name)
) non_selected
) as non_selected_plans
FROM metroplus_best_plans e1
WHERE rn = 1
),
-------------------------------------------------------
-- 7. Compute Metrics For Each Provider-Code Combination
-------------------------------------------------------
df_with_flags AS (
SELECT
provider_id,
raw_payer_name,
plan_name,
array_agg(id) as ids
FROM base_hospital_data_hospital_rates
GROUP BY 1,2,3
)
-------------------------------------------------------
-- 8. Build Plan Bridge with Network Logic (UNION for separate rows)
-------------------------------------------------------
-- Aetna EPO Network
SELECT
df_with_flags.provider_id,
df_with_flags.ids,
TRUE as is_mapped,
'EPO' as network_name,
aep.raw_payer_name as selected_raw_payer_name,
aep.plan_name as selected_plan_name,
aep.non_selected_plans,
CAST(7 AS VARCHAR) as payer_id
FROM df_with_flags
INNER JOIN aetna_epo_final aep
ON df_with_flags.provider_id = aep.provider_id
AND df_with_flags.raw_payer_name = aep.raw_payer_name
AND df_with_flags.plan_name = aep.plan_name
UNION ALL
-- Emblem DC37 Network
SELECT
df_with_flags.provider_id,
df_with_flags.ids,
TRUE as is_mapped,
'DC37 Med-Team' as network_name,
edc.raw_payer_name as selected_raw_payer_name,
edc.plan_name as selected_plan_name,
edc.non_selected_plans,
CAST(97 AS VARCHAR) as payer_id
FROM df_with_flags
INNER JOIN emblem_dc37_final edc
ON df_with_flags.provider_id = edc.provider_id
AND df_with_flags.raw_payer_name = edc.raw_payer_name
AND df_with_flags.plan_name = edc.plan_name
UNION ALL
-- Anthem EPO Netowrk
SELECT
df_with_flags.provider_id,
df_with_flags.ids,
TRUE as is_mapped,
'EPO' as network_name,
epf.raw_payer_name as selected_raw_payer_name,
epf.plan_name as selected_plan_name,
epf.non_selected_plans,
CAST(42 AS VARCHAR) as payer_id
FROM df_with_flags
INNER JOIN anthem_epo_final epf
ON df_with_flags.provider_id = epf.provider_id
AND df_with_flags.raw_payer_name = epf.raw_payer_name
AND df_with_flags.plan_name = epf.plan_name
UNION ALL
-- Anthem Blue Access Gated EPO Network
SELECT
df_with_flags.provider_id,
df_with_flags.ids,
TRUE as is_mapped,
'Blue Access Gated EPO' as network_name,
abe.raw_payer_name as selected_raw_payer_name,
abe.plan_name as selected_plan_name,
abe.non_selected_plans,
CAST(42 AS VARCHAR) as payer_id
FROM df_with_flags
INNER JOIN anthem_blueaccess_final abe
ON df_with_flags.provider_id = abe.provider_id
AND df_with_flags.raw_payer_name = abe.raw_payer_name
AND df_with_flags.plan_name = abe.plan_name
UNION ALL
-- MetroPlus Health Gold Network
SELECT
df_with_flags.provider_id,
df_with_flags.ids,
TRUE as is_mapped,
'Gold' as network_name,
mph.raw_payer_name as selected_raw_payer_name,
mph.plan_name as selected_plan_name,
mph.non_selected_plans,
CAST(958 AS VARCHAR) as payer_id
FROM df_with_flags
INNER JOIN metroplus_final mph
ON df_with_flags.provider_id = mph.provider_id
AND df_with_flags.raw_payer_name = mph.raw_payer_name
AND df_with_flags.plan_name = mph.plan_name