Open Dental Forum

Hi all,
I need a few reports to help my OM.
Trying to find out why collections are low for my associate, and pull a report of the patients she has seen, if insurance has paid and how much, and if patient has paid and how much.

Know of any good reports that will narrow this down for us?

Thanks

This should help with collections.
drtmz

/*85 Sum of patient and insurance payments for date range for given provider*/
SET @StartDate='2021-10-01',@EndDate='2021-10-31';
SET @ProviderAbbreviation='DrL'; -- Enter provider abbreviation here. Uses partial matching. Leave blank for all.

/*--------------------DO NOT MODIFY BELOW THIS LINE--------------------*/
/* Query code written/modified 03/11/2022:ChrisD */
SELECT
p.PatNum,
main.PaymentType,
pv.Abbr,
main.PaymentAmt AS $PaymentAmt_
FROM patient p
INNER JOIN (
SELECT
ps.PatNum,
COALESCE(d.ItemName, "Income Transfer") AS 'PaymentType',
ps.ProvNum,
SUM(ps.SplitAmt) AS 'PaymentAmt'
FROM payment pm
INNER JOIN paysplit ps
ON pm.PayNum = ps.PayNum
AND ps.DatePay BETWEEN @StartDate AND @EndDate
LEFT JOIN definition d
ON d.DefNum = pm.PayType
GROUP BY ps.PatNum, d.ItemName

UNION ALL

SELECT
claimproc.PatNum,
'Ins Checks' AS 'PaymentType',
claimproc.ProvNum,
SUM(claimproc.InsPayAmt) AS 'PaymentAmt'
FROM claimproc
WHERE claimproc.DateCP BETWEEN @StartDate AND @EndDate
AND claimproc.Status IN (1, 4) -- received, supplemental
GROUP BY claimproc.PatNum
) main
ON main.PatNum = p.PatNum
INNER JOIN provider pv
ON pv.ProvNum = main.ProvNum
AND pv.Abbr LIKE CONCAT('%', @ProviderAbbreviation, '%')
ORDER BY p.LName, p.FName;