> ## 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.
The table schema can be found in our [HDB dbdocs reference](https://dbdocs.io/hosteddb_support/hosted_database_snowflake?view=era_matched_adjustment).
**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.
```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.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.
```sql sql theme={null}
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.
```sql sql theme={null}
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.
```sql sql theme={null}
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.
```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 - \*