Spines
Provider​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }}
-- Subversion: {{ sub_version }}
{% set US_STATES = "
'AL', 'AK', 'AZ', 'AR', 'CA', 'CO', 'CT', 'DE', 'FL', 'GA',
'HI', 'ID', 'IL', 'IN', 'IA', 'KS', 'KY', 'LA', 'ME', 'MD',
'MA', 'MI', 'MN', 'MS', 'MO', 'MT', 'NE', 'NV', 'NH', 'NJ',
'NM', 'NY', 'NC', 'ND', 'OH', 'OK', 'OR', 'PA', 'RI', 'SC',
'SD', 'TN', 'TX', 'UT', 'VT', 'VA', 'WA', 'WV', 'WI', 'WY',
'DC'
" %}
CREATE TABLE {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }} AS
WITH
geo_single_county_mapping as (
select
city_name
, state_abbreviation
, count(distinct county_name) as county_count
from {{ cld_params.Tables.SPINES_GEO_FLATTENED.value }}
group by 1, 2
having count(distinct county_name) = 1
),
geo_flat as (
select
a.city_name
, a.state_abbreviation
, a.county_name
from {{ cld_params.Tables.SPINES_GEO_FLATTENED.value }} as a
inner join geo_single_county_mapping as b on
a.city_name = b.city_name
and a.state_abbreviation = b.state_abbreviation
),
zip_carrier_locality AS (
select zip_code, any_value(zip.carrier_locality) as carrier_locality
from {{ cld_params.Tables.REF_CMS_ZIP_CARRIER_LOCALITY.value }} zip
join (
select carrier_locality, max(eff_start_dt) as eff_start_dt, max(eff_end_dt) as eff_end_dt
from {{ cld_params.Tables.REF_CMS_ZIP_CARRIER_LOCALITY.value }}
group by carrier_locality
) latest
on latest.carrier_locality = zip.carrier_locality
and latest.eff_start_dt = zip.eff_start_dt
and latest.eff_end_dt = zip.eff_end_dt
GROUP BY 1
),
geo_cbsa as (
select
cbsa_id
, cbsa_name
from {{ cld_params.Tables.SPINES_GEO_CBSA.value }}
),
-- MISC PROVIDER DETAILS
nucc as (
select
taxonomy_code
,taxonomy_grouping
from {{ cld_params.Tables.SPINES_PROVIDER_INTERMEDIATE_NUCC_LATEST.value }}
),
-- HOSPITALS
hospital_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_HOSPITAL.value }}
UNION ALL
SELECT
provider_id,
npi
FROM {{ cld_params.Tables.SPINES_PROVIDER_HOSPITAL_ADDITIONAL_NPIS.value }}
)
GROUP BY provider_id
),
-- ASCs
asc_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_ASC.value }}
UNION
SELECT
provider_id
, npi
FROM {{ cld_params.Tables.SPINES_PROVIDER_ASC_ADDITIONAL_NPIS.value }}
)
group by provider_id
),
-- IMAGING
imaging_center_array_npi as (
select
provider_id,
ARRAY_SORT(ARRAY_AGG(DISTINCT cast(npi as varchar))) as npi
from {{ cld_params.Tables.SPINES_PROVIDER_IMAGING_CENTERS.value }} -- there are no additional NPIs for imaging centers at this point
group by provider_id
),
-- LABORATORIES
labs_filtered AS (
SELECT
hco_1_npi,
count(distinct encounter_key) as n
FROM tq_intermediate.external_komodo.medical_headers
WHERE hco_1_npi IN (
SELECT npi
FROM {{ cld_params.Tables.PROVIDER_DEMOGRAPHICS_TABLE.value }}
WHERE taxonomy_code IN ('291U00000X', '293D00000X')
)
GROUP BY 1
HAVING count(distinct encounter_key) > 1000
ORDER BY n DESC
LIMIT 1000
),
pd_lab_clean AS (
SELECT
npi,
ein,
CASE
WHEN clean_org_name LIKE 'Quest Diag%' THEN 'Quest Diagnostics'
WHEN
clean_org_name LIKE 'Laboratory Corp%'
OR clean_org_name LIKE 'Labcorp%'
THEN 'LabCorp'
ELSE clean_org_name
END as provider_name,
city,
state,
address,
latitude,
longitude,
cbsa,
cbsa_description as cbsa_name,
county,
taxonomy_code,
taxonomy_grouping
FROM {{ cld_params.Tables.PROVIDER_DEMOGRAPHICS_TABLE.value }}
WHERE taxonomy_grouping = 'Laboratories'
AND taxonomy_code IN (
'291U00000X', -- Clinical Medical Laboratory
'293D00000X' -- Physiological Laboratory
)
AND provider_type IN (
'Clinical Medical Laboratory',
'Physician Group Affiliated Entity',
'Physiological Laboratory',
'Hospital Affiliated Entity',
'Single/Multi-Specialty Physician Group'
)
AND npi_type = '2'
AND (
npi IN (
SELECT hco_1_npi
FROM labs_filtered
)
OR
clean_org_name LIKE 'Quest Diag%'
OR
clean_org_name LIKE 'Laboratory Corp%'
OR
clean_org_name LIKE 'Labcorp%'
)
),
pd_lab_agg AS (
SELECT
CAST(from_big_endian_64(xxhash64(CAST(
provider_name ||
state
AS varbinary
))) AS VARCHAR) as provider_id,
CASE
WHEN provider_name = 'Quest Diagnostics' THEN provider_name || ' ' || state
WHEN provider_name = 'LabCorp' THEN provider_name || ' ' || state
ELSE provider_name
END as provider_name,
ARRAY_DISTINCT(ARRAY_AGG(npi)) as npi,
ARRAY_DISTINCT(ARRAY_AGG(ein)) as ein,
ANY_VALUE(state) as state,
ANY_VALUE(taxonomy_code) as taxonomy_code,
ANY_VALUE(taxonomy_grouping) as taxonomy_grouping,
ANY_VALUE(city) as city,
ANY_VALUE(cbsa) as cbsa,
ANY_VALUE(cbsa_name) as cbsa_name,
ANY_VALUE(county) as county,
ANY_VALUE(address) as address,
ANY_VALUE(latitude) as latitude,
ANY_VALUE(longitude) as longitude
FROM pd_lab_clean
GROUP BY provider_name, state
),
------------------------------------
-- FINAL PROVIDER CTEs
------------------------------------
hospitals as (
SELECT
a.provider_id,
CASE
WHEN npis.npi[1] IS NULL THEN NULL
ELSE npis.npi
END as npi,
CAST(a.dh_hospital_id AS INTEGER) as definitive_id,
a.provider_name as provider_name,
a.ccn as medicare_provider_id,
CASE WHEN a.tin IS NOT NULL THEN ARRAY[a.tin] ELSE ARRAY[] END as ein,
a.provider_city as city,
a.provider_state as state,
a.provider_cbsa_code as cbsa,
geo_cbsa.cbsa_name,
a.provider_county as county,
a.provider_zip5 as zip_code,
SUBSTRING(a.provider_zip5, 1, 3) as zip3,
z.carrier_locality,
a.provider_address as street_address,
a.provider_latitude as hq_latitude,
a.provider_longitude as hq_longitude,
a.provider_website as website_url,
a.provider_healthsystem_id as health_system_id,
TRIM(hs.provider_name) as health_system_name,
'Health System' as health_system_type,
hs.provider_city as health_system_city,
hs.provider_state as health_system_state,
a.provider_subtype as provider_type,
'282N00000X' as taxonomy_code,
'Hospitals' as taxonomy_grouping,
a.total_licensed_beds 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
FROM {{ cld_params.Tables.SPINES_PROVIDER_HOSPITAL.value }} a
LEFT JOIN {{ cld_params.Tables.SPINES_PROVIDER_HEALTH_SYSTEMS.value }} hs
ON a.provider_healthsystem_id = hs.provider_id
LEFT JOIN hospital_array_npi as npis
ON a.provider_id = npis.provider_id
LEFT JOIN geo_cbsa
ON a.provider_cbsa_code = geo_cbsa.cbsa_id
LEFT JOIN zip_carrier_locality z
ON a.provider_zip5 = z.zip_code
WHERE
a.provider_active_status = 'Active'
AND a.provider_active_status_rating IN ('Likely', 'Highly Likely') -- limit to active hospitals only
),
ascs as (
select distinct
cast(a.provider_id as varchar) as provider_id,
CASE
WHEN npis.npi[1] IS NULL THEN NULL
ELSE npis.npi
END as npi,
a.dh_hospital_id as definitive_id,
a.provider_name,
ccn as medicare_provider_id,
ARRAY[] as ein,
a.provider_city as city,
a.provider_state as state,
a.provider_cbsa_code as cbsa,
geo_cbsa.cbsa_name,
coalesce(geo_flat.county_name, a.provider_county) as county,
a.provider_zip5 as zip_code,
substring(a.provider_zip5, 1, 3) as zip3,
z.carrier_locality,
a.provider_address as street_address,
a.provider_latitude as hq_latitude,
a.provider_longitude as hq_longitude,
a.provider_website as website_url,
NULL as health_system_id,
TRIM(hs.provider_name) as health_system_name,
hs.provider_type as health_system_type,
hs.provider_city as health_system_city,
hs.provider_state as health_system_state,
a.provider_type,
primary_taxonomy_code as taxonomy_code,
nucc.taxonomy_grouping as taxonomy_grouping,
NULL as total_beds,
operating_room_count,
services_lab,
services_radiology,
services_cardiology,
services_dental,
services_dermatology,
services_ent,
services_general_other,
services_endoscopy,
services_nephrology,
services_neurology,
services_obgyn,
services_opthalmology,
services_orthopedic,
services_pain,
services_plastic_surgery,
services_podiatry,
services_pulmonary,
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
FROM {{ cld_params.Tables.SPINES_PROVIDER_ASC.value }} as a
LEFT JOIN {{ cld_params.Tables.SPINES_PROVIDER_HEALTH_SYSTEMS.value }} as hs
ON a.provider_healthsystem_id = hs.provider_id
LEFT JOIN asc_array_npi as npis
ON a.provider_id = npis.provider_id
LEFT JOIN zip_carrier_locality as z
ON a.provider_zip5 = z.zip_code
LEFT JOIN nucc
ON a.primary_taxonomy_code = nucc.taxonomy_code
LEFT JOIN geo_cbsa
ON a.provider_cbsa_code = geo_cbsa.cbsa_id
LEFT JOIN geo_flat
ON upper(a.provider_city) = upper(geo_flat.city_name)
and a.provider_state = geo_flat.state_abbreviation
where
a.provider_type_rating in ('Likely', 'Highly Likely') -- remove rows where provider is only possibly an ASC
and a.provider_active_status = 'Active' -- limit to active ASCs only
and a.provider_active_status_rating in ('Likely', 'Highly Likely')
),
imaging_centers as (
select distinct
cast(a.provider_id as varchar) as provider_id,
CASE
WHEN npis.npi[1] IS NULL THEN NULL
ELSE npis.npi
END as npi,
a.dh_hospital_id as definitive_id,
a.provider_name,
'NULL' as medicare_provider_id,
ARRAY[] as ein,
a.provider_city as city,
a.provider_state as state,
a.provider_cbsa_code as cbsa,
geo_cbsa.cbsa_name,
coalesce(geo_flat.county_name, a.provider_county) as county,
a.provider_zip5 as zip_code,
substring(a.provider_zip5, 1, 3) as zip3,
z.carrier_locality,
a.provider_address as street_address,
a.provider_latitude as hq_latitude,
a.provider_longitude as hq_longitude,
a.provider_website as website_url,
NULL as health_system_id,
TRIM(hs.provider_name) as health_system_name,
hs.provider_type as health_system_type,
hs.provider_city as health_system_city,
hs.provider_state as health_system_state,
a.provider_type,
a.primary_taxonomy_code as taxonomy_code,
nucc.taxonomy_grouping 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,
services_mra,
services_ct,
services_mri,
services_mammography,
services_cta,
services_xray,
services_ultrasound,
services_spect,
services_pet,
services_imrt
FROM {{ cld_params.Tables.SPINES_PROVIDER_IMAGING_CENTERS.value }} as a
LEFT JOIN {{ cld_params.Tables.SPINES_PROVIDER_HEALTH_SYSTEMS.value }} as hs
ON a.provider_healthsystem_id = hs.provider_id
LEFT JOIN imaging_center_array_npi as npis on
a.provider_id = npis.provider_id
LEFT JOIN zip_carrier_locality as z on
a.provider_zip5 = z.zip_code
LEFT JOIN nucc on
a.primary_taxonomy_code = nucc.taxonomy_code
LEFT JOIN geo_cbsa on
a.provider_cbsa_code = geo_cbsa.cbsa_id
LEFT JOIN geo_flat on
upper(a.provider_city) = upper(geo_flat.city_name)
and a.provider_state = geo_flat.state_abbreviation
where
a.provider_type_rating in ('Likely', 'Highly Likely') -- remove rows where provider is only possibly an Imaging Center
and a.provider_active_status = 'Active' -- limit to active Imaging Centers only, allow any status rating due to reduced data sources
),
physician_groups AS (
SELECT
provider_id,
ANY_VALUE(npi) as npi,
NULL as definitive_id,
ANY_VALUE(tq_clean_group_name) as provider_name,
NULL as medicare_provider_id,
ARRAY_AGG(tin) FILTER (WHERE tin IS NOT NULL) AS ein,
NULL as city,
ANY_VALUE(state) as state,
NULL as cbsa,
NULL as cbsa_name,
NULL as county,
NULL as zip_code,
NULL as zip3,
NULL as carrier_locality,
NULL as street_address,
NULL as hq_latitude,
NULL as hq_longitude,
NULL as website_url,
ANY_VALUE(SPLIT_PART(health_system_id, '_', 2)) as health_system_id,
ANY_VALUE(TRIM(health_system_name)) as health_system_name,
NULL as health_system_type,
NULL as health_system_city,
NULL as health_system_state,
'Physician Group' as provider_type,
NULL as taxonomy_code,
'Physician Group' 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
FROM (
SELECT
provider_id,
CASE
WHEN npi[1] IS NULL THEN NULL
ELSE npi
END as npi,
tin,
COALESCE(tq_clean_group_name, pecos_name) as tq_clean_group_name,
state,
health_system_id,
health_system_name
FROM {{ cld_params.Tables.CLD_PHYSICIAN_GROUP_IDS.value }}
UNION ALL
SELECT
provider_id,
CASE
WHEN npi[1] IS NULL THEN NULL
ELSE npi
END as npi,
tin,
COALESCE(tq_clean_group_name, pecos_name) as tq_clean_group_name,
state,
health_system_id,
health_system_name
FROM {{ cld_params.Tables.CLD_NON_AFFILIATED_PHYSICIAN_GROUP_IDS.value }}
) pg
GROUP BY
provider_id
),
laboratories AS (
SELECT
provider_id,
CASE
WHEN npi[1] IS NULL THEN NULL
ELSE npi
END as npi,
NULL as definitive_id,
provider_name,
NULL as medicare_provider_id,
ein,
city,
state,
cbsa,
cbsa_name,
county,
NULL as zip_code,
NULL as zip3,
NULL as carrier_locality,
address as street_address,
latitude as hq_latitude,
longitude 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,
'Laboratory' as provider_type,
taxonomy_code,
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
FROM pd_lab_agg
),
-- COMBINE
df AS (
select * from hospitals
union
select * from ascs
union
select * from imaging_centers
union
select * from physician_groups
union
select * from laboratories
)
SELECT *
FROM df
WHERE
provider_type IN (
{{ provider_types}}
)
AND
state IN (
{{ US_STATES }}
)
Payer​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_ref_payer_spine_{{ sub_version }}
-- Subversion: {{ sub_version }}
{% set payer_ids = [
'169',
'42',
'49',
'389',
'403',
'229',
'101',
'76',
'643',
'7',
'388',
'44',
'61',
'628',
'160',
'52',
'392',
'398',
'50',
'391',
'43',
'454',
'461',
'456',
'720',
'636',
'53',
'47',
'56',
'168',
'299',
'317',
'300',
'286',
'723',
'354',
'390',
'51',
'397',
'458',
'799',
'111',
'166',
'770',
'174',
'383',
'455',
'165',
'54',
'462',
'522',
'541',
'151',
'772',
'37',
'633',
'774',
'813',
'504',
'849',
'121',
'272',
'552',
'277',
'791',
'768',
'567',
'624',
'779',
'305',
'728',
'784',
'699',
'356',
'27',
'971',
'97',
'108',
'638',
'958'
] %}
CREATE TABLE {{ schema_name }}.tmp_ref_payer_spine_{{ sub_version }} AS
WITH
payer_ids AS (
{% for id in payer_ids %}
SELECT {{ id }} AS payer_payer_id {% if not loop.last %} UNION ALL {% endif %}
{% endfor %}
),
payers AS (
SELECT
CAST(payer_payer_id as VARCHAR) as payer_id,
CASE
WHEN payer_payer_id = 169 THEN ARRAY[575676]
WHEN payer_payer_id = 42 THEN ARRAY[575957,585334,575927,575914,579862,575931,553230,575890,575915,579863,552470]
WHEN payer_payer_id = 49 THEN ARRAY[552472]
WHEN payer_payer_id = 389 THEN ARRAY[552471]
WHEN payer_payer_id = 403 THEN ARRAY[552477]
WHEN payer_payer_id = 229 THEN ARRAY[553215]
WHEN payer_payer_id = 76 THEN ARRAY[552436]
WHEN payer_payer_id = 643 THEN ARRAY[583151]
WHEN payer_payer_id = 7 THEN ARRAY[552393]
WHEN payer_payer_id = 388 THEN ARRAY[552479]
WHEN payer_payer_id = 44 THEN ARRAY[575917]
WHEN payer_payer_id = 101 THEN NULL
WHEN payer_payer_id = 61 THEN ARRAY[582983]
WHEN payer_payer_id = 628 THEN ARRAY[575907]
WHEN payer_payer_id = 160 THEN ARRAY[575911]
WHEN payer_payer_id = 52 THEN ARRAY[552474]
WHEN payer_payer_id = 392 THEN ARRAY[575753]
WHEN payer_payer_id = 398 THEN ARRAY[553244]
WHEN payer_payer_id = 50 THEN ARRAY[575929]
WHEN payer_payer_id = 391 THEN ARRAY[575928]
WHEN payer_payer_id = 43 THEN ARRAY[575912]
WHEN payer_payer_id = 454 THEN ARRAY[575913]
WHEN payer_payer_id = 461 THEN ARRAY[575922]
WHEN payer_payer_id = 456 THEN ARRAY[575930]
WHEN payer_payer_id = 720 THEN ARRAY[582860]
WHEN payer_payer_id = 636 THEN NULL
WHEN payer_payer_id = 53 THEN ARRAY[575940]
WHEN payer_payer_id = 47 THEN ARRAY[575932]
WHEN payer_payer_id = 56 THEN ARRAY[582906]
WHEN payer_payer_id = 168 THEN ARRAY[575946]
WHEN payer_payer_id = 299 THEN ARRAY[582976]
WHEN payer_payer_id = 317 THEN ARRAY[577321]
WHEN payer_payer_id = 300 THEN ARRAY[577702]
WHEN payer_payer_id = 286 THEN ARRAY[577590]
WHEN payer_payer_id = 723 THEN ARRAY[846735]
WHEN payer_payer_id = 354 THEN ARRAY[583152]
WHEN payer_payer_id = 390 THEN ARRAY[575932]
WHEN payer_payer_id = 51 THEN ARRAY[575934]
WHEN payer_payer_id = 397 THEN ARRAY[575945]
WHEN payer_payer_id = 458 THEN ARRAY[575939]
WHEN payer_payer_id = 799 THEN ARRAY[941897]
WHEN payer_payer_id = 111 THEN ARRAY[583123]
WHEN payer_payer_id = 166 THEN ARRAY[575936]
WHEN payer_payer_id = 770 THEN ARRAY[575949]
WHEN payer_payer_id = 174 THEN ARRAY[552480, 575948, 575916]
WHEN payer_payer_id = 383 THEN ARRAY[582983]
WHEN payer_payer_id = 455 THEN ARRAY[575919]
WHEN payer_payer_id = 165 THEN ARRAY[575933]
WHEN payer_payer_id = 54 THEN ARRAY[552385]
WHEN payer_payer_id = 462 THEN ARRAY[582779]
WHEN payer_payer_id = 522 THEN ARRAY[583130, 583124]
WHEN payer_payer_id = 541 THEN ARRAY[552525, 577707]
WHEN payer_payer_id = 151 THEN ARRAY[1024706]
WHEN payer_payer_id = 772 THEN ARRAY[577506]
WHEN payer_payer_id = 37 THEN NULL
WHEN payer_payer_id = 633 THEN ARRAY[577345]
WHEN payer_payer_id = 774 THEN ARRAY[582789]
WHEN payer_payer_id = 813 THEN NULL
WHEN payer_payer_id = 504 THEN ARRAY[780737, 577734, 577605, 860772, 582930, 582862, 582866]
WHEN payer_payer_id = 849 THEN ARRAY[583075]
WHEN payer_payer_id = 121 THEN ARRAY[576034]
WHEN payer_payer_id = 272 THEN ARRAY[582938]
WHEN payer_payer_id = 552 THEN ARRAY[575335]
WHEN payer_payer_id = 277 THEN ARRAY[846724, 575906, 846727, 846725, 967752, 995224, 967734, 846726, 931629, 846664, 775668, 995225, 846658, 846656, 846728]
WHEN payer_payer_id = 791 THEN NULL
WHEN payer_payer_id = 768 THEN NULL
WHEN payer_payer_id = 567 THEN NULL
WHEN payer_payer_id = 624 THEN ARRAY[575393]
WHEN payer_payer_id = 779 THEN ARRAY[583139]
WHEN payer_payer_id = 305 THEN ARRAY[581691]
WHEN payer_payer_id = 728 THEN NULL
WHEN payer_payer_id = 784 THEN NULL
WHEN payer_payer_id = 699 THEN NULL
WHEN payer_payer_id = 356 THEN NULL
WHEN payer_payer_id = 27 THEN ARRAY[577743]
WHEN payer_payer_id = 971 THEN ARRAY[780683, 1003874, 576035, 999213, 951069, 577746]
WHEN payer_payer_id = 97 THEN ARRAY[575897, 582850, 583128]
WHEN payer_payer_id = 108 THEN ARRAY[967760, 577755, 577688]
WHEN payer_payer_id = 638 THEN ARRAY[583148]
ELSE NULL
END as definitive_payer_id
FROM payer_ids
)
SELECT
p.payer_id,
sp.payer_name,
p.definitive_payer_id
FROM payers p
JOIN {{ cld_params.Tables.SPINES_PAYER.value }} sp
ON p.payer_id = sp.payer_id
Network​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_ref_network_spine_{{ sub_version }}
-- Subversion: {{ sub_version }}
-- network_mappings: (payer_id, product_network_label, network_name (in CLD), states)
-- product_network_label and states support multiple values, separated by commas.
{% set ppo_network_mappings = [
('76', "'NATIONAL OAP', 'NATIONAL PPO'", 'National OAP', "'NATIONAL'"),
('7', "'OPEN ACCESS MANAGED CHOICE'", 'Open Access Managed Choice', "'NATIONAL'"),
('643', "'CHOICE PLUS'", 'Choice Plus', "'NATIONAL'"),
('42', "'NY PPO'", 'NY PPO', "'NY'"),
('42', "'CA BLUE CROSS PPO'", 'CA Blue Cross PPO', "'CA'"),
('42', "'OH PPO'", 'OH PPO', "'OH'"),
('42', "'CO BLUE PREFERRED PPO'", 'CO Blue Preferred PPO', "'CO'"),
('42', "'MO BLUE ACCESS PPO'", 'MO Blue Access PPO', "'MO'"),
('42', "'IN BLUE ACCESS PPO'", 'IN Blue Access PPO', "'IN'"),
('42', "'GA BLUE OPEN ACCESS POS', 'BLUECARD - OPEN ACCESS POS'", 'GA OPEN ACCESS', "'GA'"),
('42', "'NH HMO', 'BLUECARD - BLUE CHOICE'", 'NH OPEN ACCESS', "'NH'"),
('42', "'KY PPO'", 'KY PPO', "'KY'"),
('42', "'VA KEYCARE PPO'", 'VA Keycare PPO', "'VA'"),
('42', "'NV CHOICE PPO'", 'NV Choice PPO', "'NV'"),
('42', "'WI BLUE ACCESS PPO'", 'WI Blue Access PPO', "'WI'"),
('42', "'ME BLUE CHOICE PPO'", 'ME Blue Choice PPO', "'ME'"),
('42', "'CT CENTURY PREFERRED PPO'", 'CT Century Preferred PPO', "'CT'"),
('169', "'BLUE CHOICE PPO'", 'Blue Choice PPO', "'TX'"),
('403', "'GROUP PPO'", 'Group PPO', "'CA'"),
('389', "'PPO PARTICIPATING PROVIDER OPTIONS'", 'PPO Participating Provider Options', "'IL'"),
('49', "'PPO'", 'PPO', "'MI'"),
('229', "'GROUP - PPO'", 'PPO', "'NJ'"),
('101', "'EXCELLUS BLUECROSS BLUESHIELD'", 'Excellus Bluecross Blueshield', "'NY'"),
('44', "'PREFERRED PROVIDER NETWORK (PPO)'", 'Preferred Provider Network (PPO)', "'MD', 'DC', 'VA'"),
('388', "'BLUECHOICE PPO'", 'BlueChoice PPO', "'FL'"),
('61', "'PREFERRED IDAHO PCP/SPEC (SZ)', 'PREFERRED IDAHO (GRANDFATHERED GROUPS ONLY)'", 'ID PPO', "'ID'"),
('61', "'PREFERRED OREGON 2 TIER PCP/SPEC (SZ)', 'PREFERRED OREGON (LZ)'", 'OR PPO', "'OR'"),
('61', "'PREFERRED UTAH BLUEOPTION PCP/SPEC (SZ)', 'PREFERRED UTAH BLUE OPTION (LZ)'", 'UT PPO', "'UT'"),
('61', "'PREFERRED WASHINGTON PCP/SPEC (SZ)', 'PREFERRED WASHINGTON (LZ)'", 'WA PPO', "'WA'"),
('628', "'PBC PPO (HERITAGE) NETWORK'", 'PBC PPO (Heritage) Network', "'WA'"),
('628', "'ALASKA HERITAGE NETWORK'", 'Alaska Heritage Network', "'WA'"),
('160', "'PREFERRED PPO'", 'Preferred PPO', "'AL'"),
('52', "'PREFERRED PROVIDER NETWORK'", 'Preferred Provider Network', "'NC'"),
('392', "'BLUECARD - PREFERRED'", 'PPO', "'MA'"),
('398', "'BLUECARD - PREFERRED'", 'Preferred', "'TN'"),
('50', "'AWARE PPO'", 'Aware PPO', "'MN'"),
('391', "'PPO - PREFERRED CARE PPO'", 'PPO - Preferred Care PPO', "'LA'"),
('43', "'BLUECARD - PREFERRED'", 'PPO', "'AZ'"),
('454', "'ABCBS HA IN NETWORK: TRUE BLUE PPO'", 'True Blue PPO', "'AR'"),
('461', "'WELLMARK HEALTH PLAN IOWA PLAN HMO'", 'IA HMO', "'IA'"),
('461', "'WELLMARK SOUTH DAKOTA BLUE SELECT PPO PLAN'", 'SD PPO', "'SD'"),
('456', "'PREFERRED PROVIDER NETWORK'", 'Preferred Provider Network', "'MS'"),
('720', "'CHOICENET PPO'", 'Choicenet PPO', "'MA'"),
('636', "'VALUE/CARE TIERED POS - UT'", 'Value/Care Tiered POS - UT', "'UT'"),
('53', "'BLUE PREFERRED PPO'", 'Blue Preferred PPO', "'OK'"),
('47', "'PREFERREDCARE BLUE'", 'Preferredcare Blue', "'MO'"),
('56', "'QCC COMMERCIAL PPO/EPO'", 'QCC Commercial PPO/EPO', "'PA'"),
('56', "'KHPE COMMERCIAL HMO/POS'", 'KHPE Commercial HMO/POS', "'PA'"),
('168', "'PPO - PREFERRED BLUE'", 'PPO - Preferred Blue', "'SC'"),
('299', "'PPO'", 'PPO', "'MI'"),
('317', "'PPO MA'", 'PPO MA', "'MA'"),
('300', "'PEBB CHOICE'", 'PEBB Choice', "'OR'"),
('286', "'MVP EPO / PPO'", 'MVP EPO / PPO', "'NY'"),
('723', "'ESSENTIAL PLAN'", 'Essential Plan', "'NY'"),
('354', "'PREMIUM'", 'Premium', "'PA'"),
('390', "'BLUE CHOICE'", 'Blue Choice', "'KS'"),
('51', "'NETWORKBLUE PPO INDIVIDUAL', 'NETWORK BLUE'", 'Networkblue PPO', "'NE'"),
('397', "'PPO1'", 'PPO', "'RI'"),
('458', "'BLUECARD - PREFERRED'", 'PPO', "'ND'"),
('799', "'QUARTZ TIERED CHOICE PLUS (QHBPC WI): QUARTZ'", 'Quartz Tiered Choice Plus (QHBPC WI): Quartz', "'WI'"),
('111', "'PPO'", 'PPO', "'PA'"),
('166', "'PREFERRED PROVIDER ORGANIZATION PPO'", 'Preferred Provider Organization PPO', "'NM'"),
('770', "'COMMERCIAL PPO'", 'Commercial PPO', "'MA'"),
('174', "'BLUECARD - PREFERRED'", 'PPO', "'PA', 'DE', 'NY', 'WV'"),
('383', "'TRADITIONAL & PREFERRED PROVIDER PPO'", 'PPO', "'ID'"),
('455', "'PPO'", 'PPO', "'HI'"),
('165', "'BLUE PREFERRED PPO'", 'Blue Preferred PPO', "'MT'"),
('54', "'PPO'", 'PPO', "'PA'"),
('462', "'PPO QHP EPO (ZIG; ZII)'", 'PPO', "'VT'"),
(
'522',
"'KFHP COLORADO', 'KPIC COLORADO', 'SELF FUNDED COLORADO'",
'Kaiser Health Plan (CO)',
"'CO'"
),
(
'522',
"'KFHP GEORGIA', 'KPIC GEORGIA', 'SELF FUNDED GEORGIA'",
'Kaiser Health Plan (GA)',
"'GA'"
),
(
'522',
"'KFHP HAWAII', 'KPIC HAWAII'",
'Kaiser Health Plan (HI)',
"'HI'"
),
(
'522',
"'KFHP NORTHERN CALIFORNIA', 'KFHP SOUTHERN CALIFORNIA', 'KPIC NORTHERN CALIFORNIA', 'KPIC SOUTHERN CALIFORNIA'",
'Kaiser Health Plan (CA)',
"'CA'"
),
(
'522',
"'KPIC MID ATLANTIC', 'SELF FUNDED MID ATLANTIC'",
'Kaiser Health Plan Mid Atlantic',
"'DC', 'MD', 'VA'"
),
(
'522',
"'SELF FUNDED NORTHWEST'",
'Kaiser Health Plan Northwest',
"'OR', 'WA'"
),
('772', "'REALVALUE ASURIS (LZ)'", 'RealValue', "'WA'"),
('37', "'AVERA HEALTH NETWORK ASO NETWORK'", 'Avera Health', "'SD'"),
('633', "'BSW PLUS PPO'", 'PPO', "'TX'"),
('774', "'PPO/EPO'", 'PPO', "'NY'"),
('813', "'EPO/PPO'", 'PPO', "'MI'"),
('849', "'COMMERCIAL FULLY FUNDED (GROUP AND INDIVIDUAL)'", 'PPO', "'MA'"),
('272', "'SUPERMED PLUS'", 'SuperMed', "'OH'"),
('552', "'CONNEXUS'", 'Connexus', "'OR'"),
('791', "'MOTIVNET'", 'MotivNet', "'UT'"),
('768', "'ALL PRODUCTS'", 'PPO', "'WI'"),
('624', "'NAVIGATOR GOLD'", 'Navigator Gold', "'OR', 'MT', 'ID', 'WA'"),
('779', "'PREFERREDONE PPO'", 'PPO', "'MN'"),
('305', "'BROAD NETWORK'", 'PPO', "'SD', 'ND', 'MN'"),
('728', "'SUTTER HEALTH PLUS LARGE GROUP PLANS'", 'PPO', "'CA'"),
('784', "'ALL PRODUCTS'", 'PPO', "'HI'"),
('699', "'UNIVERA PPO SIGNATURE DEDUCTIBLE'", 'PPO', "'NY'"),
('356', "'ALLIANCE WPS STATEWIDE FIRST HEALTH WPS HEALTH INSURANCE'", 'PPO', "'WI'")
] %}
{% set hmo_network_mappings = [
(
7,
"'FL GROUP HMO', 'FL INDIVIDUAL HMO'",
'FL HMO',
"'FL'"
),
(
7,
"'NC INDIVIDUAL HMO'",
'NC HMO',
"'NC'"
),
(
7,
"'DE INDIVIDUAL HMO'",
'DE HMO',
"'DE'"
),
(
7,
"'MD GROUP HMO', 'MD GROUP HMO/POS', 'DC GROUP HMO'",
'MD HMO',
"'MD','DC'"
),
(
7,
"'VA INDIVIDUAL HMO', 'DC GROUP HMO'",
'VA HMO',
"'VA', 'DC'"
),
(
7,
"'TX INDIVIDUAL HMO'",
'TX HMO',
"'TX'"
),
(
7,
"'NV INDIVIDUAL HMO', 'NV GROUP HMO'",
'NV HMO',
"'NV'"
),
(
7,
"'IL INDIVIDUAL HMO'",
'IL HMO',
"'IL'"
),
(
7,
"'CA INDIVIDUAL HMO', 'CA GROUP HMO'",
'CA HMO',
"'CA'"
),
(
7,
"'PA GROUP HMO/POS'",
'PA HMO',
"'PA'"
),
(
7,
"'GA INDIVIDUAL HMO'",
'GA HMO',
"'GA'"
),
(
42,
"'KY HMO', 'KY PATHWAY X HMO'",
'KY HMO',
"'KY'"
),
(
42,
"'CT HMO'",
'CT HMO',
"'CT'"
),
(
42,
"'NH HMO'",
'NH HMO',
"'NH'"
),
(
42,
"'ME HMO'",
'ME HMO',
"'ME'"
),
(
42,
"'VA HMO INDIVIDUAL POS'",
'VA HMO',
"'VA'"
),
(
42,
"'IN PATHWAY HMO/POS', 'IN PATHWAY X HMO'",
'IN HMO',
"'IN'"
),
(
42,
"'GA BLUE VALUE IND NETWORK HMO'",
'GA HMO',
"'GA'"
),
(
42,
"'CA HMO', 'CA PATHWAY HMO'",
'CA HMO',
"'CA'"
),
(
42,
"'NV HMO'",
'NV HMO',
"'NV'"
),
(
43,
"'INDIVIDUAL AND FAMILY HMO - MARICOPA', 'SMALL GROUP HMO - ALLIANCE', 'SMALL GROUP HMO', 'INDIVIDUAL AND FAMILY HMO - PIMA', 'INDIVIDUAL AND FAMILY HMO', 'INDIVIDUAL AND FAMILY HMO - NEIGHBORHOOD'",
'HMO',
"'AZ'"
),
(
44,
"'BLUECHOICE (HMO),'",
'HMO',
"'MD', 'DC', 'VA'"
),
(
49,
"'HMO', 'NORTHWOOD DME - HMO', 'METRO DETROIT - HMO', 'SELECT - HMO', 'VIRTUAL OPTION - HMO', 'CARECENTRIX HOME INFUSION - HMO / PPO', 'LOCAL - HMO'",
'HMO',
"'MI'"
),
(
50,
"'BLUE PLUS METRO MN W ALLINA', 'BLUE PLUS SE MN', 'BLUE PLUS MN VALUE'",
'HMO',
"'MN'"
),
(
52,
"'DUKE EMPLOYEE HMO'",
'HMO',
"'NC'"
),
(
53,
"'BLUELINCS HMO', 'BLUELINCS HMO GROUP', 'BLUELINCS HMO COMMON'",
'HMO',
"'OK'"
),
(
76,
"'MAINE HMO'",
'ME HMO',
"'ME'"
),
(
76,
"'SOUTH TEXAS HMO', 'NORTH TEXAS HMO'",
'TX HMO',
"'TX'"
),
(
76,
"'SAN FRANCISCO SACRAMENTO HMO', 'SOUTHERN CA HMO'",
'CA HMO',
"'CA'"
),
(
76,
"'TENNESSEE HMO/POS'",
'TN HMO',
"'TN'"
),
(
76,
"'SOUTHWEST MISSOURI HMO'",
'MO HMO',
"'MO'"
),
(
76,
"'METRO NEW YORK HMO'",
'NY HMO',
"'NY'"
),
(
76,
"'NEW ENGLAND HMO/POS'",
'New England HMO',
"'MA', 'CT', 'RI', 'NH', 'VT', 'ME'"
),
(
76,
"'NEW HAMPSHIRE HMO'",
'NH HMO',
"'NH'"
),
(
76,
"'NORTH NEW JERSEY HMO', 'SOUTH NEW JERSEY HMO'",
'NJ HMO',
"'NJ'"
),
(
76,
"'GEORGIA HMO'",
'GA HMO',
"'GA'"
),
(
76,
"'SOUTH FLORIDA HMO', 'FLORIDA HMO'",
'FL HMO',
"'FL'"
),
(
76,
"'ARIZONA HMO'",
'AZ HMO',
"'AZ'"
),
(
76,
"'ALABAMA HMO'",
'AL HMO',
"'AL'"
),
(
76,
"'RICHMOND AND NORTHERN VA OAP CONNECT'",
'VA HMO',
"'VA'"
),
(
76,
"'NORTH CAROLINA HMO'",
'NC HMO',
"'NC'"
),
(
111,
"'PPO/HMO', 'HMO/POS'",
'HMO',
"'PA'"
),
(
166,
"'BLUE ADVANTAGE HMO', 'HEALTH MAINTENANCE ORGANIZATION HMO', 'BLUE COMMUNITY HMO NETWORK'",
'HMO',
"'NM'"
),
(
168,
"'HMO - PRIMARY CHOICE'",
'HMO',
"'SC'"
),
(
169,
"'BLUE ADVANTAGE HMO', 'MYBLUE HEALTH HMO'",
'HMO',
"'TX'"
),
(
229,
"'GROUP - OMNIA'",
'OMNIA',
"'NJ'"
),
(
286,
"'MVP HMO / POS'",
'HMO',
"'NY'"
),
(
299,
"'HMO'",
'HMO',
"'MI'"
),
(
317,
"'YOUR CHOICE HMO MA', 'SELECT HMO MA', 'HMO MA'",
'HMO',
"'MA'"
),
(
388,
"'SIMPLYBLUE HMO', 'BLUECARE HMO', 'MYBLUE HMO'",
'HMO',
"'FL'"
),
(
389,
"'BLUE PRECISION HMO', 'BLUE ADVANTAGE HMO - NON STANDARD', 'BLUE ADVANTAGE HMO HMO NON STANDARD', 'BLUE PRECISION HMO - NON STANDARD', 'BLUE ADVANTAGE HMO', 'HMO ILLINOIS STANDARD AND ADVOCATE HEALTH', 'BLUE ADVANTAGE HMO - STANDARD AND ADVOCATE HEALTH', 'HMO ILLINOIS', 'BLUE PRECISION HMO - STANDARD AND ADVOCATE HEALTH', 'BLUE ADVANTAGE HMO STANDARD AND ADVOCATE HEALTH', 'HMO ILLINOIS HMO NON STANDARD', 'HMO ILLINOIS - NON STANDARD', 'HMO ILLINOIS - STANDARD AND ADVOCATE HEALTH'",
'HMO',
"'IL'"
),
(
391,
"'BLUECARD - COMMUNITY BLUE HMO/POS'",
'HMO',
"'LA'"
),
(
392,
"'HMO BLUE SELF INSURED THIRD PARTY', 'HMO BLUE FULLY INSURED'",
'HMO',
"'MA'"
),
(
403,
"'INDIVIDUAL & FAMILY HMO TRIO', 'GROUP HMO TRIO', 'INDIVIDUAL & FAMILY HMO TRIO AI-AN'",
'HMO',
"'CA'"
),
(
454,
"'ABCBS HA IN NETWORK: HEALTH ADVANTAGE HMO'",
'HMO',
"'AR'"
),
(
455,
"'HMO'",
'HMO',
"'HI'"
),
(
461,
"'WELLMARK HEALTH PLAN IOWA PLAN HMO'",
'HMO',
"'IA'"
),
(
628,
"'SHERWOOD HMO'",
'HMO',
"'WA'"
),
(
636,
"'VALUE HMO', 'MED HMO - UT', 'MED HMO', 'VALUE HMO - UT', 'CARE HMO - UT', 'SHARE HMO', 'MED HMO - NV', 'VALUE HMO - NV'",
'HMO',
"'UT', 'NV'"
),
(
643,
"'COMPASS HMO', 'SIERRA HEALTH HMO', 'HMO GATED', 'CORE ESSENTIAL HMO', 'HMO NONGATED'",
'HMO',
"'NATIONAL'"
),
(
720,
"'ELEVATEHEALTH HMO HSA', 'HMO FLEX', 'VIRTUAL CHOICE HMO', 'NH LOCAL CHOICE HMO', 'GROUP INSURANCE COMMISSION HARVARD PILGRIM QUALITY HMO', 'CHOICENET HMO', 'NH LOCAL HMO', 'BEST BUY HMO HSA', 'AMERICAN INDIAN ALASKAN NATIVES HMO', 'MAINES CHOICE PLUS HMO', 'HARVARD PILGRIM HEALTH CARE HOUSE ACCT ELEVATEHEALTH HMO HSA', 'AMERICAN INDIAN NATIVE ALASKAN HMO FLEX', 'FOCUS NETWORK MA HMO', 'HMO LP', 'HMO', 'SIMPLYVIRTUAL HMO', 'ELEVATEHEALTH HMO'",
'HMO',
"'MA'"
),
(
770,
"'ALLIES HMO', 'COMMERCIAL HMO', 'VALUE HMO', 'SELECT HMO'",
'HMO',
"'MA'"
),
(
151,
"'AMBETTER TX', 'AMBETTER GA', 'AMBETTER FL', 'AMBETTER OH', 'AMBETTER MI', 'AMBETTER AR', 'AMBETTER AZ', 'AMBETTER MS', 'AMBETTER WA', 'AMBETTER KS', 'AMBETTER NC', 'AMBETTER TN', 'AMBETTER SC', 'AMBETTER CA', 'AMBETTER NE', 'AMBETTER IL', 'AMBETTER MO', 'AMBETTER IN', 'AMBETTER PA', 'AMBETTER LA', 'AMBETTER AL', 'AMBETTER OK', 'AMBETTER KY', 'AMBETTER NV'",
'HMO',
"'TX', 'GA', 'FL', 'OH', 'MI', 'AR', 'AZ', 'MS', 'WA', 'KS', 'NC', 'TN', 'SC', 'CA', 'NE', 'IL', 'MO', 'IN', 'PA', 'LA', 'AL', 'OK', 'KY', 'NV'"
),
(
774,
"'HMO / HEALTHY NY / ESSENTIAL'",
'HMO',
"'NY'"
),
(
813,
"'HMO - HAP'",
'HMO',
"'MI'"
)
] %}
{% set narrow_network_mappings = [
(76, "'LOCALPLUS'", 'LocalPlus', "'NATIONAL'"),
(643, "'NAVIGATE'", 'Navigate', "'NATIONAL'"),
(643, "'NEXUS ACO OAP'", 'Nexus', "'NATIONAL'")
] %}
-- CUSTOM NYCDOH
{% set custom_ppo = [
('7', "'NY GROUP EPO'", 'EPO', "'NY'"),
('97', "'DC37 MED-TEAM'", 'DC37 Med-Team', "'NY'"),
('42', "'NY EPO'", 'EPO', "'NY'"),
('42', "'NY EPO'", 'Blue Access Gated EPO', "'NY'"),
('958', "'NYC GOLD / GOLDCARE'", 'Gold', "'NY'"),
] %}
-- CUSTOM NYCDOH
{% set custom_hmo = [
('97', "'GHI HMO'", 'GHI HMO', "'NY'"),
('97', "'HIP HMO'", 'HIP HMO', "'NY'"),
('97', "'HIP HMO'", 'HIP Prime POS', "'NY'"),
('97', "'GHI-CBP', 'NY EPO'", 'GHI / Anthem CBP', "'NY'"),
('42', "'GHI-CBP', 'NY EPO'", 'GHI / Anthem CBP', "'NY'")
] %}
CREATE TABLE {{ schema_name }}.tmp_ref_network_spine_{{ sub_version }} AS
WITH
payer_labels AS (
-- SELECT
-- DISTINCT
-- payer_id,
-- product_network_label
-- FROM {{ payer_schema }}.compressed_idx_file_label
{% for payer_id, product_network_label, network_name, states in ppo_network_mappings %}
SELECT
'{{ payer_id }}' AS payer_id,
'{{ network_name }}' AS network_name,
ARRAY[{{ states }}] AS network_state,
ARRAY[{{ product_network_label }}] AS payer_data_network,
'PPO' AS network_type
{% if not loop.last %} UNION ALL {% endif %}
{% endfor %}
UNION ALL
-- HMO mappings
{% for payer_id, product_network_label, network_name, states in hmo_network_mappings %}
SELECT
'{{ payer_id }}' AS payer_id,
'{{ network_name }}' AS network_name,
ARRAY[{{ states }}] AS network_state,
ARRAY[{{ product_network_label }}] AS payer_data_network,
'HMO' AS network_type
{% if not loop.last %} UNION ALL {% endif %}
{% endfor %}
UNION ALL
-- NARROW mappings
{% for payer_id, product_network_label, network_name, states in narrow_network_mappings %}
SELECT
'{{ payer_id }}' AS payer_id,
'{{ network_name }}' AS network_name,
ARRAY[{{ states }}] AS network_state,
ARRAY[{{ product_network_label }}] AS payer_data_network,
'NARROW' AS network_type
{% if not loop.last %} UNION ALL {% endif %}
{% endfor %}
UNION ALL
-- Custom PPO mappings
{% for payer_id, product_network_label, network_name, states in custom_ppo %}
SELECT
'{{ payer_id }}' AS payer_id,
'{{ network_name }}' AS network_name,
ARRAY[{{ states }}] AS network_state,
ARRAY[{{ product_network_label }}] AS payer_data_network,
'PPO' AS network_type
{% if not loop.last %} UNION ALL {% endif %}
{% endfor %}
UNION ALL
-- Custom HMO mappings
{% for payer_id, product_network_label, network_name, states in custom_hmo %}
SELECT
'{{ payer_id }}' AS payer_id,
'{{ network_name }}' AS network_name,
ARRAY[{{ states }}] AS network_state,
ARRAY[{{ product_network_label }}] AS payer_data_network,
'HMO' AS network_type
{% if not loop.last %} UNION ALL {% endif %}
{% endfor %}
)
SELECT
distinct
from_big_endian_64(xxhash64(CAST(
payer_id || network_name AS varbinary
))) AS network_id,
network_name,
network_state,
payer_id,
payer_data_network,
'Commercial' as network_class,
network_type
FROM payer_labels
Code​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_ref_code_spine_{{ sub_version }}
-- Subversion: {{ sub_version }}
CREATE TABLE {{ schema_name }}.tmp_ref_code_spine_{{ sub_version }} AS
WITH
-- CODES: select all MS-DRG, top 5000 HCPCS by revenue, all J1 codes, selected life sciences codes
opps as (
select distinct
hcpcs,
payment_rate
from {{ cld_params.Tables.OPPS_ADDENDUM_B.value }} opps
where is_latest_start_effective_date = true
),
util as
(
select distinct
util.billing_code,
CASE
WHEN setting = 'Inpatient'
THEN 'Inpatient'
WHEN setting = 'Outpatient'
THEN 'Outpatient'
END AS bill_type,
billing_code_type,
sum(util.total_count_encounters) as total_encounters
from {{ cld_params.Tables.UTILIZATION_NATIONAL.value }} util
where taxonomy_grouping = 'Hospitals'
and claim_type_code = 'institutional'
and taxonomy_classification = 'General Acute Care Hospital'
group by 1, 2, 3
),
allowed_amounts AS (
SELECT
CASE
WHEN setting = 'Inpatient'
THEN 'Inpatient'
WHEN setting = 'Outpatient'
THEN 'Outpatient'
END AS bill_type,
billing_code_type,
billing_code,
AVG(median_allowed_amount) AS allowed_amount
FROM {{ cld_params.Tables.ALLOWABLES_NATIONAL_PAYERCHANNEL.value }}
WHERE payer_channel = 'Commercial'
AND npi_source = 'hco'
AND billing_code_modifier IS NULL
AND claim_type_code IN ('institutional')
AND median_allowed_amount IS NOT NULL
GROUP BY 1, 2, 3
),
revenue_calculated AS (
SELECT
u.bill_type,
u.billing_code_type,
u.billing_code,
u.total_encounters as n_claims,
a.allowed_amount,
u.total_encounters * a.allowed_amount AS revenue
FROM util u
JOIN allowed_amounts a
ON u.billing_code_type = a.billing_code_type
AND u.billing_code = a.billing_code
AND u.bill_type = a.bill_type
WHERE
u.total_encounters * a.allowed_amount > 0
AND u.billing_code_type = 'MS-DRG'
),
dpr_codes AS (
SELECT distinct hcpcs as code
FROM {{ cld_params.Tables.ASP_PRICING_TABLE.value }}
UNION
SELECT distinct hcpcs AS code
FROM {{ cld_params.Tables.TMP_OPPS_MEDICARE_REFERENCE_TABLE.value }}
WHERE status_indicator IN ('G', 'K')
AND rate IS NOT NULL
),
drug_names AS (
SELECT
COALESCE(t.j_code, r.code) AS code,
ARRAY_AGG(DISTINCT COALESCE(t.brand, r.drug_name)) as drug_name_array
FROM (
SELECT *
FROM {{ cld_params.Tables.CMV_INFO.value }}
WHERE j_code != brand
AND brand != 'not applicable'
) t
FULL OUTER JOIN {{ cld_params.Tables.REF_ASP_HCPCS_NDC_CROSSWALK.value }} r
ON t.j_code = r.code
GROUP BY 1
),
life_sciences AS (
SELECT
'Outpatient' AS bill_type,
'HCPCS' AS billing_code_type,
t.code,
NULL AS revenue,
NULL as facility
FROM dpr_codes t
),
lab_util AS (
SELECT DISTINCT encounter_key
{% if sub_version == 'test' or version == 'test' %}
FROM {{ cld_params.Tables.CLAIMS_MEDICAL_HEADERS.value | replace('external_komodo', 'cld_utils') }}
{% else %}
FROM {{ cld_params.Tables.CLAIMS_MEDICAL_HEADERS.value }}
{% endif %}
WHERE hco_1_npi IN (
SELECT npi
FROM {{ cld_params.Tables.PROVIDER_DEMOGRAPHICS_TABLE.value }}
WHERE taxonomy_code IN ('291U00000X', '293D00000X')
AND provider_type IN (
'Clinical Medical Laboratory',
'Physician Group Affiliated Entity',
'Physiological Laboratory',
'Hospital Affiliated Entity',
'Single/Multi-Specialty Physician Group'
)
AND npi_type = '2'
)
),
lab_revenue_calculated AS (
SELECT
'Professional' AS bill_type,
'HCPCS' AS billing_code_type,
procedure_code as billing_code,
clr.rate * count(distinct encounter_key) AS revenue,
False as facility
{% if sub_version == 'test' or version == 'test' %}
FROM {{ cld_params.Tables.CLAIMS_MEDICAL_SERVICE_LINES.value | replace('external_komodo', 'cld_utils') }} AS msl
{% else %}
FROM {{ cld_params.Tables.CLAIMS_MEDICAL_SERVICE_LINES.value }} AS msl
{% endif %}
JOIN (
SELECT hcpcs, max(rate) as rate
FROM {{ cld_params.Tables.CLINICAL_LAB_REFERENCE_PRICING.value }}
WHERE is_latest_start_effective_date = True
AND modifier = ''
GROUP BY 1
) AS clr ON msl.procedure_code = clr.hcpcs
WHERE EXISTS (
SELECT 1
FROM lab_util AS e
WHERE msl.encounter_key = e.encounter_key
)
GROUP BY procedure_code, clr.rate
ORDER BY revenue DESC
LIMIT 750
),
ssp_line_codes AS (
SELECT
DISTINCT
'Outpatient' AS bill_type,
'HCPCS' AS billing_code_type,
s.line_code AS billing_code,
NULL AS revenue,
NULL AS facility
FROM {{ cld_params.Tables.SSP_SUB_PACKAGES.value }} c
JOIN {{ cld_params.Tables.SSP_PACKAGE_CONTENTS.value }} s
ON s.sub_package_id = c.sub_package_id
WHERE s.fee_type IN ('Facility Fee', 'Optional Fee')
AND item_type = 'package_line'
AND c.ssp_id IN (
'DE000','DE003','EN000','EN004','GA000','GA002','GA007','GA010','GA011',
'MS005','MS006','MS008','MS009','MS010','MS019','MS028','NU002','OB001',
'OB002','OP000','PU000','RA000','RA001','RA004','RA005','RA006','RA007',
'RA008','RE003','RE004','RA010','RA011','RA012'
)
AND LENGTH(line_code) = 5
UNION ALL
SELECT
DISTINCT
'Professional' AS bill_type,
'HCPCS' AS billing_code_type,
s.line_code AS billing_code,
NULL AS revenue,
False AS facility
FROM {{ cld_params.Tables.SSP_SUB_PACKAGES.value }} c
JOIN {{ cld_params.Tables.SSP_PACKAGE_CONTENTS.value }} s
ON s.sub_package_id = c.sub_package_id
WHERE s.fee_type IN ('Facility Fee', 'Optional Fee', 'Professional Fee')
AND item_type = 'package_line'
AND c.ssp_id IN (
'DE000','DE003','EN000','EN004','GA000','GA002','GA007','GA010','GA011',
'MS005','MS006','MS008','MS009','MS010','MS019','MS028','NU002','OB001',
'OB002','OP000','PU000','RA000','RA001','RA004','RA005','RA006','RA007',
'RA008','RE003','RE004','RA010','RA011','RA012'
)
AND LENGTH(line_code) = 5
),
all_codes AS (
-- get all MS-DRG codes
SELECT
'Inpatient' as bill_type,
s.service_type AS billing_code_type,
s.service_code AS billing_code,
rc.revenue,
NULL as facility
FROM {{ cld_params.Tables.SPINES_SERVICES.value }} s
LEFT JOIN revenue_calculated rc
ON s.service_code = rc.billing_code
AND rc.billing_code_type = 'MS-DRG'
WHERE s.service_type = 'MS-DRG'
-- exclude Transplant, ECMO, and CAR-T
AND s.service_code NOT IN (
'001', '002', '003', '004', '005', '006', '007', '008', '009', '010',
'011', '012', '013', '014', '015', '016', '017', '018', '019',
'650', '651', '652'
)
UNION ALL
-- get all APR-DRG codes
SELECT
'Inpatient' as bill_type,
s.service_type AS billing_code_type,
s.modified_service_code AS billing_code,
rc.revenue,
NULL as facility
FROM (
-- Add SOI suffixes to APR-DRG codes
SELECT
s.*,
CONCAT(service_code, suffix) AS modified_service_code
FROM {{ cld_params.Tables.SPINES_SERVICES.value }} s
CROSS JOIN (
SELECT '-1' AS suffix UNION ALL
SELECT '-2' UNION ALL
SELECT '-3' UNION ALL
SELECT '-4'
) AS suffixes
WHERE service_type = 'APR-DRG'
) s
LEFT JOIN revenue_calculated rc
ON s.service_code = rc.billing_code
AND rc.billing_code_type = 'APR-DRG'
WHERE s.service_type = 'APR-DRG'
-- exclude Transplant, ECMO, and CAR-T
AND s.service_code NOT IN (
'0001', '0002', '0004', '0005', '0006', '0007', '0008', '0009',
'0011', '0161', '0440'
)
UNION ALL
-- top ~5000 medicare payable HCPCS by revenue + handful of manual additions
select *
from {{ cld_params.Tables.OUTPATIENT_CODESET.value }}
UNION ALL
-- SSP line codes
SELECT
bill_type,
billing_code_type,
billing_code,
revenue,
facility
FROM ssp_line_codes
UNION ALL
-- J1 HCPCS
select distinct
'Outpatient' as bill_type,
'HCPCS' as billing_code_type,
orp.hcpcs as billing_code,
util.total_encounters * opps.payment_rate as revenue,
NULL as facility
from {{ cld_params.Tables.TMP_OPPS_MEDICARE_REFERENCE_TABLE.value }} orp
left join opps
on orp.hcpcs = opps.hcpcs
left join util
on opps.hcpcs = util.billing_code
where orp.status_indicator = 'J1'
UNION ALL
-- Life Sciences
SELECT *
FROM life_sciences
UNION ALL
-- Physician Groups: Professional Fees | Facility
SELECT
'Professional' AS bill_type,
'HCPCS' AS billing_code_type,
procedure_code AS billing_code,
NULL AS revenue,
True as facility
FROM {{ cld_params.Tables.CLD_PHYSICIAN_GROUP_CODESET.value }}
UNION ALL
-- Physician Groups: Professional Fees | Non-Facility
SELECT
'Professional' AS bill_type,
'HCPCS' AS billing_code_type,
procedure_code AS billing_code,
NULL AS revenue,
False as facility
FROM {{ cld_params.Tables.CLD_PHYSICIAN_GROUP_CODESET.value }}
UNION ALL
-- Laboratories
SELECT
bill_type,
billing_code_type,
billing_code,
revenue,
facility
FROM lab_revenue_calculated
),
codes AS (
SELECT DISTINCT
bill_type,
billing_code_type,
billing_code,
facility
FROM all_codes
),
descriptions AS (
SELECT distinct service_code, service_type, service_description
FROM {{ cld_params.Tables.SPINES_SERVICES.value }}
WHERE
service_type IN ('HCPCS', 'MS-DRG', 'APR-DRG')
),
categories AS (
SELECT
DISTINCT
service_code,
service_type,
category
FROM {{ cld_params.Tables.SPINES_SERVICES.value }} ss
left join {{ cld_params.Tables.SPINES_SERVICES_RELATIONSHIPS.value }} ssr
on ss.service_id = ssr.service_id
left join {{ cld_params.Tables.SPINES_SERVICES_CLINICAL_CATEGORIES.value }} sscc
on sscc.category_id = ssr.category_id
),
--------------------------------
-- Identify SURG codes from the OPG schedules and billing codes starting with 1-6
-- Also lbel MS-DRGs as surgical codes using `type` from REF_CMS_MSDRG_WEIGHTS
--------------------------------
opg_schedules AS (
SELECT '643' AS payer_id, billing_code, billing_code_type, grouper
FROM {{ cld_params.Tables.OPG_SCHEDULES_UNITED.value }}
UNION ALL
SELECT '7' AS payer_id, billing_code, billing_code_type, grouper
FROM {{ cld_params.Tables.OPG_SCHEDULES_AETNA.value}}
UNION ALL
SELECT '76' AS payer_id, billing_code, billing_code_type, grouper
FROM {{ cld_params.Tables.OPG_SCHEDULES_CIGNA.value}}
UNION ALL
SELECT '403' AS payer_id, billing_code, billing_code_type, grouper
FROM {{ cld_params.Tables.OPG_SCHEDULES_BCBS_CA.value }}
),
surg_codes AS (
SELECT
distinct
billing_code,
'HCPCS' AS billing_code_type,
True as is_surg_code
FROM opg_schedules
UNION
SELECT
distinct
billing_code,
'HCPCS' AS billing_code_type,
True as is_surg_code
FROM codes
WHERE billing_code_type = 'HCPCS'
AND SUBSTRING(billing_code, 1, 1) IN ('1', '2', '3', '4', '5', '6')
UNION
SELECT
distinct
msdrg as billing_code,
'MS-DRG' AS billing_code_type,
True as is_surg_code
FROM {{ cld_params.Tables.REF_CMS_MSDRG_WEIGHTS.value }}
WHERE type = 'SURG'
)
SELECT
codes.bill_type,
codes.billing_code_type,
codes.billing_code,
codes.billing_code_type || ' ' || codes.billing_code AS billing_code_full,
codes.facility,
d.service_description,
CASE
WHEN codes.billing_code IN (SELECT billing_code FROM {{ cld_params.Tables.ANESTHESIA_BASE_UNITS.value }})
THEN 'Anesthesia'
ELSE ctg.category
END AS service_line,
therap.therapeutic_area,
dn.drug_name_array,
surg_codes.is_surg_code,
CASE
WHEN dpr_codes.code IS NOT NULL
THEN True
ELSE False
END as is_drug_code
FROM codes
LEFT JOIN categories ctg
ON codes.billing_code = ctg.service_code
AND codes.billing_code_type = ctg.service_type
LEFT JOIN {{ cld_params.Tables.DRUG_THERAPEUTIC_AREAS.value }} therap
ON therap.billing_code = codes.billing_code
LEFT JOIN surg_codes
ON codes.billing_code = surg_codes.billing_code
AND codes.billing_code_type = surg_codes.billing_code_type
LEFT JOIN dpr_codes
ON codes.billing_code = dpr_codes.code
LEFT JOIN descriptions d
-- SPLIT_PART for APR-DRGs to remove SOI
ON SPLIT_PART(codes.billing_code, '-', 1) = d.service_code
AND codes.billing_code_type = d.service_type
LEFT JOIN drug_names dn
ON codes.billing_code = dn.code
AND dpr_codes.code IS NOT NULL
{% if cmv_codes %}
WHERE codes.billing_code IN (
SELECT DISTINCT billing_code
FROM {{ cld_params.Tables.CMV_FF.value }}
)
{% endif %}