Patient insurance

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. 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). 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 query below shows this.

If your practices use Elation Billing, Patient Insurance Eligibility also carries coverage_start and coverage_end for each policy, usually more completely, along with the latest eligibility status.

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.

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;

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.

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;

Coverage date completeness

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

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;

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

Early Release

Changelog

Before you query patient insurance

Historical policies are kept

Rank

Coverage dates are often empty

Current insurance for each patient

Coverage in effect on the date of service

Coverage date completeness

​Before you query patient insurance

​Historical policies are kept

​Rank

​Coverage dates are often empty

​Current insurance for each patient

​Coverage in effect on the date of service

​Coverage date completeness

Before you query patient insurance

Historical policies are kept

Rank

Coverage dates are often empty

Current insurance for each patient

Coverage in effect on the date of service

Coverage date completeness