Authorization

Prior authorizations issued by payers to patients at a practice. One row per authorization, including the payer, authorization number, number of authorized units, period start and expire dates, an active flag, and any free-text notes. Referenced by claim.authorization_id. The table schema can be found in our HDB dbdocs reference. For reporting on but not limited to:

Currently active authorizations for a patient or practice
Authorizations expiring soon that may need renewal
How many claims have been billed against an authorization
Authorization counts and authorized-unit totals by payer

The authorization table does not store a “units used” counter. To see how an authorization has been consumed, join to claim on claim.authorization_id. The claim.authorization_id field is only populated when staff explicitly linked the authorization on the claim, so untagged claims will not appear in the join.

Active authorizations

Returns authorizations the practice has flagged active that have not been soft-deleted.

select
    auth.id as authorization_id
  , auth.patient_id
  , auth.practice_id
  , auth.payer_name
  , auth.auth_num
  , auth.auth_units
  , auth.starts
  , auth.expires
  , auth.auth_note
  , auth.creation_time_utc
  , auth.last_modified_utc
from authorization auth
where auth.is_deleted = false
  and auth.active = 1
order by auth.expires;

Authorizations expiring soon

Identifies authorizations expiring within the next 30 days. Useful for proactively requesting renewals.

select
    auth.id as authorization_id
  , auth.patient_id
  , auth.practice_id
  , auth.payer_name
  , auth.auth_num
  , auth.auth_units
  , auth.starts
  , auth.expires
  , datediff(day, current_date, auth.expires) as days_until_expiration
from authorization auth
where auth.is_deleted = false
  and auth.active = 1
  and auth.expires between current_date and dateadd(day, 30, current_date)
order by auth.expires;

Claims billed against each authorization

Counts the claims that were linked to each authorization. This is the right way to see how an authorization has been used, since the authorization table itself does not record consumption.

One claim is not necessarily one authorized unit - payers may count units per CPT line or per service date. If you need unit-level accounting, drop down to the charge grain rather than counting claims. Also, claims only appear here when staff tagged the authorization on the claim; untagged claims are invisible to this join.

select
    auth.id as authorization_id
  , auth.patient_id
  , auth.payer_name
  , auth.auth_num
  , auth.auth_units as units_authorized
  , auth.starts
  , auth.expires
  , count(c.id) as claims_linked
from authorization auth
  left join claim c on c.authorization_id = auth.id
where auth.is_deleted = false
  and auth.active = 1
group by auth.id, auth.patient_id, auth.payer_name, auth.auth_num, auth.auth_units, auth.starts, auth.expires
order by claims_linked desc;

Patient authorization history

Returns every authorization on file for a single patient, deleted rows included, ordered by most recently created.

select
    auth.id as authorization_id
  , auth.payer_name
  , auth.auth_num
  , auth.auth_units
  , auth.active
  , auth.starts
  , auth.expires
  , auth.auth_note
  , auth.is_deleted
  , auth.creation_time_utc
  , auth.last_modified_utc
from authorization auth
where auth.patient_id = 12345  -- Replace with actual patient ID
order by auth.creation_time_utc desc;

Authorizations by payer

Aggregates authorization counts and total authorized units per payer.

select
    auth.payer_name
  , count(*) as total_authorizations
  , count(distinct auth.patient_id) as unique_patients
  , sum(auth.auth_units) as total_units_authorized
from authorization auth
where auth.is_deleted = false
group by auth.payer_name
order by total_authorizations 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

Changelog

Active authorizations

Authorizations expiring soon

Claims billed against each authorization

Patient authorization history

Authorizations by payer

​Active authorizations

​Authorizations expiring soon

​Claims billed against each authorization

​Patient authorization history

​Authorizations by payer

Active authorizations

Authorizations expiring soon

Claims billed against each authorization

Patient authorization history

Authorizations by payer