Page 1 of 1
Query for Insurance and Employer
Posted: Thu Jun 22, 2017 11:28 am
by josiedds
I'm looking to run a report that shows who the employers are for a particular insurance.
For example, which employers have Metlife as their carrier. I've tried a few different query examples but no dice.
Any ideas?
Re: Query for Insurance and Employer
Posted: Thu Jun 22, 2017 12:26 pm
by cmcgehee
Sure, here you go:
Code: Select all
/*List all employers for a particular insurance*/
SET @Carrier='%metlife%';
SELECT c.CarrierName, e.EmpName
FROM insplan ip
INNER JOIN carrier c ON c.CarrierNum=ip.CarrierNum
AND c.CarrierName LIKE @Carrier
INNER JOIN employer e ON ip.EmployerNum=e.EmployerNum
GROUP BY e.EmployerNum
ORDER BY c.CarrierName,e.EmpName
Re: Query for Insurance and Employer
Posted: Thu Jun 22, 2017 1:22 pm
by josiedds
That worked great. Thank you.
Is there a tutorial on how to write these queries?
Cuz now I'd like to take it one step further... Can that report give me how many patients are covered by that employer? And can I make it list only active patients?
I'd love to be able to know how to write these...
Thanks
Re: Query for Insurance and Employer
Posted: Thu Jun 22, 2017 1:43 pm
by cmcgehee
We do have a page that gives an introduction on how to write queries:
http://opendental.com/manual/querysql.html.
Re: Query for Insurance and Employer
Posted: Fri Jun 23, 2017 8:19 am
by JasonL
josiedds wrote:I'd love to be able to know how to write these...
Along with the manual page that Chris mentioned there are plenty of free online resources that will teach you some of the basics of SQL/MySQL. W3 Schools has some interactive features that allow you to play with a fake database (good for beginners) and the MySQL reference manual is very detailed (in most places). Whenever you're stuck on something though - you can use the Query Examples page as a guide on how to retrieve information. Hope this helps!
MySQL Reference Manual:
https://dev.mysql.com/doc/refman/5.5/en/
W3 Schools:
https://www.w3schools.com/SQL/
Query Examples:
http://opendentalsoft.com:1942/ODQueryL ... yList.aspx