Payer Address

One row per saved payer mailing address. A practice can hold more than one address for the same payer. This is an address book kept against the Claim.MD payer directory rather than against the practice’s own saved payer list, so payer_id resolves on roughly two thirds of rows. The rest are directory payers the practice holds a mailing address for but has never saved as a payer of its own. claimmd_payer_id is populated on every row, so use it rather than payer_id when you need payer identity across the whole table. This table’s columns and relationships are shown in the Hosted Database schema.

claimmd_payer_id is the Claim.MD payer directory code and is held as text. Not every value is numeric - PAPER, EAP20, and 00060 are all valid codes - so leading zeros are significant and it should not be cast to a number.

zip formatting is inconsistent in the source. Both five-digit and nine-digit forms appear, with and without a hyphen, so normalize it before matching on it.

For reporting on but not limited to:

Where paper claims are mailed for a given payer
Payers a practice holds an address for but has not saved
Practices holding several addresses for the same payer
Checking address completeness before a paper claim run
Mailing addresses by state, to spot regional payer coverage

Mailing addresses with their payer

select
    pr.name as practice_name
  , pa.payer_name
  , p.custom_payer_name as saved_payer_name
  , pa.claimmd_payer_id
  , pa.address1
  , pa.address2
  , pa.city
  , pa.state
  , pa.zip
from payer_address pa
  left join practice pr on pr.id = pa.practice_id
  left join payer p on p.id = pa.payer_id
where not pa.is_deleted
order by pr.name, pa.payer_name;

Addresses for directory payers the practice has not saved

select
    pr.name as practice_name
  , pa.payer_name
  , pa.claimmd_payer_id
  , pa.city
  , pa.state
from payer_address pa
  left join practice pr on pr.id = pa.practice_id
where pa.payer_id is null
  and not pa.is_deleted
order by pr.name, pa.payer_name;

Payers with more than one address on file

select
    pr.name as practice_name
  , pa.claimmd_payer_id
  , pa.payer_name
  , count(*) as address_count
from payer_address pa
  left join practice pr on pr.id = pa.practice_id
where not pa.is_deleted
group by pr.name, pa.claimmd_payer_id, pa.payer_name
having count(*) > 1
order by address_count desc;

Addresses missing a city or state

select
    pr.name as practice_name
  , pa.payer_name
  , pa.claimmd_payer_id
  , pa.address1
  , pa.city
  , pa.state
  , pa.zip
from payer_address pa
  left join practice pr on pr.id = pa.practice_id
where not pa.is_deleted
  and (pa.city is null or pa.state is null)
order by pr.name, pa.payer_name;

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

Mailing addresses with their payer

Addresses for directory payers the practice has not saved

Payers with more than one address on file

Addresses missing a city or state

​Mailing addresses with their payer

​Addresses for directory payers the practice has not saved

​Payers with more than one address on file

​Addresses missing a city or state

Mailing addresses with their payer

Addresses for directory payers the practice has not saved

Payers with more than one address on file

Addresses missing a city or state