Open Dental Forum

I have been trying to find / develop a query that would give me actual collections, (Ins & Patient) with collection dates linked to the procedure, procedure date and provider.

Here is what I have so far but I think I am missing some other payments. Is there a better query that someone else is using or does anyone have any suggestions as to capture all ACTUAL collections.


Select 'InsPay' As 'Source',
prov.Abbr AS 'Provider', CONCAT(prov.FName, ' ',prov.LName) As Provname, left(prov.Abbr,4) AS 'ProvID',
CONCAT(p.LName,', ',p.FName,' ',p.MiddleI) AS Patient,
SUM(cp.InsPayAmt)AS PayAmt,
pl.ProcDate AS 'VisitDate',
month(pl.ProcDate) AS 'VisitMonth',
year(pl.ProcDate) AS 'VisitMonth',
Month(cp.dateentry) AS 'PmtMth',
Year(cp.dateentry) AS 'PmtYr',
cp.dateentry AS 'PmtDate',
pc.ProcCode,
pl.ProcFee
FROM procedurelog pl
INNER JOIN patient p ON p.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
INNER JOIN provider prov ON prov.ProvNum=pl.ProvNum
LEFT JOIN claimproc cp ON pl.ProcNum=cp.ProcNum AND cp.Status IN (0,1,6)
WHERE pl.ProcStatus = '2'
AND cp.dateentry > '2019-01-01'
GROUP BY pl.ProcNum

UNION

Select 'PatPay' As 'Source',
prov.Abbr AS 'Provider', CONCAT(prov.FName, ' ',prov.LName) As Provname, left(prov.Abbr,4) AS 'ProvID',
CONCAT(p.LName,', ',p.FName,' ',p.MiddleI) AS Patient,
SUM(ps.SplitAmt)AS PayAmt,
pl.ProcDate AS 'VisitDate',
month(pl.ProcDate) AS 'VisitMonth',
year(pl.ProcDate) AS 'VisitMonth',
Month(ps.dateentry) AS 'PmtMth',
Year(ps.dateentry) AS 'PmtYr',
ps.dateentry AS 'PmtDate',
pc.ProcCode,
pl.ProcFee
FROM procedurelog pl
INNER JOIN patient p ON p.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
INNER JOIN provider prov ON prov.ProvNum=pl.ProvNum
LEFT JOIN paysplit ps ON pl.ProcNum=ps.ProcNum
WHERE pl.ProcStatus = '2'
AND ps.dateentry > '2019-01-01'
GROUP BY pl.ProcNum

Off of the top of my head: your first query will miss any "As Total" payments because those are claimprocs but are not attached to procedures. Your second query will miss any paysplits that are not attached to a procedure (which i'm less up to date on if these paysplits are valid "income" anymore depending on your preferences/workflow so you'll have to check those out in your DB). There could be other edge cases i'm missing here and our query request department would be better suited to help you with those.

Some other suggestions for query writing in general: LEFT JOINs should just be an INNER JOIN if you're going to put a WHERE clause that uses the LEFT JOIN table, since you're saying that the LEFT JOIN table can't be null. Runtime probably makes little difference because mysql is smart and digests them before running but it helps readability.

If you would like assistance in getting a query that will meet all your needs I would recommend looking into our query requests. https://opendentalsoft.com:1943/ODQuery ... tForm.aspx