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;
