Patient report

For users or potential users.
Post Reply
housedentistry
Posts: 7
Joined: Wed Aug 07, 2013 7:55 am

Patient report

Post by housedentistry » Tue Jun 13, 2017 6:26 am

Is there a way to get a list/report of our patients that don't have insurance.

User avatar
cmcgehee
Posts: 711
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: Patient report

Post by cmcgehee » Tue Jun 13, 2017 8:02 am

Here's a quick little query that will give you a list of uninsured patients:

Code: Select all

SELECT CONCAT(p.LName,', ',p.FName) AS 'Patient Name'
FROM patient p 
LEFT JOIN patplan pp ON pp.PatNum=p.PatNum AND pp.Ordinal=1 
WHERE pp.PatNum IS NULL
AND p.PatStatus=0
ORDER BY p.LName,p.FName
I want to warn you to be careful when looking at our Query Examples page regarding patients with no insurance. Most of them are using the patient.HasIns column which is sort of deprecated.
Chris McGehee
Open Dental Software
http://www.opendental.com

PedoDentist
Posts: 13
Joined: Thu Dec 17, 2015 10:10 am

Re: Patient report

Post by PedoDentist » Fri Jan 24, 2020 8:07 am

That is awesome, is there a way I can limit that search to a certain date or date range.

PatrickC
Posts: 56
Joined: Thu Jun 06, 2019 11:37 am

Re: Patient report

Post by PatrickC » Fri Jan 24, 2020 9:20 am

This will give you the same list with patients having completed procedures between the indicated dates.

Code: Select all

SET @startDate='2007-01-01' , @endDate='2020-01-01';
SELECT CONCAT(p.LName,', ',p.FName) AS 'Patient Name'
FROM patient p 
LEFT JOIN patplan pp ON pp.PatNum=p.PatNum AND pp.Ordinal=1 
INNER JOIN procedurelog pl ON p.PatNum=pl.PatNum 
WHERE pp.PatNum IS NULL
AND p.PatStatus=0
AND (pl.ProcDate BETWEEN @startDate AND @endDate)
AND pl.ProcStatus=2
GROUP BY p.PatNum
ORDER BY p.LName,p.FName;
Patrick Carlson
Open Dental Software
http://www.opendental.com

tgriswold
Posts: 122
Joined: Fri Jun 07, 2013 8:52 am

Re: Patient report

Post by tgriswold » Fri Jan 24, 2020 9:36 am

Note that the above query does not take into account the insurance the patient had at the time the procedure was completed, only what their current insurance coverage is now. The patient may have dropped their insurance plan between the procedure date and today. If you are trying to look back several months/years into the past you will need a more complicated query.
See https://opendentalsoft.com:1943/ODQuery ... tForm.aspx if you are interested in getting a report that would better fit what you're looking for.
Travis Griswold
Open Dental Software
http://www.opendental.com

Post Reply