Query for Insurance and Employer

For users or potential users.
Post Reply
josiedds
Posts: 41
Joined: Tue Sep 18, 2007 11:59 am
Location: Southern California
Contact:

Query for Insurance and Employer

Post by josiedds » Thu Jun 22, 2017 11:28 am

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?

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

Re: Query for Insurance and Employer

Post by cmcgehee » Thu Jun 22, 2017 12:26 pm

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
Chris McGehee
Open Dental Software
http://www.opendental.com

josiedds
Posts: 41
Joined: Tue Sep 18, 2007 11:59 am
Location: Southern California
Contact:

Re: Query for Insurance and Employer

Post by josiedds » Thu Jun 22, 2017 1:22 pm

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

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

Re: Query for Insurance and Employer

Post by cmcgehee » Thu Jun 22, 2017 1:43 pm

We do have a page that gives an introduction on how to write queries: http://opendental.com/manual/querysql.html.
Chris McGehee
Open Dental Software
http://www.opendental.com

User avatar
JasonL
Posts: 28
Joined: Wed Jun 10, 2015 1:57 pm

Re: Query for Insurance and Employer

Post by JasonL » Fri Jun 23, 2017 8:19 am

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
Jason Long
Open Dental Web Technology Coordinator
www.opendental.com

Post Reply