CREATE TABLE {{ schema_name }}.tmp_whisper_provider_{{ sub_version }}
WITH (
PARTITIONING = ARRAY['provider_type']
)
AS
WITH
hospital_scorecards AS (
SELECT
CAST(provider_id AS VARCHAR) as provider_id,
composite_score as hospital_scorecard_composite_score,
cash_price_score as hospital_scorecard_cash_price_score,
list_price_score as hospital_scorecard_list_price_score,
v2_score as hospital_scorecard_v2_score
FROM {{ cld_params.Tables.HOSPITAL_SCORECARD_COMPOSITE_SCORES.value }}
),
nashp AS (
SELECT
ccn,
net_profit_margin as nashp_net_profit_margin,
commercial_breakeven as nashp_commercial_breakeven,
charity_care_and_uninsured_and_bad_debt_payer_mix as nashp_charity_mix,
medicaid__schip_and_low_income_gov_t_program_payer_mix as nashp_medicaid_mix,
medicare_payer_mix + medicare_adv_payer_mix as nashp_medicare_mix,
commercial_payer_mix as nashp_commercial_payer_mix,
total_drug_costs as nashp_total_drug_costs,
total_drug_charges as nashp_total_drug_charges,
1.00 *total_drug_charges / total_drug_costs as nashp_drug_charge_ratio,
inpatient_drug_charges as nashp_inpatient_drug_charges,
outpatient_drug_charges as nashp_outpatient_drug_charges
FROM {{ cld_params.Tables.NASHP_HOSPITAL_COST_REPORT.value }}
),
carejourney AS (
SELECT
medicare_provider_id,
provider_count_physician AS cj_hospital_provider_count_physician,
provider_count_non_physician AS cj_hospital_provider_count_non_physician,
provider_count_unknown_type AS cj_hospital_provider_count_unknown_type,
provider_count_all AS cj_hospital_provider_count_all,
avg_quality_score_physician AS cj_hospital_avg_quality_score_physician,
avg_quality_score_non_physician AS cj_hospital_avg_quality_score_non_physician,
avg_cost_score_physician AS cj_hospital_avg_cost_score_physician,
avg_cost_score_non_physician AS cj_hospital_cost_score_non_physician,
avg_quality_score_all AS cj_hospital_avg_quality_score_all,
avg_cost_score_all AS cj_hospital_avg_cost_score_all
FROM {{ cld_params.Tables.CAREJOURNEY_HOSPITAL_SCORES.value }}
)
SELECT
p.provider_id,
p.provider_name,
p.health_system_name,
p.medicare_provider_id,
p.provider_type,
p.city,
p.cbsa_name,
ms.cbsa,
ms.state,
hs.hospital_scorecard_composite_score,
hs.hospital_scorecard_cash_price_score,
hs.hospital_scorecard_list_price_score,
hs.hospital_scorecard_v2_score,
p.total_beds,
ms.hospital_ownership as provider_hospital_ownership,
ms.market_concentration_index as provider_market_concentration_index,
ms.geographic_classification as provider_geographic_classification,
CASE
WHEN ms._340b_hospital_type IS NOT NULL THEN True
ELSE False
END as provider_is_340b_hospital,
ms._340b_hospital_type as provider_340b_hospital_type,
ms.number_staffed_beds as number_staffed_beds,
ms.number_licensed_beds as number_licensed_beds,
ms.net_patient_revenue as provider_net_patient_revenue,
ms.pct_of_cbsa_net_patient_revenue as provider_pct_of_cbsa_net_patient_revenue,
ms.pct_of_state_net_patient_revenue as provider_pct_of_state_net_patient_revenue,
ms.pct_of_national_net_patient_revenue as provider_pct_of_national_net_patient_revenue,
ms.cbsa_rank as provider_net_patient_revenue_cbsa_rank,
ms.state_rank as provider_net_patient_revenue_state_rank,
ms.national_rank as provider_net_patient_revenue_national_rank,
CASE
WHEN
ms.pos_medical_school_affiliation in ('Graduate', 'Major')
or ms.medical_school_affiliates is not null
THEN True
ELSE False
END as provider_amc,
cms.hospital_overall_rating as provider_cms_overall_rating,
lf.hospital_score as provider_leapfrog_score,
lf.hospital_grade as provider_leapfrog_grade,
n.nashp_net_profit_margin,
n.nashp_commercial_breakeven,
n.nashp_charity_mix,
n.nashp_medicaid_mix,
n.nashp_medicare_mix,
n.nashp_commercial_payer_mix,
n.nashp_total_drug_costs,
n.nashp_total_drug_charges,
n.nashp_drug_charge_ratio,
n.nashp_inpatient_drug_charges,
n.nashp_outpatient_drug_charges,
cmi.cmi,
cj_hospital_provider_count_physician,
cj_hospital_provider_count_non_physician,
cj_hospital_provider_count_unknown_type,
cj_hospital_provider_count_all,
cj_hospital_avg_quality_score_physician,
cj_hospital_avg_quality_score_non_physician,
cj_hospital_avg_cost_score_physician,
cj_hospital_cost_score_non_physician,
cj_hospital_avg_quality_score_all,
cj_hospital_avg_cost_score_all
FROM {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }} p
LEFT JOIN hospital_scorecards hs
ON p.provider_id = hs.provider_id
LEFT JOIN {{ schema_name }}.tmp_ref_definitive_hospital_market_share_{{ sub_version }} ms
ON p.provider_id = ms.provider_id
LEFT JOIN {{ schema_name }}.tmp_ref_quality_cms_{{ sub_version }} cms
ON p.provider_id = cms.provider_id
LEFT JOIN {{ schema_name }}.tmp_ref_quality_leapfrog_{{ sub_version }} lf
ON p.provider_id = lf.provider_id
LEFT JOIN nashp n
ON p.medicare_provider_id = CAST(n.ccn AS VARCHAR)
LEFT JOIN {{ cld_params.Tables.REF_CMS_CMI.value }} cmi
ON p.medicare_provider_id = cmi.provider_number
LEFT JOIN carejourney cj
ON p.medicare_provider_id = cj.medicare_provider_id