Open Dental Forum

I'm writing some reports and I'm having trouble figuring this out, perhaps someone with a better idea of how the database works can give me some advice? I have done the queries below, but not being a financial guy I don't know if what I'm getting back makes any sense.

1. If I sum PayAmt(payment table) for a given time period will that give me everything a patient has paid?

2. In the same vein if I sum CheckAmt(claimpayment table) in the same way will that give me all payments by insurance? If not, what will that give me?

Thanks for any help,
Will

willcipriano wrote:1. If I sum PayAmt(payment table) for a given time period will that give me everything a patient has paid?

Well... in a way, yes. But be careful because their might be payments in the table the your office has entered that wasn't really made by a patient? E.g. you have a non-patient "patient" called "PayPal" that people donate to you (that'd be nice). Those payment entries will be part of that table so just keep that in mind.

willcipriano wrote:2. In the same vein if I sum CheckAmt(claimpayment table) in the same way will that give me all payments by insurance? If not, what will that give me?

Yes. It will be very close at the very least. If it differs from our internal reports, it will most likely be because we often times go straight to the claimproc table and group them up by status and sum up the corresponding InsPayAmt column.

willcipriano wrote:I'm writing some reports and I'm having trouble figuring this out, perhaps someone with a better idea of how the database works can give me some advice? I have done the queries below, but not being a financial guy I don't know if what I'm getting back makes any sense.

1. If I sum PayAmt(payment table) for a given time period will that give me everything a patient has paid?

2. In the same vein if I sum CheckAmt(claimpayment table) in the same way will that give me all payments by insurance? If not, what will that give me?

Thanks for any help,
Will

1.) You may want to look at paysplits instead of payments. Payments would be who made the payment, paysplits would be who the payment was applied to (doesn't have to be the same family).

2.) Almost, it will show you the total insurance payments that have already had an insurance check created. However, sometimes offices enter payment by procedure on a claim and forget to make an insurance check, so no claimpayment row is created. In these cases you'd have to look at claimproc.inspayamt.