Query I made for Top 25 Employer Plans

[KevinRossen]

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]

Thank You, this is very nice.