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 OR REPLACE TABLE {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }} AS
WITH
geo_state AS (
SELECT state_abbreviation, state_name
FROM {{ cld_params.Tables.SPINES_GEO_STATE.value }}
),
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 }}
)
WHERE npi IS NOT NULL
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.SPINES_PROVIDER_LABS.value }}
UNION ALL
SELECT npi
FROM {{ cld_params.Tables.SPINES_PROVIDER_LABS_ADDITIONAL_NPIS.value }}
)
GROUP BY 1
HAVING count(distinct encounter_key) > 1000
ORDER BY n DESC
LIMIT 1000
),
labs_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_LABS.value }}
UNION
SELECT
provider_id,
npi
FROM {{ cld_params.Tables.SPINES_PROVIDER_LABS_ADDITIONAL_NPIS.value }}
)
WHERE npi IS NOT NULL
GROUP BY provider_id
),
pd_lab_clean AS (
SELECT
provider_id,
npi,
--ein,
provider_name,
provider_city as city,
provider_state as state,
provider_address as address,
--latitude,
--longitude,
provider_cbsa_code as cbsa,
provider_cbsa_name as cbsa_name,
--county,
taxonomy_code,
taxonomy_grouping,
provider_type,
national_payer_coverage
FROM {{ cld_params.Tables.SPINES_PROVIDER_LABS.value }}
WHERE (
npi IN (
SELECT hco_1_npi
FROM labs_filtered
)
OR
provider_name LIKE 'Quest Diag%'
OR
provider_name LIKE 'Laboratory Corp%'
OR
provider_name LIKE 'Labcorp%'
)
),
pd_lab_agg AS (
SELECT
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(provider_type) as provider_type,
ANY_VALUE(national_payer_coverage) as national_payer_coverage,
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_id, 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,
geo_state.state_name || ' (' || a.provider_state || ')' as state_name_full,
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_type as provider_type,
a.provider_type as provider_type_new,
a.provider_subtype as provider_subtype,
'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,
FALSE as national_payer_coverage
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 geo_state
ON a.provider_state = geo_state.state_abbreviation
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,
geo_state.state_name || ' (' || a.provider_state || ')' as state_name_full,
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.provider_type as provider_type_new,
a.provider_subtype as provider_subtype,
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,
FALSE as national_payer_coverage
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_state
ON a.provider_state = geo_state.state_abbreviation
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,
geo_state.state_name || ' (' || a.provider_state || ')' as state_name_full,
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.provider_type as provider_type_new,
a.provider_subtype as provider_subtype,
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,
FALSE as national_payer_coverage
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_state
ON a.provider_state = geo_state.state_abbreviation
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
pg.provider_id,
ANY_VALUE(ARRAY_SORT(ARRAY_DISTINCT(
CASE
-- ensure provider_npi ARRAYS do not contain NULLs
WHEN CARDINALITY(primary_npis) > 1
THEN filter(primary_npis, x -> x IS NOT NULL)
WHEN CARDINALITY(primary_npis) = 1
THEN transform(primary_npis, x -> COALESCE(x, ''))
ELSE primary_npis
END
))) as npi,
NULL as definitive_id,
ANY_VALUE(pg.provider_name) as provider_name,
NULL as medicare_provider_id,
ARRAY_AGG(tin) as ein,
NULL as city,
ANY_VALUE(pg.provider_state) as provider_state,
ANY_VALUE(geo_state.state_name || ' (' || pg.provider_state || ')') as state_name_full,
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(health_system_id) as health_system_id,
ANY_VALUE(phs.provider_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,
'Physician Group' as provider_type_legacy,
'Physician Group' as provider_subtype,
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,
FALSE as national_payer_coverage
FROM {{ cld_params.Tables.SPINES_PROVIDER_PHYSICIAN_GROUPS.value }} pg
LEFT JOIN {{ cld_params.Tables.SPINES_PROVIDER_PHYSICIAN_GROUPS_ASSOCIATED_TINS.value }} tins
ON pg.provider_id = tins.provider_id
LEFT JOIN (
SELECT *
FROM {{ cld_params.Tables.SPINES_PROVIDER_PHYSICIAN_GROUPS_ASSOCIATED_HS.value }}
WHERE graph_based_affiliation_type = 'Single Affiliated'
) hs
ON pg.provider_id = hs.provider_id
LEFT JOIN {{ cld_params.Tables.SPINES_PROVIDER_HEALTH_SYSTEMS.value }} phs
ON phs.provider_id = hs.health_system_id
LEFT JOIN geo_state
ON pg.provider_state = geo_state.state_abbreviation
WHERE provider_type_rating IN ('Likely', 'Highly Likely')
AND (
pg.total_reassignments > 20
OR
pg.provider_source = 'Definitive'
)
GROUP BY
pg.provider_id
),
laboratories AS (
SELECT
l.provider_id,
CASE
WHEN npis.npi[1] IS NULL THEN NULL
ELSE npis.npi
END as npi,
NULL as definitive_id,
l.provider_name,
NULL as medicare_provider_id,
NULL as ein,
l.city,
l.state,
geo_state.state_name || ' (' || l.state || ')' as state_name_full,
l.cbsa,
cbsa_name,
NULL as county,
NULL as zip_code,
NULL as zip3,
NULL as carrier_locality,
l.address as street_address,
NULL as hq_latitude,
NULL as hq_longitude,
NULL as website_url,
NULL as health_system_id,
NULL as health_system_name,
NULL as health_system_type,
NULL as health_system_city,
NULL as health_system_state,
l.provider_type,
l.provider_type as provider_type_new,
l.provider_type as provider_subtype,
l.taxonomy_code,
l.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,
l.national_payer_coverage
FROM pd_lab_agg l
LEFT JOIN geo_state
ON l.state = geo_state.state_abbreviation
LEFT JOIN labs_array_npi as npis
ON l.provider_id = npis.provider_id
),
infusion_centers AS (
SELECT
p.provider_id,
ARRAY[p.npi] as npi,
NULL as definitive_id,
p.provider_name,
NULL as medicare_provider_id,
NULL as ein,
p.provider_city AS city,
p.provider_state AS state,
geo_state.state_name || ' (' || p.provider_state || ')' as state_name_full,
NULL AS cbsa,
NULL AS cbsa_name,
NULL AS county,
provider_zip5 as zip_code,
provider_zip3 as zip3,
NULL as carrier_locality,
NULL as street_address,
NULL as hq_latitude,
NULL as hq_longitude,
NULL as website_url,
NULL as health_system_id,
NULL as health_system_name,
NULL as health_system_type,
NULL as health_system_city,
NULL as health_system_state,
provider_type,
provider_type AS provider_type_legacy,
provider_subtype,
selected_taxonomy_code AS taxonomy_code,
NULL AS taxonomy_grouping,
NULL as total_beds,
NULL as operating_room_count,
NULL as services_lab,
NULL as services_radiology,
NULL as services_cardiology,
NULL as services_dental,
NULL as services_dermatology,
NULL as services_ent,
NULL as services_general_other,
NULL as services_endoscopy,
NULL as services_nephrology,
NULL as services_neurology,
NULL as services_obgyn,
NULL as services_opthalmology,
NULL as services_orthopedic,
NULL as services_pain,
NULL as services_plastic_surgery,
NULL as services_podiatry,
NULL as services_pulmonary,
NULL as services_urology,
NULL as services_mra,
NULL as services_ct,
NULL as services_mri,
NULL as services_mammography,
NULL as services_cta,
NULL as services_xray,
NULL as services_ultrasound,
NULL as services_spect,
NULL as services_pet,
NULL as services_imrt,
FALSE as national_payer_coverage
FROM {{ cld_params.Tables.SPINES_PROVIDER_INFUSION_CENTERS.value }} p
LEFT JOIN geo_state
ON p.provider_state = geo_state.state_abbreviation
),
-- COMBINE
df AS (
select * from hospitals
union
select * from ascs
union
select * from imaging_centers
union
select * from physician_groups
union
select * from laboratories
union
select * from infusion_centers
)
SELECT *
FROM df
WHERE
(
CASE
WHEN provider_type = 'Hospital'
THEN provider_subtype IN (
'Childrens Hospital',
'Critical Access Hospital',
'Short Term Acute Care Hospital',
'Rehabilitation Hospital'
)
ELSE provider_type IN (
{{ provider_types}}
)
END
)
AND
state IN (
{{ US_STATES }}
)
{% if lookback_run %}
AND
provider_type NOT IN ('ASC', 'Physician Group')
{% endif %}
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',
'768',
'567',
'624',
'779',
'305',
'728',
'784',
'699',
'356',
'27',
'971',
'97',
'108',
'638',
'958',
'796',
'464'
] %}
CREATE OR REPLACE 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 = 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,
CASE
WHEN payer_payer_id IN (
42,43,44,46,47,48,49,50,51,52,53,54,56,61,62,
101,160,165,166,168,169,170,174,229,383,388,389,
390,391,392,397,398,403,454,455,456,458,461,462,
464,628,729,837,914,952
)
THEN True
ELSE False
END as blue_payer
FROM payer_ids
)
SELECT
p.payer_id,
sp.payer_name,
p.definitive_payer_id,
p.blue_payer
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 }}
-- Step: Create base table with PPO mappings
-- Helper macro to generate a SELECT row
{% macro network_row(payer_id, network_name, states, product_network_label, network_class, network_type) %}
SELECT
from_big_endian_64(xxhash64(CAST('{{ payer_id }}' || '{{ network_name }}' AS varbinary))) AS network_id,
'{{ network_name }}' AS network_name,
ARRAY[{{ states }}] AS network_state,
'{{ payer_id }}' AS payer_id,
ARRAY[{{ product_network_label }}] AS payer_data_network,
'{{ network_class }}' AS network_class,
'{{ network_type }}' AS network_type
{% endmacro %}
-- Create table with PPO mappings
CREATE OR REPLACE TABLE {{ schema_name }}.tmp_ref_network_spine_{{ sub_version }} AS
{% for payer_id, product_network_label, network_name, states in ppo_network_mappings %}
{{ network_row(payer_id, network_name, states, product_network_label, 'Commercial', 'PPO') }}
{% if not loop.last %}UNION ALL{% endif %}
{% endfor %}
Network Components​
Custom​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_ref_network_spine_{{ sub_version }}
-- Subversion: {{ sub_version }}
-- Step: Insert Custom PPO and HMO mappings
-- Helper macro to generate a SELECT row
{% macro network_row(payer_id, network_name, states, product_network_label, network_class, network_type) %}
SELECT
from_big_endian_64(xxhash64(CAST('{{ payer_id }}' || '{{ network_name }}' AS varbinary))) AS network_id,
'{{ network_name }}' AS network_name,
ARRAY[{{ states }}] AS network_state,
'{{ payer_id }}' AS payer_id,
ARRAY[{{ product_network_label }}] AS payer_data_network,
'{{ network_class }}' AS network_class,
'{{ network_type }}' AS network_type
{% endmacro %}
-- Insert Custom PPO and HMO mappings
INSERT INTO {{ schema_name }}.tmp_ref_network_spine_{{ sub_version }}
{% for payer_id, product_network_label, network_name, states in custom_ppo_mappings %}
{{ network_row(payer_id, network_name, states, product_network_label, 'Commercial', 'PPO') }}
UNION ALL
{% endfor %}
{% for payer_id, product_network_label, network_name, states in custom_hmo_mappings %}
{{ network_row(payer_id, network_name, states, product_network_label, 'Commercial', 'HMO') }}
{% if not loop.last %}UNION ALL{% endif %}
{% endfor %}
Dedup​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_ref_network_spine_{{ sub_version }}
-- Subversion: {{ sub_version }}
-- Step: Deduplicate final table
-- Deduplicate the table (equivalent to the original DISTINCT)
CREATE OR REPLACE TABLE {{ schema_name }}.tmp_ref_network_spine_{{ sub_version }} AS
SELECT DISTINCT * FROM {{ schema_name }}.tmp_ref_network_spine_{{ sub_version }}
Exchange​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_ref_network_spine_{{ sub_version }}
-- Subversion: {{ sub_version }}
-- Step: Insert EXCHANGE mappings
-- Helper macro to generate a SELECT row
{% macro network_row(payer_id, network_name, states, product_network_label, network_class, network_type) %}
SELECT
from_big_endian_64(xxhash64(CAST('{{ payer_id }}' || '{{ network_name }}' AS varbinary))) AS network_id,
'{{ network_name }}' AS network_name,
ARRAY[{{ states }}] AS network_state,
'{{ payer_id }}' AS payer_id,
ARRAY[{{ product_network_label }}] AS payer_data_network,
'{{ network_class }}' AS network_class,
'{{ network_type }}' AS network_type
{% endmacro %}
-- Insert EXCHANGE mappings
INSERT INTO {{ schema_name }}.tmp_ref_network_spine_{{ sub_version }}
{% for payer_id, product_network_label, network_name, states in exchange_network_mappings %}
{{ network_row(payer_id, network_name, states, product_network_label, 'Exchange', 'EXCHANGE') }}
{% if not loop.last %}UNION ALL{% endif %}
{% endfor %}
HMO​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_ref_network_spine_{{ sub_version }}
-- Subversion: {{ sub_version }}
-- Step: Insert HMO mappings
-- Helper macro to generate a SELECT row
{% macro network_row(payer_id, network_name, states, product_network_label, network_class, network_type) %}
SELECT
from_big_endian_64(xxhash64(CAST('{{ payer_id }}' || '{{ network_name }}' AS varbinary))) AS network_id,
'{{ network_name }}' AS network_name,
ARRAY[{{ states }}] AS network_state,
'{{ payer_id }}' AS payer_id,
ARRAY[{{ product_network_label }}] AS payer_data_network,
'{{ network_class }}' AS network_class,
'{{ network_type }}' AS network_type
{% endmacro %}
-- Insert HMO mappings
INSERT INTO {{ schema_name }}.tmp_ref_network_spine_{{ sub_version }}
{% for payer_id, product_network_label, network_name, states in hmo_network_mappings %}
{{ network_row(payer_id, network_name, states, product_network_label, 'Commercial', 'HMO') }}
{% if not loop.last %}UNION ALL{% endif %}
{% endfor %}
Narrow​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_ref_network_spine_{{ sub_version }}
-- Subversion: {{ sub_version }}
-- Step: Insert NARROW mappings
-- Helper macro to generate a SELECT row
{% macro network_row(payer_id, network_name, states, product_network_label, network_class, network_type) %}
SELECT
from_big_endian_64(xxhash64(CAST('{{ payer_id }}' || '{{ network_name }}' AS varbinary))) AS network_id,
'{{ network_name }}' AS network_name,
ARRAY[{{ states }}] AS network_state,
'{{ payer_id }}' AS payer_id,
ARRAY[{{ product_network_label }}] AS payer_data_network,
'{{ network_class }}' AS network_class,
'{{ network_type }}' AS network_type
{% endmacro %}
-- Insert NARROW mappings
INSERT INTO {{ schema_name }}.tmp_ref_network_spine_{{ sub_version }}
{% for payer_id, product_network_label, network_name, states in narrow_network_mappings %}
{{ network_row(payer_id, network_name, states, product_network_label, 'Commercial', 'NARROW') }}
{% if not loop.last %}UNION ALL{% endif %}
{% endfor %}
Code​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_ref_code_spine_{{ sub_version }}
-- Subversion: {{ sub_version }}
CREATE OR REPLACE 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
UNION ALL
SELECT
'Professional' AS bill_type,
'HCPCS' AS billing_code_type,
t.code,
NULL AS revenue,
False 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', '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
-- also includes lab codes for hospitals and PGs
-- https://github.com/turquoisehealth/airflow_dags/blob/f0c3ca71721b136dcd1e7ee16ed766a34d8f889a/dags/data_science/cld/core_licensable_data_utils/sql/spines/outpatient_codeset.sql
SELECT *
FROM {{ cld_params.Tables.OUTPATIENT_CODESET.value }}
UNION ALL
-- https://github.com/turquoisehealth/airflow_dags/blob/f0c3ca71721b136dcd1e7ee16ed766a34d8f889a/dags/data_science/cld/core_licensable_data_utils/sql/spines/asc_codeset.sql
SELECT *
FROM {{ cld_params.Tables.ASC_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 }}
WHERE procedure_code NOT IN (
SELECT code
FROM dpr_codes
)
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 }}
WHERE procedure_code NOT IN (
SELECT code
FROM dpr_codes
)
UNION ALL
-- Laboratories + Physician Groups: Lab Codes | Non-Facility
SELECT
'Professional' AS bill_type,
'HCPCS' AS billing_code_type,
billing_code,
NULL AS revenue,
False as facility
FROM {{ cld_params.Tables.LAB_HOSPITAL_AND_PGS_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,
CASE
WHEN
codes.billing_code IN (SELECT billing_code FROM {{ cld_params.Tables.LAB_HOSPITAL_AND_PGS_CODESET.value }})
OR
codes.billing_code IN (SELECT hcpcs FROM {{ cld_params.Tables.CLINICAL_LAB_REFERENCE_PRICING.value }})
THEN True
ELSE False
END as is_lab_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 %}