Claim

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
The billing legal entity each claim was submitted under via legal_entity_id
Whether a claim is on a biller worklist, and why, by joining worklist / worklist_reason on claim_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.

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;

Legal entity a claim was billed under

Joins each claim to the legal_entity that carries the tax id, NPI, and billing name it was submitted under.

select
    c.id as claim_id
  , c.local_id
  , c.patient_id
  , c.from_date
  , c.is_billed
  , le.id as legal_entity_id
  , le.name as legal_entity_name
  , le.tin
  , le.npi as legal_entity_npi
from claim c
  left join legal_entity le on le.id = c.legal_entity_id
where c.is_deleted = false
  and c.from_date >= dateadd(day, -90, current_date)
order by c.from_date, c.id;

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

Changelog

Claims for a date range

Unbilled claims

Corrected and voided submissions

Claim volume per rendering provider

Reconcile a claim with its EHR bill

Claims billed against an authorization

Legal entity a claim was billed under

​Claims for a date range

​Unbilled claims

​Corrected and voided submissions

​Claim volume per rendering provider

​Reconcile a claim with its EHR bill

​Claims billed against an authorization

​Legal entity a claim was billed under

Claims for a date range

Unbilled claims

Corrected and voided submissions

Claim volume per rendering provider

Reconcile a claim with its EHR bill

Claims billed against an authorization

Legal entity a claim was billed under