For users or potential users.
-
KevinRossen
- Posts: 293
- Joined: Mon Apr 22, 2013 8:49 am
- Location: Dallas, TX
-
Contact:
Post
by KevinRossen » Wed Jun 25, 2014 9:53 am
I wrote up this query to see who the top 25 employers are in our office based on patients we've seen over the past two years. I really like the result, so I decided to share. It's similar to example query #67, but only finds patients who had a completed procedure over the past 2 years. Enjoy!
Code: Select all
SELECT e.EmpName, COUNT(DISTINCT pl.PatNum) AS NumPats
FROM procedurelog pl
INNER JOIN patient p ON pl.PatNum=p.PatNum
INNER JOIN patplan pp ON pl.PatNum=pp.PatNum
INNER JOIN inssub i ON pp.InsSubNum=i.InsSubNum
INNER JOIN insplan ip ON i.PlanNum=ip.PlanNum
LEFT JOIN employer e ON ip.EmployerNum=e.EmployerNum
WHERE (pl.ProcStatus=2 AND pl.ProcDate >= DATE_SUB(NOW(),INTERVAL 2 YEAR)) AND p.PatStatus NOT IN (2,3,4,5)
GROUP BY EmpName ORDER BY NumPats DESC LIMIT 25;
-
Jorgebon
- Posts: 502
- Joined: Mon Jun 18, 2007 2:25 pm
- Location: Mayaguez, PR
-
Contact:
Post
by Jorgebon » Wed Jun 25, 2014 3:10 pm
Thank You, this is very nice.
Jorge Bonilla DMD
Open Dental user since May 2005