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

# Patient insurance

> A patient's insurance policies as recorded on the chart, including historical policies and coverage dates.

The `patient_insurance` table has one row per insurance policy recorded on a patient's chart in Elation. It holds the carrier and plan, member and group IDs, the policy's rank, copay and deductible, the coverage dates, and the details of the insured person when that is not the patient. Join to `patient` on `patient_id`.

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

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

* Each patient's current primary, secondary, and tertiary insurance
* Patient counts by carrier or plan
* Matching the coverage in effect to a date of service
* Insurance history for a patient

## Before you query patient insurance

### Historical policies are kept

When a policy is replaced on the chart, the old one moves to the chart's **Historical Insurance Records** section (see [Insurance Management](/articles/Patient-Demographics-Insurance-Management)). The Hosted Database keeps it with `is_deleted = true`. A policy removed from the chart with **Delete This Insurance** is also kept with `is_deleted = true`, and the two cannot be told apart.

Filter on `is_deleted = false` for the policies shown as active on the chart. Leave the filter off when you need the policies that were in effect at an earlier date.

### Rank

`rank` is `1` for primary, `2` for secondary, and `3` for tertiary insurance. A small number of policies carry other values. A patient can have more than one active policy at the same rank, so pick one explicitly, for example the most recently modified, when you need a single primary policy per patient.

### Coverage dates are often empty

`coverage_start` and `coverage_end` are the **Effective Date** and **End Date** entered on the policy. Many policies have neither, and an empty `coverage_end` usually means no end date was entered rather than coverage that is known to be ongoing. Check how complete the dates are for your practices before relying on them. The [coverage date completeness](#coverage-date-completeness) query below shows this.

<Note>
  If your practices use Elation Billing, [Patient Insurance Eligibility](/articles/hdb/patient-insurance-eligibility) also carries `coverage_start` and `coverage_end` for each policy, usually more completely, along with the latest eligibility status.
</Note>

## Current insurance for each patient

Returns the active primary, secondary, and tertiary policy for each patient, taking the most recently modified policy where a patient has more than one at the same rank.

<CodeGroup>
  ```sql sql theme={null}
  select
      i.patient_id
    , concat(pat.first_name, ' ', pat.last_name) as patient_name
    , i.rank
    , i.carrier
    , i.plan
    , i.member_id
    , i.group_id
    , i.coverage_start
    , i.coverage_end
  from patient_insurance i
    left join patient pat on pat.id = i.patient_id
  where i.is_deleted = false
    and i.rank in (1, 2, 3)
  qualify row_number() over (
      partition by i.patient_id, i.rank
      order by i.last_modified desc
  ) = 1
  order by patient_name, i.rank;
  ```
</CodeGroup>

## Coverage in effect on the date of service

Matches each bill to the patient's policies whose coverage dates include the date of the visit. Historical policies are included, because the policy in effect at the time of an older visit may since have been replaced. Bills whose patient has no policy with a matching coverage window are left out, which is common where coverage dates were not entered.

<CodeGroup>
  ```sql sql theme={null}
  select
      b.id as bill_id
    , v.patient_id
    , concat(pat.first_name, ' ', pat.last_name) as patient_name
    , v.document_date::date as date_of_service
    , i.rank
    , i.carrier
    , i.plan
    , i.member_id
    , i.coverage_start
    , i.coverage_end
    , i.is_deleted as is_historical_or_deleted
  from bill b
    join visit_note v on v.id = b.visit_note_id
    left join patient pat on pat.id = v.patient_id
    join patient_insurance i
      on i.patient_id = v.patient_id
     and v.document_date::date >= i.coverage_start
     and (i.coverage_end is null or v.document_date::date <= i.coverage_end)
  where b.is_deleted = false
    and v.deleted_by_user_id is null
    and v.document_date >= '2026-01-01'
  order by date_of_service, b.id, i.rank;
  ```
</CodeGroup>

## Coverage date completeness

Shows, for each practice, how many active policies have a coverage start date and an end date.

<CodeGroup>
  ```sql sql theme={null}
  select
      pat.practice_id
    , pr.name as practice_name
    , count(*) as active_policies
    , count(i.coverage_start) as with_coverage_start
    , count(i.coverage_end) as with_coverage_end
    , round(100 * count(i.coverage_start) / count(*), 1) as pct_with_coverage_start
  from patient_insurance i
    join patient pat on pat.id = i.patient_id
    left join practice pr on pr.id = pat.practice_id
  where i.is_deleted = false
  group by pat.practice_id, pr.name
  order by active_policies 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>*