One row per Elation Billing claim - the bill submitted to a payer for the services on a patient visit. Each claim links to its patient, practice, service location, and the EHR-side bill it corresponds to. Provider data is denormalized: for each of the five roles (rendering, referring, supervising, billing, ordering) the row carries the provider’s name and NPI directly.
The table schema can be found in our HDB dbdocs reference.
For reporting on but not limited to:
- All claims for a patient, practice, or date range
- Claims that have not yet been submitted to a payer (
is_billed = false)
- Original vs. corrected vs. voided submissions via
icn_code
- Per-provider claim volume by joining on the rendering or billing NPI
- Reconciling Elation Billing claims to EHR bills via
bill_id
- Drilling into a single claim by
id or local_id
claim.bill_id links the Elation Billing claim back to the EHR-side bill table. It is empty when the claim has no matching bill in HDB - typically claims created directly in Elation Billing without an EHR bill, or claims for warehouses where the bill side is not yet crosswalked.
Claims for a date range
Pulls every claim with a service-date window, with payer-facing detail and the provider who rendered the service.
select
c.id as claim_id
, c.local_id
, c.patient_id
, c.practice_id
, c.service_location_id
, c.location_code
, c.from_date
, c.through_date
, c.rendering_provider_name
, c.rendering_provider_npi
, c.billing_provider_name
, c.billing_provider_npi
, c.is_billed
, c.accept_assign
, c.claimmd_id
, c.icn
, c.icn_code
, c.co_pay_paid
from claim c
where c.is_deleted = false
and c.from_date between '2026-01-01' and '2026-03-31'
order by c.from_date, c.id;
Unbilled claims
Returns claims that have not been submitted to a payer yet, oldest first. Useful for a “what’s stuck in pre-submission” worklist.
select
c.id as claim_id
, c.local_id
, c.patient_id
, c.practice_id
, c.from_date
, c.through_date
, c.rendering_provider_name
, c.billing_provider_name
, c.worklist
, c.narrative
, c.created_timestamp
, c.updated_timestamp
from claim c
where c.is_deleted = false
and c.is_billed = false
order by c.from_date;
Corrected and voided submissions
Filters to claims that were resubmitted as corrections or voids. icn_code carries the resubmission type (1 = original, 7 = corrected, 8 = void); icn carries the payer’s original control number that the resubmission references.
select
c.id as claim_id
, c.local_id
, c.patient_id
, c.from_date
, c.through_date
, c.icn
, case c.icn_code
when 1 then 'original'
when 7 then 'corrected'
when 8 then 'void'
else 'other'
end as resubmission_type
, c.claimmd_id
, c.billing_provider_name
, c.updated_timestamp
from claim c
where c.is_deleted = false
and c.icn_code in (7, 8)
order by c.updated_timestamp desc;
Claim volume per rendering provider
Aggregates claims and copay collected per rendering provider over the last 90 service days. Filters out deleted claims and rows with no rendering provider attached.
select
c.rendering_provider_npi
, c.rendering_provider_name
, c.practice_id
, count(*) as claim_count
, count(distinct c.patient_id) as unique_patients
, sum(c.co_pay_paid) as total_copay_collected
, sum(iff(c.is_billed, 1, 0)) as claims_submitted
, sum(iff(c.is_billed, 0, 1)) as claims_pending
from claim c
where c.is_deleted = false
and c.rendering_provider_npi is not null
and c.from_date >= dateadd(day, -90, current_date)
group by c.rendering_provider_npi, c.rendering_provider_name, c.practice_id
order by claim_count desc;
Reconcile a claim with its EHR bill
Joins a single claim to the EHR-side bill table to compare what the practice billed against what was submitted to the payer.
The claim table lives in your elation_billing schema while the bill table lives in your standard schema. Either set your default schema or qualify both tables with their schema names when running this query.
select
c.id as claim_id
, c.local_id
, c.bill_id
, c.patient_id
, c.practice_id
, c.from_date as claim_from_date
, c.through_date as claim_through_date
, c.rendering_provider_name
, c.billing_provider_name
, c.is_billed
, c.claimmd_id
, b.id as ehr_bill_id
, b.billing_date as ehr_billing_date
, b.billing_status as ehr_billing_status
, b.notes as ehr_bill_notes
from claim c
left join bill b on b.id = c.bill_id
where c.is_deleted = false
and c.id = 1234567890; -- Replace with actual claim ID
Claims billed against an authorization
Lists the claims attached to a specific prior authorization. The join also returns claim-level service dates so you can see when the authorization was actually used.
select
a.id as authorization_id
, a.auth_num
, a.payer_name
, a.auth_units
, a.starts as auth_starts
, a.expires as auth_expires
, c.id as claim_id
, c.local_id
, c.from_date
, c.through_date
, c.rendering_provider_name
, c.is_billed
from authorization a
join claim c on c.authorization_id = a.id
where a.is_deleted = false
and a.id = 12345 -- Replace with actual authorization ID
order by c.from_date;
