Fee Schedule Comparison Query

If you are trying to determine if your providers are charging consistent rates across different fee schedules, this report provides the clarity you need. This open dental fee schedule comparison query allows you to view the fees for specific procedure codes side-by-side for different fee schedules, helping you spot inconsistencies in your practice's pricing.

The Query

/* Set the Fee Schedule IDs you want to compare here */
SET @FeeSched1 = 1;
SET @FeeSched2 = 2;

SELECT 
    pc.ProcCode, 
    pc.AbbrDesc AS Description,
    f1.Amount AS Fee_Sched_1,
    f2.Amount AS Fee_Sched_2,
    (f1.Amount - f2.Amount) AS Difference
FROM procedurecode pc
LEFT JOIN fee f1 ON pc.CodeNum = f1.CodeNum AND f1.FeeSched = @FeeSched1
LEFT JOIN fee f2 ON pc.CodeNum = f2.CodeNum AND f2.FeeSched = @FeeSched2
WHERE f1.Amount IS NOT NULL OR f2.Amount IS NOT NULL
ORDER BY pc.ProcCode;

How to Run This Query

1. In Open Dental, go to Reports in the Main Menu.
2. Click User Query.
3. Paste the query provided above into the large text box.
4. Before clicking submit, look at the top two lines of the query. Change the numbers 1 and 2 to match the FeeSchedNum of the schedules you wish to compare. You can find these numbers by going to Setup > Definitions > Fee Schedules.
5. Click Submit Query.
6. The results will display in the grid below.

Understanding the Results

• ProcCode: The ADA procedure code (e.g., D0150).
• Description: The abbreviated description of the procedure as defined in your procedure code list.
• Fee_Sched_1: The dollar amount assigned to this code in the first fee schedule you selected.
• Fee_Sched_2: The dollar amount assigned to this code in the second fee schedule you selected.
• Difference: The mathematical difference between the two fees. A positive number means the first schedule is higher; a negative number means the second schedule is higher.

How to Customize

To compare different fee schedules, you only need to modify the SET statements at the very top of the query:

• Change the Fee Schedules: Locate SET @FeeSched1 = 1; and SET @FeeSched2 = 2;. Replace the numbers with the specific FeeSchedNum IDs from your system.
• Filter by Category: If you only want to see hygiene codes, you can add a line before the ORDER BY clause: AND pc.ProcCat = (SELECT DefNum FROM definition WHERE ItemName = 'Hygiene'). Note: This assumes you have a category named 'Hygiene' in your Procedure Categories definition.

Variations

If you want to identify only the codes where there is a price discrepancy, you can add a filter to the end of the query. Change the last line to:

WHERE (f1.Amount - f2.Amount) <> 0
ORDER BY pc.ProcCode;

This will hide all rows where the fees are identical, leaving you with a clean list of only the codes that need your attention.

Skip the Query — Use DentalCanvas Instead

Don't want to write SQL? DentalCanvas connects to your Open Dental database and shows you this data automatically in a visual dashboard — no queries required.

This article is provided by opendentalsupport.com, an independent community resource. We are not affiliated with Open Dental Software, Inc.