of other patients across all payers for a particular referring physician.

Combinatorial (Second Order SQL) Queries for Accounts Receivable Analysis

Fortunately, modern database query technology can address both limitations by enabling "second order SQL" queries, which allow data manipulation based on multiple criteria and using functions of combinations of such criteria.

In our case, second-degree SQL queries allow finding the worst payer for best revenue generating code. Such a discriminating approach allows focusing on higher priority items first, resulting in more effective management. In general, the manager performs a custom comparison of payers according to th

Average medical practice may lose as much as 11% of its revenue due to underpayments. But underpayment recovery potential averages only 5% of revenue and involves costly appeal process. To avoid unrecoverable losses, some providers discontinue servicing patients insured by the worst performing payers. Unfortunately, such a drastic loss reduction measure may boomerang and increase losses depending on complexity of referral relationships. This article outlines limitations of traditional database queries used to identify payer candidates for contract termination and demonstrates alternative decision choices with superior performance in terms of revenue and risk management, facilitated with On Line Analytical Processing (OLAP) technology.

First Order SQL Queries for Accounts Receivable Analysis

Traditional accounts receivable analysis includes identification of payers that systematically underpay and refuse denial appeals. Such analysis is based on simple queries, designed to identify the best CPT code or the worst payer in absolute terms:

• Comparison of revenue for various CPT codes for a given time-period
• Comparison of underpayments for various payers for a given time-period
• Comparison of denials for various payers for a given time-period

A single key database indexing is a standard measure to improve time performance of such queries. It builds an ordered relationship within the data elements based on the value of the selected metric. But single key indexing precludes implementation of more complex queries like "who is the payer that underpays the most for the best CPT code," or "who is the worst referring physician for my worst payer?" and require complex SQL programming skills because of the need to store and process intermediate results. Therefore, ranking the data elements along a single attribute, forces a limited choice for management decision:

• Ignore the problem,
• Renegotiate the contract with the payer, or
• Stop serving patients insured by the worst payer.

But to find more subtle solutions the office manager requires the ability to aggregate and drill into data and formulate queries in real time, in response to observed results to the previous queries. Specifically, a low frequency under performing payer with a high degree of underpayment may not be as detrimental to the office as a high frequency under performing payer with a low degree of underpayment. Contract termination with a wrong payer may accomplish the opposite result to practice goals in terms of revenue maximization and workload reduction. Additionally, a decision to stop serving patients insured by any one payer may cause reduction of referral volume of other patients across all payers for a particular referring physician.

Combinatorial (Second Order SQL) Queries for Accounts Receivable Analysis

Fortunately, modern database query technology can address both limitations by enabling "second order SQL" queries, which allow data manipulation based on multiple criteria and using functions of combinations of such criteria.

In our case, second-degree SQL queries allow finding the worst payer for best revenue generating code. Such a discriminating approach allows focusing on higher priority items first, resulting in more effective management. In general, the manager performs a custom comparison of payers according to the

Line Analytical Processing (OLAP) technology.

First Order SQL Queries for Accounts Receivable Analysis

Traditional accounts receivable analysis includes identification of payers that systematically underpay and refuse denial appeals. Such analysis is based on simple queries, designed to identify the best CPT code or the worst payer in absolute terms:

• Comparison of revenue for various CPT codes for a given time-period
• Comparison of underpayments for various payers for a given time-period
• Comparison of denials for various payers for a given time-period

A single key database indexing is a standard measure to improve time performance of such queries. It builds an ordered relationship within the data elements based on the value of the selected metric. But single key indexing precludes implementation of more complex queries like "who is the payer that underpays the most for the best CPT code," or "who is the worst referring physician for my worst payer?" and require complex SQL programming skills because of the need to store and process intermediate results. Therefore, ranking the data elements along a single attribute, forces a limited choice for management decision:

• Ignore the problem,
• Renegotiate the contract with the payer, or
• Stop serving patients insured by the worst payer.

But to find more subtle solutions the office manager requires the ability to aggregate and drill into data and formulate queries in real time, in response to observed results to the previous queries. Specifically, a low frequency under performing payer with a high degree of underpayment may not be as detrimental to the office as a high frequency under performing payer with a low degree of underpayment. Contract termination with a wrong payer may accomplish the opposite result to practice goals in terms of revenue maximization and workload reduction. Additionally, a decision to stop serving patients insured by any one payer may cause reduction of referral volume of other patients across all payers for a particular referring physician.

Combinatorial (Second Order SQL) Queries for Accounts Receivable Analysis

Fortunately, modern database query technology can address both limitations by enabling "second order SQL" queries, which allow data manipulation based on multiple criteria and using functions of combinations of such criteria.

In our case, second-degree SQL queries allow finding the worst payer for best revenue generating code. Such a discriminating approach allows focusing on higher priority items first, resulting in more effective management. In general, the manager performs a custom comparison of payers according to th

e performance of such queries. It builds an ordered relationship within the data elements based on the value of the selected metric. But single key indexing precludes implementation of more complex queries like "who is the payer that underpays the most for the best CPT code," or "who is the worst referring physician for my worst payer?" and require complex SQL programming skills because of the need to store and process intermediate results. Therefore, ranking the data elements along a single attribute, forces a limited choice for management decision:

• Ignore the problem,
• Renegotiate the contract with the payer, or
• Stop serving patients insured by the worst payer.

But to find more subtle solutions the office manager requires the ability to aggregate and drill into data and formulate queries in real time, in response to observed results to the previous queries. Specifically, a low frequency under performing payer with a high degree of underpayment may not be as detrimental to the office as a high frequency under performing payer with a low degree of underpayment. Contract termination with a wrong payer may accomplish the opposite result to practice goals in terms of revenue maximization and workload reduction. Additionally, a decision to stop serving patients insured by any one payer may cause reduction of referral volume of other patients across all payers for a particular referring physician.

Combinatorial (Second Order SQL) Queries for Accounts Receivable Analysis

Fortunately, modern database query technology can address both limitations by enabling "second order SQL" queries, which allow data manipulation based on multiple criteria and using functions of combinations of such criteria.

In our case, second-degree SQL queries allow finding the worst payer for best revenue generating code. Such a discriminating approach allows focusing on higher priority items first, resulting in more effective management. In general, the manager performs a custom comparison of payers according to th

st payer.

But to find more subtle solutions the office manager requires the ability to aggregate and drill into data and formulate queries in real time, in response to observed results to the previous queries. Specifically, a low frequency under performing payer with a high degree of underpayment may not be as detrimental to the office as a high frequency under performing payer with a low degree of underpayment. Contract termination with a wrong payer may accomplish the opposite result to practice goals in terms of revenue maximization and workload reduction. Additionally, a decision to stop serving patients insured by any one payer may cause reduction of referral volume of other patients across all payers for a particular referring physician.

Combinatorial (Second Order SQL) Queries for Accounts Receivable Analysis

Fortunately, modern database query technology can address both limitations by enabling "second order SQL" queries, which allow data manipulation based on multiple criteria and using functions of combinations of such criteria.

In our case, second-degree SQL queries allow finding the worst payer for best revenue generating code. Such a discriminating approach allows focusing on higher priority items first, resulting in more effective management. In general, the manager performs a custom comparison of payers according to th

of other patients across all payers for a particular referring physician.

Combinatorial (Second Order SQL) Queries for Accounts Receivable Analysis

Fortunately, modern database query technology can address both limitations by enabling "second order SQL" queries, which allow data manipulation based on multiple criteria and using functions of combinations of such criteria.

In our case, second-degree SQL queries allow finding the worst payer for best revenue generating code. Such a discriminating approach allows focusing on higher priority items first, resulting in more effective management. In general, the manager performs a custom comparison of payers according to the following four-step sequence:

• Select metrics (e.g., % paid, % accounts receivable beyond 120 days, % denials)
• Select dimensions (providers, payers, CPT codes, ICD-9 codes, referring physicians)
• Partition
• Aggregate, drill-down, pivot

Worst Payer Query

To find a payer with highest amount of underpayments for the most-frequent CPT code, a second order SQL query can be written along the following lines:

For a given time-interval,

Select payers

Where sum of underpayments over

(all CPT codes Where Revenue > Revenue Threshold) > Underpayment Threshold

Worst Referring Physician Query

To avoid the risk of losing referrals from better-performing payers, the manager may consider severing referral relationship with some referring physicians instead of payers. In such a case, distribution of patients across various payers plays an important role for each referring physician. A single combinatorial query may fetch the Worst Referring Physician as follows:

For a given time-interval,

Select referring physicians Where Revenue for the Worst Payer > Threshold

Summary

Underpayment management involves all phases of claims processing and requires powerful Vericle-like computing platforms for exhaustive comparisons of payments versus allowed amounts and subsequent appeal management. OLAP allows better analysis of accounts receivable and more effective management because of the ability to handle queries with functions of multiple attributes and dimensions. Note that in the absence of native OLAP mechanism, effective Vericle-like billing platforms allow similarly powerful analysis by introducing intermediary steps. Such steps may add insight to analysis and improve decision quality.