Query Help - Production by Insurance Plan

For complex topics that regular users would not be interested in. For power users and database administrators.
Post Reply
atd
Posts: 404
Joined: Thu Mar 27, 2008 2:28 pm
Location: Minneapolis, MN

Query Help - Production by Insurance Plan

Post by atd » Thu Mar 11, 2010 1:13 pm

I need to create a query to calculate Production by Insurance Plan - I need columns for the Fee, Writeoff, ins est (or paid amount) and patient responsibility. Previously I was just using a modified version of the PPO Writeoff report - having it include all insurance, not just PPO and then assuming the difference between that and total production was private pay. However, I recently figured out that the PPO writeoff report is double-counting the Fees if the patient had secondary insurance. So our accounting of private pay revenue is being underreported. I'd also like the report to include a line for private pay, rather than assuming the difference between the Production and Income report and the PPO report is the correct amount. Has anyone already created this?

Third Coast
Posts: 30
Joined: Fri Nov 14, 2014 4:56 pm

Re: Query Help - Production by Insurance Plan

Post by Third Coast » Wed Oct 03, 2018 10:55 am

I BUMPED this old post to see if anyone has a good answer for this.

I am trying to determine the amount of production from a particular insurance plan, and can not find an easy way to do this. Surely there's a easy report for this that I'm overlooking?

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

Re: Query Help - Production by Insurance Plan

Post by cmcgehee » Wed Oct 03, 2018 11:37 am

A couple queries from our examples page might give you what you're looking for, particularly 227 or 1045.
http://opendentalsoft.com:1942/ODQueryL ... yList.aspx
Chris McGehee
Open Dental Software
http://www.opendental.com

Third Coast
Posts: 30
Joined: Fri Nov 14, 2014 4:56 pm

Re: Query Help - Production by Insurance Plan

Post by Third Coast » Thu Oct 04, 2018 6:35 am

Thank you, that was able to help get me what I needed, I also needed to create a separate insurance carrier to separate out what I was specifically looking for using those reports.

Do you think you could confirm whether the $Patient_ column in query 227 is utilizing our office fees rather than the adjusted "write-off" totals, or if it should be after any relevant write-offs? I'm wondering because when I run query 1045 there is a similar $Prod_ value as 227's corresponding $Patient_ value, but there is also an $Adj_ column which if subtracted from the $Prod_ would significantly change the total production from that particular plan.

Please let me know if any further clarification is helpful.

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

Re: Query Help - Production by Insurance Plan

Post by cmcgehee » Thu Oct 04, 2018 8:14 am

In query 227 the $Patient column is calculated as your office fees minus your insurance estimates. So it will not be the true patient portion if you use writeoffs.
In query 1045 the $Prod value is the fees billed to insurance which means that it will include less procedures than the $TotalProd column in 227. If you subtract $Adj from $Prod, then you will get close to your net production for that carrier.
Chris McGehee
Open Dental Software
http://www.opendental.com

Third Coast
Posts: 30
Joined: Fri Nov 14, 2014 4:56 pm

Re: Query Help - Production by Insurance Plan

Post by Third Coast » Thu Oct 04, 2018 10:19 am

Perfect explanation, thanks!

Post Reply