If you have noticed gaps in your patient records or are struggling to generate accurate provider-specific production reports, you likely have patients missing a primary provider. This open dental no provider assigned query helps you identify active patients who do not have a primary provider listed in their Family Module, allowing you to clean up your database quickly.
The Query
Copy and paste the following code into your User Query window. This query specifically targets active patients (PatStatus = 0) who have a primary provider ID of 0, which indicates no provider is currently assigned.
/* Query to find active patients with no primary provider assigned */
SELECT
PatNum AS 'Patient ID',
LName AS 'Last Name',
FName AS 'First Name',
Birthdate AS 'DOB',
HmPhone AS 'Home Phone'
FROM patient
WHERE PatStatus = 0
AND (PriProv = 0 OR PriProv IS NULL)
ORDER BY LName, FName;
How to Run This Query
- In Open Dental, go to Reports in the Main Menu.
- Click User Query.
- Paste the query provided above into the large text box.
- Click Submit Query.
- The results will display in the grid below, showing you exactly which patient files need attention.
Understanding the Results
The output grid provides the essential information you need to locate and update the patient records:
- Patient ID: The unique identification number for the patient in your system.
- Last Name / First Name: The patient's name for easy searching in the Family Module.
- DOB: The patient's date of birth, which helps confirm you are editing the correct record if you have patients with similar names.
- Home Phone: Included so you can quickly contact the patient if you need to verify their information or update their file.
How to Customize
If you want to narrow down your search, you can easily modify the query. For example, if you only want to see patients who have visited the office within the last two years, you can add a date filter.
To filter by a specific date range, add this line before the ORDER BY clause:
AND DateFirstVisit >= '2024-01-01'
If you want to exclude patients who are marked as "Inactive" or "Archived" (though the current query already filters for PatStatus = 0), you can ensure you are only looking at active patients by keeping the PatStatus = 0 line exactly as it is.
Variations
Find Patients with No Secondary Provider
If you also track secondary providers and want to find patients missing that information, you can change the PriProv column to SecProv:
SELECT PatNum, LName, FName
FROM patient
WHERE PatStatus = 0
AND (SecProv = 0 OR SecProv IS NULL);
Filter by Clinic
If your practice uses multiple clinics and you only want to check one specific location, add the ClinicNum filter:
SELECT PatNum, LName, FName
FROM patient
WHERE PatStatus = 0
AND (PriProv = 0 OR PriProv IS NULL)
AND ClinicNum = 1; -- Replace 1 with your specific ClinicNum
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.