{% set dosage_standardized = "(rates.gross_charge / CAST(NULLIF(dd.parsed_quantity, 0) AS DECIMAL(18, 2))) * asp.quantity" %}
{% set bill_type = """
CASE
WHEN billing_code_type IN ('MS-DRG','APR-DRG','DRG')
THEN 'Inpatient'
WHEN COALESCE(billing_class, '') != 'Professional' AND billing_code_type IN ('CPT', 'HCPCS', 'APC', 'EAPG')
THEN 'Outpatient'
WHEN billing_class = 'Professional' AND billing_code_type IN ('CPT', 'HCPCS', 'APC', 'EAPG')
THEN 'Professional'
END
""" %}
{% macro claim_dosage_standardize(col) %}
(1.000 * {{col}} / NULLIF(median_billed_units, 0)) * asp.quantity
{% endmacro %}
{% macro komodo_gross_charge(alias, scope, ccr_adjust_col) %}
CASE
WHEN ros.bill_type = 'Inpatient' OR cs.is_surg_code
THEN {{ alias }}.gross_charge_claim
{% set std_base = "(1.00 * " ~ alias ~ ".gross_charge_line_item / NULLIF(" ~ alias ~ ".median_billed_units,0)) * asp.quantity" %}
{% if ccr_adjust_col %}
{% set std = "(" ~ std_base ~ ") * COALESCE(" ~ ccr_adjust_col ~ ",1)" %}
{% else %}
{% set std = std_base %}
{% endif %}
WHEN {{ std }} BETWEEN asp.gc_lower AND asp.gc_upper
THEN {{ std }}
WHEN asp.asp_payment_limit IS NOT NULL AND {{ alias }}.gross_charge_line_item
NOT BETWEEN asp.gc_lower AND asp.gc_upper
THEN NULL
ELSE {{ alias }}.gross_charge_line_item
END AS komodo_gross_charge_{{ scope }},
{{ alias }}.count_encounters AS komodo_gross_charge_{{ scope }}_count_encounters,
{% if ccr_adjust_col %}
COALESCE({{ ccr_adjust_col }},1) AS gross_charge_{{ scope | replace('_median','') }}_provider_adjustment,
{% endif %}
{% endmacro %}
{% macro komodo_gross_charge_dosage_standardization_params(alias, scope, ccr_adjust_col) %}
CASE
WHEN ros.bill_type = 'Inpatient' OR cs.is_surg_code
THEN JSON_OBJECT()
{% set std_base = "(1.00 * " ~ alias ~ ".gross_charge_line_item / NULLIF(" ~ alias ~ ".median_billed_units,0)) * asp.quantity" %}
{% if ccr_adjust_col %}
{% set std = "(" ~ std_base ~ ") * COALESCE(" ~ ccr_adjust_col ~ ",1)" %}
{% else %}
{% set std = std_base %}
{% endif %}
WHEN {{ std }} BETWEEN asp.gc_lower AND asp.gc_upper
THEN JSON_OBJECT(
'raw_gross_charge': {{ alias }}.gross_charge_line_item,
'median_billed_units': {{ alias }}.median_billed_units,
'asp_quantity': asp.quantity,
'asp_unit_label': asp.unit_label
)
WHEN asp.asp_payment_limit IS NOT NULL AND {{ alias }}.gross_charge_line_item
NOT BETWEEN asp.gc_lower AND asp.gc_upper
THEN JSON_OBJECT()
ELSE JSON_OBJECT()
END AS komodo_gross_charge_{{ scope }}_dosage_standardization_params,
{% endmacro %}
CREATE OR REPLACE TABLE {{ schema_name }}.tmp_raw_gross_charges_{{ n_chunk }}_{{ sub_version }}
WITH (
PARTITIONING = ARRAY['payer_id']
)
AS
WITH
asp AS (
SELECT
hcpcs,
CAST(quantity AS DOUBLE) as quantity,
unit_label,
CAST(payment_limit AS DOUBLE) as asp_payment_limit,
payment_limit * {{ cld_params.GROSS_CHARGE_ASP_LOWER_BOUND }} AS gc_lower,
payment_limit * {{ cld_params.GROSS_CHARGE_ASP_UPPER_BOUND }} AS gc_upper
FROM (
SELECT
hcpcs,
REPLACE(COALESCE(REGEXP_EXTRACT(dosage, '[0-9.]+'), '1'),',','') as quantity,
COALESCE(REGEXP_REPLACE(dosage, '[0-9.]+', ''), '') as unit_label,
payment_limit,
ROW_NUMBER() OVER (
PARTITION BY hcpcs
ORDER BY start_effective_date DESC
) as rn
FROM {{ cld_params.Tables.ASP_PRICING_TABLE.value }}
WHERE {% if historic_run %}year(start_effective_date) = {{ sub_version[:4] }}{% else %}is_latest_start_effective_date = true{% endif %}
)
WHERE rn = 1
),
aprdrg_xwalk AS (
SELECT *
FROM {{ cld_params.Tables.APR_DRG_XWALK_TABLE.value }}
),
hospital_rates_with_billing_code AS (
SELECT
provider_id,
{{ bill_type }} as bill_type,
CASE
WHEN billing_code_type = 'DRG' THEN 'MS-DRG'
ELSE billing_code_type
END as billing_code_type,
CASE
WHEN billing_code_type = 'MS-DRG'
THEN lpad(billing_code,3,'0')
WHEN billing_code_type = 'APR-DRG'
THEN LPAD(SPLIT_PART(billing_code, '-', 1), 4, '0') ||
CASE
WHEN REGEXP_LIKE(SPLIT_PART(billing_code, '-', 2), '^[0-9]+$')
THEN '-' || SPLIT_PART(billing_code, '-', 2)
ELSE ''
END
ELSE billing_code
END as billing_code,
setting,
payer_class_name,
gross_charge,
description,
billing_class
FROM {{ hospital_schema }}.hospital_rates
WHERE
(gross_charge BETWEEN 0.1 AND 100000000)
AND payer_class_name = 'Commercial'
AND provider_id IN ({{ provider_ids }})
),
hospital_rates_with_new_columns_added AS (
SELECT
DISTINCT
provider_id,
bill_type,
billing_code_type,
billing_code,
NULL as gross_charge_original_billing_code_type,
setting,
payer_class_name,
ARRAY[] as gross_charge_original_billing_code,
dd.parsed_quantity,
dd.parsed_unit_label,
asp.quantity as asp_quantity,
asp.unit_label as asp_unit_label,
CASE
WHEN {{ dosage_standardized }} BETWEEN gc_lower AND gc_upper
THEN {{ dosage_standardized }}
WHEN asp.asp_payment_limit IS NOT NULL
AND rates.gross_charge NOT BETWEEN gc_lower AND gc_upper
THEN NULL
ELSE rates.gross_charge
END as gross_charge,
CASE
WHEN {{ dosage_standardized }} BETWEEN gc_lower AND gc_upper
THEN JSON_OBJECT(
'raw_gross_charge': rates.gross_charge,
'parsed_quantity': dd.parsed_quantity,
'parsed_unit_label': dd.parsed_unit_label,
'asp_quantity': asp.quantity,
'asp_unit_label': asp.unit_label
)
WHEN asp.asp_payment_limit IS NOT NULL
AND rates.gross_charge NOT BETWEEN gc_lower AND gc_upper
THEN JSON_OBJECT()
ELSE JSON_OBJECT()
END as gross_charge_dosage_standardization_params
FROM hospital_rates_with_billing_code rates
LEFT JOIN (
SELECT
description,
code,
any_value(CAST(parsed_quantity AS DECIMAL(18, 2))) as parsed_quantity,
any_value(parsed_unit_label) as parsed_unit_label
FROM {{ drug_dosage_std_table }}
WHERE parsed_quantity != ''
GROUP BY description, code
) dd
ON rates.description = dd.description
AND rates.billing_code = dd.code
LEFT JOIN asp
on asp.hcpcs = rates.billing_code
WHERE
billing_code IN (
SELECT DISTINCT billing_code
FROM {{ schema_name }}.tmp_ref_code_spine_{{ sub_version }}
)
),
inventory AS (
SELECT
DISTINCT
provider_id,
bill_type,
billing_code_type,
billing_code,
True as exists_in_inventory
FROM hospital_rates_with_new_columns_added
),
ms_drg_to_apr_drg AS (
SELECT
r.provider_id,
r.bill_type,
'APR-DRG' as billing_code_type,
dc.apr_drg as billing_code,
'MS-DRG' as gross_charge_original_billing_code_type,
r.setting,
r.payer_class_name,
ARRAY_DISTINCT(ARRAY_AGG(r.billing_code)) as gross_charge_original_billing_code,
NULL as parsed_quantity,
NULL as parsed_unit_label,
NULL as asp_quantity,
NULL as asp_unit_label,
AVG(r.gross_charge) as gross_charge,
NULL as gross_charge_dosage_standardization_params
FROM hospital_rates_with_new_columns_added r
JOIN aprdrg_xwalk dc
ON r.billing_code = dc.ms_drg
LEFT JOIN inventory i
ON r.provider_id = i.provider_id
AND r.bill_type = i.bill_type
AND 'APR-DRG' = i.billing_code_type
AND dc.apr_drg = i.billing_code
WHERE r.billing_code_type = 'MS-DRG'
AND i.exists_in_inventory IS NULL
GROUP BY 1, 2, 3, 4, 5, 6, 7
),
apr_drg_to_ms_drg AS (
SELECT
r.provider_id,
r.bill_type,
'MS-DRG' as billing_code_type,
dc.ms_drg as billing_code,
'APR-DRG' as gross_charge_original_billing_code_type,
r.setting,
r.payer_class_name,
ARRAY_DISTINCT(ARRAY_AGG(r.billing_code)) as gross_charge_original_billing_code,
NULL as parsed_quantity,
NULL as parsed_unit_label,
NULL as asp_quantity,
NULL as asp_unit_label,
AVG(r.gross_charge) as gross_charge,
NULL as gross_charge_dosage_standardization_params
FROM hospital_rates_with_new_columns_added r
JOIN aprdrg_xwalk dc
ON r.billing_code = dc.apr_drg
LEFT JOIN inventory i
ON r.provider_id = i.provider_id
AND r.bill_type = i.bill_type
AND 'MS-DRG' = i.billing_code_type
AND dc.ms_drg = i.billing_code
WHERE r.billing_code_type = 'APR-DRG'
AND i.exists_in_inventory IS NULL
GROUP BY 1, 2, 3, 4, 5, 6, 7
),
combined_all_hospital_rates AS (
SELECT *
FROM hospital_rates_with_new_columns_added
UNION ALL
SELECT *
FROM ms_drg_to_apr_drg
UNION ALL
SELECT *
FROM apr_drg_to_ms_drg
),
hospital_gross_charge_ranked AS (
SELECT
hr.provider_id,
bill_type,
billing_code_type,
billing_code,
gross_charge_original_billing_code_type,
gross_charge_original_billing_code,
gross_charge,
gross_charge_dosage_standardization_params,
ROW_NUMBER() OVER (
PARTITION BY hr.provider_id, bill_type, billing_code_type, billing_code
ORDER BY gross_charge DESC
) as rn
FROM combined_all_hospital_rates hr
),
hospital_gross_charge AS (
SELECT
hr.provider_id,
ps.cbsa,
ps.state,
bill_type,
billing_code_type,
billing_code,
gross_charge_original_billing_code_type,
gross_charge_original_billing_code,
gross_charge,
gross_charge_dosage_standardization_params
FROM hospital_gross_charge_ranked hr
LEFT JOIN {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }} ps
ON hr.provider_id = ps.provider_id
WHERE rn = 1
),
hospital_gross_charge_cbsa_median AS (
SELECT
cbsa,
'Hospitals' as taxonomy_grouping,
bill_type,
billing_code_type,
billing_code,
approx_percentile(gross_charge, 0.5) as gross_charge
FROM hospital_gross_charge hr
GROUP BY 1, 2, 3, 4, 5
HAVING count(distinct gross_charge) > 4
),
hospital_gross_charge_state_median AS (
SELECT
state,
'Hospitals' as taxonomy_grouping,
bill_type,
billing_code_type,
billing_code,
approx_percentile(gross_charge, 0.5) as gross_charge
FROM hospital_gross_charge hr
GROUP BY 1, 2, 3, 4, 5
HAVING count(distinct gross_charge) > 4
),
komodo_gross_charge_provider AS (
SELECT
CAST(provider_id AS VARCHAR) as provider_id,
billing_code_type,
billing_code,
case
when claim_type_code = 'institutional'
and setting = 'Inpatient'
then 'Inpatient'
when claim_type_code = 'institutional'
and setting = 'Outpatient'
then 'Outpatient'
end as bill_type,
avg(median_billed_units) FILTER (
WHERE gross_charges_type = 'line_item'
) as median_billed_units,
avg(median_gross_charges) FILTER (
WHERE gross_charges_type = 'line_item'
) as gross_charge_line_item,
avg(median_gross_charges) FILTER (
WHERE gross_charges_type = 'claim'
) as gross_charge_claim,
sum(count_encounters) as count_encounters
FROM {{ cld_params.Tables.GROSS_CHARGES_NPI.value }}
WHERE
npi_source = 'hco'
AND billing_code_modifier IS NULL
AND CAST(provider_id AS VARCHAR) IN ({{ provider_ids }})
GROUP BY
provider_id,
billing_code,
billing_code_type,
claim_type_code,
setting
),
komodo_gross_charge_cbsa_median AS (
SELECT
cbsa,
taxonomy_grouping,
billing_code_type,
billing_code,
case
when claim_type_code = 'institutional'
and setting = 'Inpatient'
then 'Inpatient'
when claim_type_code = 'institutional'
and setting = 'Outpatient'
then 'Outpatient'
end as bill_type,
avg(median_billed_units) FILTER (
WHERE gross_charges_type = 'line_item'
) as median_billed_units,
avg(median_gross_charges) FILTER (
WHERE gross_charges_type = 'line_item'
) as gross_charge_line_item,
avg(median_gross_charges) FILTER (
WHERE gross_charges_type = 'claim'
) as gross_charge_claim,
sum(count_encounters) as count_encounters
FROM {{ cld_params.Tables.GROSS_CHARGES_CBSA.value }}
WHERE
npi_source = 'hco'
AND billing_code_modifier IS NULL
{% if version != 'test' %}
{% set year = sub_version[:4] %}
{% if year <= '2024' %}
AND service_year = 2024
{% else %}
AND service_year >= 2024
{% endif %}
{% endif %}
GROUP BY
cbsa,
taxonomy_grouping,
billing_code,
billing_code_type,
claim_type_code,
setting
),
komodo_gross_charge_state_median AS (
SELECT
state,
taxonomy_grouping,
billing_code_type,
billing_code,
case
when claim_type_code = 'institutional'
and setting = 'Inpatient'
then 'Inpatient'
when claim_type_code = 'institutional'
and setting = 'Outpatient'
then 'Outpatient'
end as bill_type,
avg(median_billed_units) FILTER (
WHERE gross_charges_type = 'line_item'
) as median_billed_units,
avg(median_gross_charges) FILTER (
WHERE gross_charges_type = 'line_item'
) as gross_charge_line_item,
avg(median_gross_charges) FILTER (
WHERE gross_charges_type = 'claim'
) as gross_charge_claim,
sum(count_encounters) as count_encounters
FROM {{ cld_params.Tables.GROSS_CHARGES_STATE.value }}
WHERE
npi_source = 'hco'
AND billing_code_modifier IS NULL
{% if version != 'test' %}
{% set year = sub_version[:4] %}
{% if year <= '2024' %}
AND service_year = 2024
{% else %}
AND service_year >= 2024
{% endif %}
{% endif %}
GROUP BY
state,
taxonomy_grouping,
billing_code,
billing_code_type,
claim_type_code,
setting
),
cbsa_relativities AS (
SELECT provider_id, cbsa_ip_ratio_relativity
FROM {{ cld_params.Tables.COST_REPORT_CHARGE_RELATIVITIES.value }}
WHERE cbsa_ip_ratio_relativity BETWEEN 0.2 AND 5
),
state_relativities AS (
SELECT provider_id, state_ip_ratio_relativity
FROM {{ cld_params.Tables.COST_REPORT_CHARGE_RELATIVITIES.value }}
WHERE state_ip_ratio_relativity BETWEEN 0.2 AND 5
),
combined AS (
SELECT
ros.roid,
ros.payer_id,
ros.provider_id,
ros.bill_type,
ros.billing_code_type,
ros.billing_code,
asp.asp_payment_limit,
{{ komodo_gross_charge('kpgc','provider', None) }}
{{ komodo_gross_charge_dosage_standardization_params('kpgc','provider', None) }}
{{ komodo_gross_charge('kgcm','cbsa_median','cr.cbsa_ip_ratio_relativity') }}
{{ komodo_gross_charge_dosage_standardization_params('kgcm','cbsa_median','cr.cbsa_ip_ratio_relativity') }}
{{ komodo_gross_charge('kgsm','state_median','sr.state_ip_ratio_relativity') }}
{{ komodo_gross_charge_dosage_standardization_params('kgsm','state_median','sr.state_ip_ratio_relativity') }}
hgc.gross_charge as mrf_gross_charge_provider,
hgc.gross_charge_dosage_standardization_params as mrf_gross_charge_provider_dosage_standardization_params,
hgc.gross_charge_original_billing_code_type as mrf_gross_charge_provider_original_billing_code_type,
hgc.gross_charge_original_billing_code as mrf_gross_charge_provider_original_billing_code,
hgcm.gross_charge as mrf_gross_charge_cbsa_median,
hgsm.gross_charge as mrf_gross_charge_state_median
FROM {{ schema_name }}.tmp_rate_object_space_{{ sub_version }} ros
LEFT JOIN {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }} ps
ON ros.provider_id = ps.provider_id
LEFT JOIN {{ schema_name }}.tmp_ref_code_spine_{{ sub_version }} cs
ON ros.billing_code = cs.billing_code
AND ros.billing_code_type = cs.billing_code_type
AND ros.bill_type = cs.bill_type
AND (
ros.facility = cs.facility
OR (ros.facility IS NULL AND cs.facility IS NULL)
)
LEFT JOIN hospital_gross_charge hgc
ON ros.provider_id = hgc.provider_id
AND ros.billing_code = hgc.billing_code
AND ros.billing_code_type = hgc.billing_code_type
AND ros.bill_type = hgc.bill_type
LEFT JOIN hospital_gross_charge_cbsa_median hgcm
ON ros.billing_code = hgcm.billing_code
AND ros.billing_code_type = hgcm.billing_code_type
AND ros.bill_type = hgcm.bill_type
AND ps.cbsa = hgcm.cbsa
AND ps.taxonomy_grouping = hgcm.taxonomy_grouping
LEFT JOIN hospital_gross_charge_state_median hgsm
ON ros.billing_code = hgsm.billing_code
AND ros.billing_code_type = hgsm.billing_code_type
AND ros.bill_type = hgsm.bill_type
AND ps.state = hgsm.state
AND ps.taxonomy_grouping = hgsm.taxonomy_grouping
LEFT JOIN komodo_gross_charge_provider kpgc
ON ros.provider_id = kpgc.provider_id
AND ros.billing_code = kpgc.billing_code
AND ros.billing_code_type = kpgc.billing_code_type
AND ros.bill_type = kpgc.bill_type
LEFT JOIN komodo_gross_charge_cbsa_median kgcm
ON ros.billing_code = kgcm.billing_code
AND ros.billing_code_type = kgcm.billing_code_type
AND ros.bill_type = kgcm.bill_type
AND ps.cbsa = kgcm.cbsa
AND ps.taxonomy_grouping = kgcm.taxonomy_grouping
LEFT JOIN komodo_gross_charge_state_median kgsm
ON ros.billing_code = kgsm.billing_code
AND ros.billing_code_type = kgsm.billing_code_type
AND ros.bill_type = kgsm.bill_type
AND ps.state = kgsm.state
AND ps.taxonomy_grouping = kgsm.taxonomy_grouping
LEFT JOIN cbsa_relativities cr
ON ros.provider_id = cr.provider_id
LEFT JOIN state_relativities sr
ON ros.provider_id = sr.provider_id
LEFT JOIN asp
ON ros.billing_code = asp.hcpcs
WHERE (
hgc.gross_charge IS NOT NULL
OR hgcm.gross_charge IS NOT NULL
OR hgsm.gross_charge IS NOT NULL
OR kpgc.gross_charge_line_item IS NOT NULL
OR kpgc.gross_charge_claim IS NOT NULL
OR kgcm.gross_charge_line_item IS NOT NULL
OR kgcm.gross_charge_claim IS NOT NULL
OR kgsm.gross_charge_line_item IS NOT NULL
OR kgsm.gross_charge_claim IS NOT NULL
)
AND ros.provider_id IN ({{ provider_ids }})
)
SELECT
roid,
provider_id,
bill_type,
billing_code_type,
billing_code,
asp_payment_limit,
mrf_gross_charge_provider,
mrf_gross_charge_provider_dosage_standardization_params,
mrf_gross_charge_provider_original_billing_code_type,
mrf_gross_charge_provider_original_billing_code,
mrf_gross_charge_cbsa_median,
mrf_gross_charge_state_median,
komodo_gross_charge_provider,
komodo_gross_charge_provider_dosage_standardization_params,
komodo_gross_charge_provider_count_encounters,
komodo_gross_charge_cbsa_median,
komodo_gross_charge_cbsa_median_dosage_standardization_params,
komodo_gross_charge_cbsa_median_count_encounters,
gross_charge_cbsa_provider_adjustment,
komodo_gross_charge_state_median,
komodo_gross_charge_state_median_dosage_standardization_params,
komodo_gross_charge_state_median_count_encounters,
gross_charge_state_provider_adjustment,
payer_id
FROM combined