Skip to main content

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 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.
The era_matched_adjustment table lives in your elation_billing schema while the appointment table lives in your standard schema. Either set your default schema or qualify both tables with their schema names when running this query.
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>