How many patients did I refer to a certain provider?

For users or potential users.
Post Reply
aussiedentist
Posts: 112
Joined: Sat Jan 22, 2011 7:33 pm

How many patients did I refer to a certain provider?

Post by aussiedentist » Mon Jul 17, 2017 10:09 pm

I am looking at finding out how many patients I refered to a certain provider each year. Ie how many endo referals, ortho, etc

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

Re: How many patients did I refer to a certain provider?

Post by cmcgehee » Tue Jul 18, 2017 7:21 am

Here is a query from our query examples page (http://opendentalsoft.com:1942/ODQueryL ... yList.aspx) that should accomplish this task:

Code: Select all

/*211 Referrered out patients for a specific ref source, optional date range, use large date range if none needed*/
SET @pos=0, @RefLName='%baron%', @StartDate='2017-01-01', @EndDate='2017-12-31';
SELECT @pos:=@pos+1 AS COUNT, p.PatNum AS PatID, p.LName AS PatLast, p.FName AS PatFirst, 
(CASE WHEN ra.RefToStatus=0 THEN 'None' 
 WHEN ra.RefToStatus=1 THEN 'Declined' 
 WHEN ra.RefToStatus=2 THEN 'Scheduled' 
 WHEN ra.RefToStatus=3 THEN 'Consulted' 
 WHEN ra.RefToStatus=4 THEN 'InTreatment' 
 ELSE 'Complete' END) AS STATUS,
/*Enum:ReferralToStatus 0=None,1=Declined,2=Scheduled,3=Consulted,4=InTreatment,5=Complete*/
r.LName AS RefLast, r.FName AS RefFirst, ra.Note FROM patient p
INNER JOIN refattach ra ON ra.PatNum = p.PatNum
INNER JOIN referral r ON r.ReferralNum=ra.ReferralNum
WHERE p.PatStatus=0 AND 
RefType=0 AND
r.LName LIKE(@RefLName) AND
(ra.RefDate BETWEEN @StartDate AND @EndDate);
Chris McGehee
Open Dental Software
http://www.opendental.com

Post Reply