> ## 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.

# Payment

> Query insurance and patient payments recorded in Elation Billing - amounts, dates, and how they apply to claims.

One row per payment recorded in Elation Billing, from both insurance payers and patients. Each payment carries its amount, dates, type and method, and how much has been applied to charges. Insurance payments have no EHR equivalent, so `patient_id` is often empty.

This table's columns and relationships are shown in the [Hosted Database schema](/articles/hdb/schema).

<Note>
  **`claim_id` is populated on patient payments only, and is always empty on payer payments.** This is by design rather than a gap in the data: one payer payment routinely settles many claims at once, so Elation Billing attributes payer payments to claims through [`financial_transaction`](/articles/hdb/financial-transaction) instead. Joining `payment` to `claim` on `claim_id` therefore returns patient payments alone. Use `financial_transaction` to attribute payer payments to claims.

  **`claimmd_era_id` is populated on payer payments only, and reaches [`era_matched_adjustment`](/articles/hdb/era-matched-adjustment) one-to-many.** A single ERA covers every charge the payer adjudicated, so one payment matches an average of roughly 26 adjustment rows and several thousand in the worst case. Aggregate `era_matched_adjustment` first and join the result, rather than joining row for row, or the payment amounts repeat once per adjustment line and any total built on them is inflated.
</Note>

**For reporting on but not limited to:**

* Total payments received by practice or date
* Insurance vs patient payments (`pay_type`)
* Applied vs unapplied payment amounts
* Co-pays (`is_copay`)
* Claim-level attribution of payer payments, through `financial_transaction`
* The notes recorded against a payment, and who wrote them, by joining [payment\_note](/articles/hdb/payment-note) on `payment_id`

## Payments by practice and month

<CodeGroup>
  ```sql sql theme={null}
  select
      p.practice_id
    , pr.name as practice_name
    , date_trunc('month', p.payment_date) as payment_month
    , count(*) as payments
    , sum(p.amount) as total_amount
  from payment p
    left join practice pr on pr.id = p.practice_id
  where p.is_deleted = false
  group by p.practice_id, pr.name, payment_month
  order by payment_month desc;
  ```
</CodeGroup>

## Unapplied payment balances

<CodeGroup>
  ```sql sql theme={null}
  select
      p.practice_id
    , pr.name as practice_name
    , p.id as payment_id
    , p.amount
    , p.applied_amount
    , p.unapplied_amount
  from payment p
    left join practice pr on pr.id = p.practice_id
  where p.is_deleted = false
    and p.unapplied_amount > 0
  order by p.unapplied_amount desc;
  ```
</CodeGroup>

## Payments with their ERA adjustment totals

Summarizes the ERA behind each payer payment. The adjustment detail is rolled up to one row per ERA and practice *before* the join, which keeps the result at one row per payment and leaves `amount` safe to total. Only `adjustment_amount` is summed, because it is the one money column that is genuinely per-adjustment-line.

<CodeGroup>
  ```sql sql theme={null}
  with era_totals as (
      select
          e.claimmd_era_id
        , e.practice_id
        , count(*) as adjustment_lines
        , count(distinct e.claim_id) as claims_on_era
        , count(distinct e.claimmd_charge_id) as charges_on_era
        , sum(e.adjustment_amount) as total_adjustments
      from era_matched_adjustment e
      group by e.claimmd_era_id, e.practice_id
  )
  select
      p.id as payment_id
    , p.practice_id
    , pr.name as practice_name
    , p.payment_date
    , p.amount
    , p.applied_amount
    , t.claims_on_era
    , t.charges_on_era
    , t.adjustment_lines
    , t.total_adjustments
  from payment p
    left join era_totals t
      on t.claimmd_era_id = p.claimmd_era_id
     and t.practice_id = p.practice_id
    left join practice pr on pr.id = p.practice_id
  where p.is_deleted = false
    and p.claimmd_era_id is not null
  order by p.payment_date desc;
  ```
</CodeGroup>

## Payer payments attributed to claims

Splits each payer payment across the claims it settled, using `financial_transaction` as the bridge. This is the route to claim-level attribution for payer payments, since their `claim_id` is always empty.

Note the `type = 'payment'` filter. `financial_transaction` also carries `adjustment`, `allowed`, and `balance_transfer` rows against the same claim, so totaling `amount` without filtering on type mixes write-offs and contracted-rate entries in with the money actually received, and the result can exceed the payment itself.

`patient_name` is null wherever `financial_transaction.patient_id` has no matching `patient` record. A filter on `patient_name` excludes those rows.

<CodeGroup>
  ```sql sql theme={null}
  select
      p.id as payment_id
    , p.payment_date
    , p.amount as payment_total
    , ft.claim_id
    , c.local_id as claim_local_id
    , concat(pat.first_name, ' ', pat.last_name) as patient_name
    , sum(ft.amount) as amount_applied_to_claim
  from payment p
    join financial_transaction ft on ft.payment_id = p.id
    left join claim c on c.id = ft.claim_id
    left join patient pat on pat.id = ft.patient_id
  where p.is_deleted = false
    and ft.is_deleted = false
    and ft.type = 'payment'
    and ft.claim_id is not null
  group by p.id, p.payment_date, p.amount, ft.claim_id, c.local_id, patient_name
  order by p.payment_date desc, amount_applied_to_claim desc;
  ```
</CodeGroup>

*If you have any questions about this topic please reach out to [Elation Support Portal](/articles/support-portal-introduction) with the subject line HDB - \<your\_question>*