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. This table’s columns and relationships are shown in the Hosted Database schema.

Read this before totaling any money column. One row is one adjustment line, but the money columns are recorded at three different levels of granularity, so adding them up without reducing to the right level double-counts.

Column	Recorded per	How to total it
`adjustment_amount`	Adjustment line	Sum directly. Already at row-level granularity.
`charge_allowed`, `charge_paid`	ERA and charge	Reduce to one row per `claimmd_era_id` + `claimmd_charge_id` first, then sum. A charge adjudicated by a primary and a secondary payer has a separate allowed and paid figure from each.
`charge_amount`, `charge_cpt`	Charge	Reduce to one row per `claimmd_charge_id` first. These describe the charge itself and repeat on every ERA that adjudicated it.

Duplication at both levels is common rather than exceptional: a large share of charges carry more than one adjustment line, and a meaningful minority are adjudicated in more than one ERA. Summing charge_paid or charge_amount straight off the table can therefore overstate them by close to 2x. The example queries below all reduce to the correct level first.

For reporting on but not limited to:

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

Two payment-date columns sit on this table and they answer different questions.

era_payment_date is the date the payer remitted this ERA. Use this one to measure payer payment timing. It is null when Elation Billing holds no posted payment for the ERA.
claim_latest_payment_date is the date of the most recent payment of any kind on the claim, patient payments taken at the time of service included. It is claim-level rather than charge-level, so the same value repeats across every row of the same claim.

Because claim_latest_payment_date spans every payment source, it can fall later than era_payment_date. On a small share of rows it falls earlier, where Elation Billing records no transaction tying this ERA’s payment to the claim.

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.era_payment_date
  , era.claim_latest_payment_date
  , era.charge_balance
  , era.charge_balance_responsibility
  , era.provider_npi
  , 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. The era_charge step reduces each charge to one row per ERA, which is the level the payer figures are recorded at. Because the query splits by payer, a charge adjudicated by both a primary and a secondary payer contributes to each of them, so total_charged_to_payer reads as the amount presented to that payer rather than a count of distinct money billed. Use the per-provider query below when you want each charge counted once.

with era_charge as (
    select
        era.claimmd_era_id
      , era.claimmd_charge_id
      , max(era.practice_id) as practice_id
      , max(era.claimmd_payer_id) as claimmd_payer_id
      , max(era.claim_id) as claim_id
      , max(era.charge_amount) as charge_amount
      , max(era.charge_allowed) as charge_allowed
      , max(era.charge_paid) as charge_paid
      , max(iff(era.is_denied, 1, 0)) as is_denied
      , sum(era.adjustment_amount) as adjustment_amount
    from era_matched_adjustment era
    where era.charge_service_date between '2026-01-01' and '2026-03-31'
    group by era.claimmd_era_id, era.claimmd_charge_id
)
select
    c.practice_id
  , p.name as practice_name
  , c.claimmd_payer_id
  , count(distinct c.claim_id) as claims
  , count(distinct c.claimmd_charge_id) as charges
  , sum(c.charge_amount) as total_charged_to_payer
  , sum(c.charge_allowed) as total_allowed
  , sum(c.charge_paid) as total_paid
  , sum(c.adjustment_amount) as total_adjustments
  , sum(c.is_denied) as denied_charges
  , sum(iff(c.is_denied = 1, c.charge_amount, 0)) as denied_charge_amount
  , round(100.0 * sum(c.is_denied) / nullif(count(*), 0), 2) as denial_rate_pct
from era_charge c
  left join practice p on p.id = c.practice_id
group by c.practice_id, p.name, c.claimmd_payer_id
order by c.practice_id, total_paid desc;

Payer remittance timing

Measures how long each payer takes to remit, from date of service to the date the payer paid. This uses era_payment_date, the payer’s own remittance date. Using claim_latest_payment_date here would understate the lag wherever a patient co-pay was collected at the visit, because that column takes the latest payment of any kind on the claim. The era_charge step applies here too, even though this query totals no money. Both dates in the lag are fixed for a given charge and ERA, so averaging over raw rows would weight each charge by its number of adjustment lines. Reducing first makes avg_days_to_remit the mean lag per charge adjudicated, matching the charges count beside it.

with era_charge as (
    select
        era.claimmd_era_id
      , era.claimmd_charge_id
      , max(era.practice_id) as practice_id
      , max(era.claimmd_payer_id) as claimmd_payer_id
      , max(era.claim_id) as claim_id
      , max(era.charge_service_date) as charge_service_date
      , max(era.era_payment_date) as era_payment_date
    from era_matched_adjustment era
    where era.era_payment_date is not null
      and era.charge_service_date >= dateadd(month, -12, current_date)
    group by era.claimmd_era_id, era.claimmd_charge_id
)
select
    c.practice_id
  , p.name as practice_name
  , c.claimmd_payer_id
  , count(distinct c.claimmd_era_id) as eras
  , count(distinct c.claim_id) as claims
  , count(distinct c.claimmd_charge_id) as charges
  , round(avg(datediff(day, c.charge_service_date, c.era_payment_date)), 1) as avg_days_to_remit
  , max(datediff(day, c.charge_service_date, c.era_payment_date)) as max_days_to_remit
from era_charge c
  left join practice p on p.id = c.practice_id
group by c.practice_id, p.name, c.claimmd_payer_id
order by avg_days_to_remit 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. Two reduction steps run before the totals are taken: era_charge collapses the adjustment lines of each charge, then charge collapses the ERAs so each charge is counted once. charge_paid is summed across ERAs, because a primary and a secondary payer each contribute money; charge_amount is taken once, because it describes the charge.

with era_charge as (
    select
        era.claimmd_era_id
      , era.claimmd_charge_id
      , max(era.appointment_id) as appointment_id
      , max(era.practice_id) as practice_id
      , max(era.provider_npi) as provider_npi
      , max(era.claim_id) as claim_id
      , max(era.claim_patient_first_name) as patient_first_name
      , max(era.claim_patient_last_name) as patient_last_name
      , max(era.charge_cpt) as charge_cpt
      , max(era.charge_amount) as charge_amount
      , max(era.charge_paid) as charge_paid
      , sum(era.adjustment_amount) as adjustment_amount
    from era_matched_adjustment era
    where era.match_confidence = 'provider_match'
    group by era.claimmd_era_id, era.claimmd_charge_id
)
, charge as (
    select
        claimmd_charge_id
      , max(appointment_id) as appointment_id
      , max(practice_id) as practice_id
      , max(provider_npi) as provider_npi
      , max(claim_id) as claim_id
      , max(patient_first_name) as patient_first_name
      , max(patient_last_name) as patient_last_name
      , max(charge_cpt) as charge_cpt
      , max(charge_amount) as charge_amount
      , sum(charge_paid) as charge_paid
      , sum(adjustment_amount) as adjustment_amount
    from era_charge
    group by claimmd_charge_id
)
select
    c.appointment_id
  , a.appt_time
  , a.appt_type
  , concat(c.patient_first_name, ' ', c.patient_last_name) as patient_name
  , c.practice_id
  , p.name as practice_name
  , c.provider_npi
  , count(distinct c.claim_id) as claims
  , count(*) as charges
  , sum(c.charge_amount) as total_charged
  , sum(c.charge_paid) as total_paid
  , sum(c.adjustment_amount) as total_adjustments
  , listagg(distinct c.charge_cpt, ', ') within group (order by c.charge_cpt) as cpts_billed
from charge c
  join appointment a on a.id = c.appointment_id
  left join practice p on p.id = c.practice_id
where a.appt_time >= '2026-01-01'
group by c.appointment_id, a.appt_time, a.appt_type, patient_name, c.practice_id, p.name, c.provider_npi
order by a.appt_time;

Per-provider revenue

Attributes charges to the billing provider. ERA rows identify the provider by NPI, so the join to canonical_physician goes through npi rather than an id. Uses the same two reduction steps as the per-appointment query, so each charge is counted once no matter how many adjustment lines or ERAs it carries. The join is a LEFT join on purpose. Not every NPI on an ERA belongs to a physician in your own records, since claims can be billed under organizational or external NPIs. provider_name and specialty are null on those rows, while provider_npi is still populated.

with era_charge as (
    select
        era.claimmd_era_id
      , era.claimmd_charge_id
      , max(era.provider_npi) as provider_npi
      , max(era.practice_id) as practice_id
      , max(era.claim_id) as claim_id
      , max(era.charge_amount) as charge_amount
      , max(era.charge_paid) as charge_paid
      , sum(era.adjustment_amount) as adjustment_amount
    from era_matched_adjustment era
    where era.charge_service_date >= dateadd(day, -90, current_date)
    group by era.claimmd_era_id, era.claimmd_charge_id
)
, charge as (
    select
        claimmd_charge_id
      , max(provider_npi) as provider_npi
      , max(practice_id) as practice_id
      , max(claim_id) as claim_id
      , max(charge_amount) as charge_amount
      , sum(charge_paid) as charge_paid
      , sum(adjustment_amount) as adjustment_amount
    from era_charge
    group by claimmd_charge_id
)
select
    c.provider_npi
  , concat(cp.first_name, ' ', cp.last_name, coalesce(', ' || cp.credentials, '')) as provider_name
  , cp.specialty
  , c.practice_id
  , p.name as practice_name
  , count(distinct c.claim_id) as claims
  , count(*) as charges
  , sum(c.charge_amount) as total_charged
  , sum(c.charge_paid) as total_paid
  , sum(c.adjustment_amount) as total_adjustments
from charge c
  left join canonical_physician cp on cp.npi = c.provider_npi
  left join practice p on p.id = c.practice_id
group by c.provider_npi, provider_name, cp.specialty, c.practice_id, p.name
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

Early Release

Changelog

Charges and adjustments for a date range

Practice-level totals over a period

Payer remittance timing

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

​Payer remittance timing

​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

Payer remittance timing

Per-appointment revenue

Per-provider revenue

Single-claim drill-down

CPT denial breakdown