Charge DX

One row per diagnosis code per Elation Billing charge line, ordered by sequence_number. This is the Elation Billing counterpart to bill_item_dx on the Elation EHR side. There is no id column. Elation Billing holds no single key for these rows, so a row is identified by its charge, diagnosis code, and sequence together - all three parts are needed, because neither charge and sequence nor charge and code is unique on its own. This table’s columns and relationships are shown in the Hosted Database schema.

dx holds the ICD-10 code with the decimal point removed: I10, E785 for E78.5, Z0000 for Z00.00. This matches the format used by bill_item_dx.dx, so it does not join directly to icd10.code without reinstating the decimal point.

sequence_number sets the order the diagnosis codes appear in on the claim and starts at 1, though a small number of rows carry 0. is_deleted rolls up the parent charge, so the diagnosis lines of a deleted charge are flagged deleted too.

For reporting on but not limited to:

Which diagnoses were billed, and how often
The primary diagnosis on each charge line
Diagnosis mix by practice or by CPT code
Charge lines carrying more diagnosis codes than expected
Reconciling billed diagnoses against the Elation EHR bill

Diagnosis codes on each charge line

select
    c.id as charge_id
  , pt.full_name
  , c.cpt
  , c.from_date
  , cd.sequence_number
  , cd.dx
from charge_dx cd
  inner join charge c on c.id = cd.charge_id
  left join patient pt on pt.id = c.patient_id
where not cd.is_deleted
order by c.id, cd.sequence_number;

Most frequently billed diagnosis codes

select
    cd.dx
  , count(*) as charge_line_count
  , count(distinct cd.charge_id) as charge_count
from charge_dx cd
where not cd.is_deleted
  and cd.dx is not null
group by cd.dx
order by charge_line_count desc;

Primary diagnosis by practice

select
    p.name as practice_name
  , cd.dx
  , count(*) as charge_line_count
from charge_dx cd
  left join practice p on p.id = cd.practice_id
where not cd.is_deleted
  and cd.sequence_number = 1
group by p.name, cd.dx
order by p.name, charge_line_count desc;

Charge lines carrying several diagnosis codes

select
    c.id as charge_id
  , pt.full_name
  , c.cpt
  , count(*) as diagnosis_count
  , listagg(cd.dx, ', ') within group (order by cd.sequence_number) as diagnoses
from charge_dx cd
  inner join charge c on c.id = cd.charge_id
  left join patient pt on pt.id = c.patient_id
where not cd.is_deleted
group by c.id, pt.full_name, c.cpt
having count(*) > 3
order by diagnosis_count 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

Diagnosis codes on each charge line

Most frequently billed diagnosis codes

Primary diagnosis by practice

Charge lines carrying several diagnosis codes

​Diagnosis codes on each charge line

​Most frequently billed diagnosis codes

​Primary diagnosis by practice

​Charge lines carrying several diagnosis codes

Diagnosis codes on each charge line

Most frequently billed diagnosis codes

Primary diagnosis by practice

Charge lines carrying several diagnosis codes