> ## Documentation Index
> Fetch the complete documentation index at: https://help.elationhealth.com/llms.txt
> Use this file to discover all available pages before exploring further.
# ERA Matched Adjustment
> Query electronic remittance advice charge-level adjustments to analyze payments, denials, and adjustments for Elation Billing customers.
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](/articles/hdb/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.
```sql sql theme={null}
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.
```sql sql theme={null}
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.
```sql sql theme={null}
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.
```sql sql theme={null}
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.
```sql sql theme={null}
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.
```sql sql theme={null}
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.
```sql sql theme={null}
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](/articles/support-portal-introduction) with the subject line HDB - \*