Open Dental Forum

October 10, 2015

Hello to y'all query gurus.

Every time I try something fancy, I get stuck. Well not every time but enough times to make it feel that way.
Here's one of our standard queries that I have tweaked a bit to provide very useful information. Now I would
like to add in information from 'paysplit table.'

Here is the original query:

/*995 Modified 2015-09-25 for Dr. Smith's recapitulation of dental insurance payments ....
ProcDate, Provider, Clinic, ProcCode, Fee, and InsPayEst for completed procedures in a date range of a specific provider and carrier.*/
/*Query code written/modified 08/14/2015*/
SET @StartDate = '2015-01-01', @EndDate = '2015-09-26'; #Set date range here
SET @ProvNum = 27, @CarrierName = '%%'; #set providerNum and CarrierName here.
SELECT procedurelog.ProcDate, provider.Abbr AS 'ProvAbbr', clinic.Description AS 'Clinic',
procedurecode.ProcCode, claimproc.PlanNum, claimproc.PatNum, DateCP, Status,
/* '0' is sent but not received, '1' is send AND received. */
claimproc.ProcNum, procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits) AS 'ProcFee',
SUM(claimproc.InsPayEst) AS 'InsPayEst', InsPayAmt, Remarks
FROM procedurelog
INNER JOIN claimproc ON procedurelog.ProcNum=claimproc.ProcNum
AND claimproc.Status IN (0,1,4,5,6,7,8)
INNER JOIN insplan ON insplan.PlanNum = claimproc.PlanNum
INNER JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum
AND carrier.CarrierName LIKE @CarrierName
INNER JOIN procedurecode ON procedurelog.CodeNum = procedurecode.CodeNum
LEFT JOIN clinic ON clinic.ClinicNum=procedurelog.ClinicNum
INNER JOIN provider ON provider.ProvNum=claimproc.ProvNum
AND provider.ProvNum = @ProvNum
WHERE procedurelog.ProcDate BETWEEN @StartDate AND @EndDate
AND procedurelog.ProcStatus = 2 #complete
GROUP BY procedurelog.ProcNum
ORDER BY ProcDate;

So, now I want to add in information for paysplit.

SELECT * FROM paysplit
ORDER BY ProcDate DESC;

Basically, The top query can provide Dr. Smith with the information as to what he has generated (produced), what has been billed for, and what we have collected on his behalf for insurance patients within a given week, or month or year.

If I add in paysplit information, then I will have the above information for cash, credit card, CareCredit, or those paying for their treatment in bushels of corn, or pumpkins. If you can give me sentence or two about your logic about which JOINS to use, that too would be helpful so I can eventually learn SQL on my own.

So thanks in advance query gurus. Thank you very much.

Regards,


Joely


PS~ To comply with HIPAA regulations, I have changed the doctor's name to Dr. Smith. That is not his real name. His real name is Dr. Jones.

On our database documentation page (Which I'm sure you have, but just for posterity is here: http://opendental.com/OpenDentalDocumentation15-3.xml) when you look at the paysplit table you'll notice about halfway down that it has a foreign key called ProcNum which goes to the procedurelog table. Since you already have the procedurelog table you can do a simple inner or left join to grab paysplit information from the paysplit table. There is a catch though; paysplits are optionally connected to procedures. If your office has been good and attached paysplits to the procedures they are paying off then the query will work fine with an INNER join. If they haven't then you may think about using a LEFT join as some procedures won't have an attached paysplit. If you don't want to connect paysplits to procedures then you'll have to grab it a different way such as going through the patient.PatNum or possibly the payment.PayNum. However, since you're grouping by procedurelog at the end of your query it makes the most sense to connect the paysplit with the ProcNum. Does that make sense?

While David's comments are correct, you are going to run into issue getting the patient paid amount if you simply left join paysplits to this query. This is because of potential duplication of rows and the way SUM() works in mysql. Currently the query works fine, but if you joined in paysplit and tried to sum the paysplit.SplitAmt you would get a duplicated amount depending on how many insurance estimates were attached to this procedure. Likewise your insurance estimate sum would get a duplicated amount depending on how many paysplits are attached to this procedure. To solve this issue you basically have to calculate one sum separately from the other. This can be seen in queries like #934 on our examples page with the

Thank you very much David and Travis. I had independently come to the same conclusions before I asked for your help, but I assumed my lack of knowledge was the problem. Our office is not attaching cash and credit card payments to procedures therefore it is our own GIGO approach that is limiting us. As background, I have worked with DB2, FoxBASE and FoxPro in the distant past. MySQL is a new experience for me that I am enjoying working with very much. This morning I am experimenting with DISTINCT function with some promising early results. I'll keep you posted and thanks again.

Joely