ERA Matched Adjustment

ERA charge-level adjustment records with HDB practice, provider, and appointment cross-references applied. Each row represents one charge-level adjustment within an ERA. The table schema can be found in our HDB dbdocs reference. For reporting on but not limited to:

Adjustment totals by payer, practice, or service date
Denial rates and denial reasons by CPT code
Per-appointment revenue when joined to the appointment table
Per-provider revenue when joined to canonical_physician
Drill-down on a specific claim or charge

Each row carries a match_confidence column that grades the appointment-id inference. Filter to match_confidence = 'provider_match' for high-trust per-provider analytics. The provider_mismatch class is common in practices that bill incident-to (charges file under a supervising physician while visits are scheduled under the rendering NP or PA), and the appointment_id on those rows points at the supervisor’s appointment rather than the rendering visit. patient_practice_date_only and no_match rows have a null appointment_id.

Charges and adjustments for a date range

Pulls every charge-level adjustment row for a given service-date window with the per-row financials and payer detail.

select
    era.claim_local_id
  , era.claim_id
  , era.claimmd_charge_id
  , era.charge_service_date
  , era.charge_cpt
  , era.charge_mod1
  , era.charge_mod2
  , era.charge_amount
  , era.charge_allowed
  , era.charge_paid
  , era.adjustment_group
  , era.adjustment_code
  , era.adjustment_amount
  , era.is_denied
  , era.claim_status_code
  , era.posting_status
  , era.claimmd_payer_id
  , era.claim_payer_icn
  , era.check_number
  , era.claim_latest_payment_date
  , era.charge_balance
  , era.charge_balance_responsibility
  , era.provider_npi
  , era.canonical_physician_id
  , concat(era.claim_patient_first_name, ' ', era.claim_patient_last_name) as patient_name
  , era.appointment_id
  , era.match_confidence
from era_matched_adjustment era
where era.charge_service_date between '2026-01-01' and '2026-03-31'
order by era.charge_service_date, era.claim_id, era.claimmd_charge_id;

Practice-level totals over a period

Rolls up charges, adjustments, and totals per practice and payer for a given service-date window. Useful for AR snapshots and payer-mix analysis.

select
    era.practice_id
  , era.claimmd_payer_id
  , count(distinct era.claim_id) as claims
  , count(distinct era.claimmd_charge_id) as charges
  , sum(era.charge_amount) as total_charged
  , sum(era.charge_allowed) as total_allowed
  , sum(era.charge_paid) as total_paid
  , sum(era.adjustment_amount) as total_adjustments
  , sum(iff(era.is_denied, 1, 0)) as denied_charges
  , sum(iff(era.is_denied, era.charge_amount, 0)) as denied_charge_amount
  , round(100.0 * sum(iff(era.is_denied, 1, 0)) / nullif(count(*), 0), 2) as denial_rate_pct
from era_matched_adjustment era
where era.charge_service_date between '2026-01-01' and '2026-03-31'
group by era.practice_id, era.claimmd_payer_id
order by era.practice_id, total_paid desc;

Per-appointment revenue

Joins ERA rows to the appointment that produced them. Filters to provider_match so each appointment’s totals reflect a confident link to the rendering provider’s visit.

select
    era.appointment_id
  , a.appt_time
  , a.appt_type
  , concat(era.claim_patient_first_name, ' ', era.claim_patient_last_name) as patient_name
  , era.practice_id
  , era.canonical_physician_id
  , count(distinct era.claim_id) as claims
  , count(distinct era.claimmd_charge_id) as charges
  , sum(era.charge_amount) as total_charged
  , sum(era.charge_paid) as total_paid
  , sum(era.adjustment_amount) as total_adjustments
  , listagg(distinct era.charge_cpt, ', ') within group (order by era.charge_cpt) as cpts_billed
from era_matched_adjustment era
  join appointment a on a.id = era.appointment_id
where era.match_confidence = 'provider_match'
  and a.appt_time >= '2026-01-01'
group by era.appointment_id, a.appt_time, a.appt_type, patient_name, era.practice_id, era.canonical_physician_id
order by a.appt_time;

Per-provider revenue

Joins ERA rows to canonical_physician for human-readable provider attribution.

select
    cp.id as canonical_physician_id
  , concat(cp.first_name, ' ', cp.last_name, coalesce(', ' || cp.credentials, '')) as provider
  , era.practice_id
  , count(distinct era.claim_id) as claims
  , count(distinct era.claimmd_charge_id) as charges
  , sum(era.charge_amount) as total_charged
  , sum(era.charge_paid) as total_paid
  , sum(era.adjustment_amount) as total_adjustments
from era_matched_adjustment era
  join canonical_physician cp on cp.id = era.canonical_physician_id
where era.charge_service_date >= dateadd(day, -90, current_date)
group by cp.id, provider, era.practice_id
order by total_paid desc;

Single-claim drill-down

Returns every ERA line for one claim, ordered by service date and charge.

select
    era.claim_local_id
  , era.claim_id
  , era.claimmd_charge_id
  , era.charge_service_date
  , era.charge_cpt
  , era.charge_amount
  , era.charge_allowed
  , era.charge_paid
  , era.adjustment_group
  , era.adjustment_code
  , era.adjustment_amount
  , era.is_denied
  , era.claim_status_code
  , era.check_number
  , era.claimmd_payer_id
  , era.claim_payer_icn
from era_matched_adjustment era
where era.claim_id = 1234567890
order by era.charge_service_date, era.claimmd_charge_id;

CPT denial breakdown

Identifies the procedure codes most often denied, with the dominant adjustment reason for each.

with denied as (
    select
        era.charge_cpt
      , era.adjustment_group
      , era.adjustment_code
      , count(*) as denial_count
      , sum(era.charge_amount) as denied_charge_amount
    from era_matched_adjustment era
    where era.is_denied = true
      and era.charge_service_date >= dateadd(day, -90, current_date)
    group by 1, 2, 3
)
select
    charge_cpt
  , sum(denial_count) as total_denials
  , sum(denied_charge_amount) as total_denied_amount
  , listagg(adjustment_group || '/' || adjustment_code, ', ')
      within group (order by denial_count desc) as top_denial_reasons
from denied
group by charge_cpt
order by total_denials desc
limit 25;

If you have any questions about this topic please reach out to Elation Support Portal with the subject line HDB - <your_question>

Hosted Database

Overview

How Tos

Common Queries

Elation Billing

Frequently Asked Questions

Changelog

Charges and adjustments for a date range

Practice-level totals over a period

Per-appointment revenue

Per-provider revenue

Single-claim drill-down

CPT denial breakdown

​Charges and adjustments for a date range

​Practice-level totals over a period

​Per-appointment revenue

​Per-provider revenue

​Single-claim drill-down

​CPT denial breakdown

Charges and adjustments for a date range

Practice-level totals over a period

Per-appointment revenue

Per-provider revenue

Single-claim drill-down

CPT denial breakdown