Patients

The patient table has one row per patient chart. It holds demographics, contact details, status, and the patient’s primary physician. Most other tables link back to it through patient_id. This table’s columns and relationships are shown in the Hosted Database schema. For reporting on but not limited to:

Active patient rosters
Panel size by primary physician
Patient counts by status
Demographic breakdowns of your patient population

Before you query patients

Demo patients are never included

The Hosted Database excludes patients marked as demo or test patients in Elation. Their charts, visit notes, bills, and other records never reach your database, even though you can see them in the EHR. If a practice has only demo patients, the practice appears in the practice table but has no patients or clinical data. Data starts flowing on the next refresh after real patients are added.

Deleted patients are kept

Deleted patient charts remain in the table with deletion_time set. Add deletion_time is null to any query that should match the charts currently visible in Elation.

Patient status can be null

patient_status is one of active, inactive, prospect, or deceased. Patients who never had a status set have a null patient_status. Decide whether null belongs with active patients for your report, and handle it explicitly. A filter on patient_status = 'active' alone drops these patients. deceased_date is only populated when the practice entered a date. Use patient_status = 'deceased' to find deceased patients.

One person can have more than one chart

A person can have more than one patient chart, each with its own id. This happens when they are seen at more than one practice in your enterprise, or when a duplicate chart was created. Elation links charts it knows belong to the same person with a shared master_id. master_id is null for most patients. Counting distinct master_id values undercounts badly. Count coalesce(master_id, id) instead: each linked group of charts counts once, and every unlinked chart counts as its own person.

Primary physician versus primary care provider

primary_physician_user_id is the patient’s primary physician in Elation and links to the user table. It is populated for every patient. primary_care_provider_id and primary_care_provider_npi hold an optional external primary care provider and are usually null. Use primary_physician_user_id for panel and attribution reporting.

Active patient roster

For reporting on but not limited to:

Current patient lists with contact details
Outreach lists

select
    p.id as "PATIENT_ID"
  , p.first_name
  , p.last_name
  , p.dob
  , p.sex
  , p.email
  , p.city
  , p.state
  , p.zip
  , coalesce(p.patient_status, 'no status') as "PATIENT_STATUS"
  , concat(u.first_name, ' ', u.last_name) as "PRIMARY_PHYSICIAN"
from patient p
  left join user u on u.id = p.primary_physician_user_id
where p.deletion_time is null
  and (p.patient_status = 'active' or p.patient_status is null)
order by p.last_name, p.first_name;

Patient counts by status

For reporting on but not limited to:

Panel health over time
How many patients have no status set

select
    coalesce(p.patient_status, 'no status') as "PATIENT_STATUS"
  , p.inactive_reason
  , count(*) as "PATIENTS"
from patient p
where p.deletion_time is null
group by all
order by "PATIENTS" desc;

Panel size by primary physician

For reporting on but not limited to:

Panel size per provider
Unique people per provider when patients have charts at several practices

select
    u.id as "USER_ID"
  , concat(u.first_name, ' ', u.last_name) as "PRIMARY_PHYSICIAN"
  , count(*) as "PATIENT_CHARTS"
  , count(distinct coalesce(p.master_id, p.id)) as "UNIQUE_PEOPLE"
from patient p
  join user u on u.id = p.primary_physician_user_id
where p.deletion_time is null
  and (p.patient_status = 'active' or p.patient_status is null)
group by all
order by "PATIENT_CHARTS" desc;

Patient attribution. Some warehouses include only a subset of a practice’s patients, based on a patient list you send to Elation. Elation matches that list to patient charts, and only matched patients appear in your database. If a patient you expect is missing, check that they were in your most recent file. See Connecting to Patient Matching SFTP Service for how to send the file.

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 patients

Demo patients are never included

Deleted patients are kept

Patient status can be null

One person can have more than one chart

Primary physician versus primary care provider

Active patient roster

Patient counts by status

Panel size by primary physician

​Before you query patients

​Demo patients are never included

​Deleted patients are kept

​Patient status can be null

​One person can have more than one chart

​Primary physician versus primary care provider

​Active patient roster

​Patient counts by status

​Panel size by primary physician

Before you query patients

Demo patients are never included

Deleted patients are kept

Patient status can be null

One person can have more than one chart

Primary physician versus primary care provider

Active patient roster

Patient counts by status

Panel size by primary physician