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

# Patients

> Common patient queries in the Hosted Database, and the behaviors to know before you count or filter 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](/articles/hdb/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

<CodeGroup>
  ```sql Active patients with primary physician theme={null}
  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;
  ```
</CodeGroup>

## Patient counts by status

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

* Panel health over time
* How many patients have no status set

<CodeGroup>
  ```sql Patients by status and inactive reason theme={null}
  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;
  ```
</CodeGroup>

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

<CodeGroup>
  ```sql Active panel per primary physician theme={null}
  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;
  ```
</CodeGroup>

<Note>
  **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](/articles/hdb/connecting-to-patient-matching-sftp-service) for how to send the file.
</Note>

*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>*