Query Help - Production by Insurance Plan

[atd]

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]

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?

[cmcgehee]

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

[Third Coast]

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.

[cmcgehee]

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.

[Third Coast]

Perfect explanation, thanks!