Reports

[nmenon]

Hello all

I would like to get a report that shows just the Patient portions of account balances in 0-30, 30-60, 60-90 and 90 + periods. If anyone can point me if this is available, that would be great !! thanks much.

[nmenon]

This is the query that I have now, but the number of patients in this much smaller than the Aging report. Please help !!

thanks much/Narayan

DROP TABLE IF EXISTS tmp;
CREATE TABLE tmp SELECT CONCAT(g.LName,', ',g.FName,' ',g.MiddleI) AS Guarantor
,g.Bal_0_30,g.Bal_31_60,g.Bal_61_90,g.BalOver90
,g.BalTotal,g.InsEst,g.BalTotal-g.InsEst AS $PatPor,
MAX(paysplit.DatePay) AS LastPayment,
(SELECT MAX(pl.ProcDate) FROM procedurelog pl INNER JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum
WHERE pc.ProcCode ='M1112' AND pl.PatNum=patient.PatNum) AS 'LastAdjDate'
FROM patient
INNER JOIN patient g ON patient.Guarantor=g.PatNum
LEFT JOIN paysplit
ON paysplit.PatNum=patient.PatNum
WHERE (patient.patstatus IN (0,1))
AND (g.Bal_0_30 > '.005')
GROUP BY patient.Guarantor
ORDER BY g.LName,g.FName;
SELECT Guarantor
,Bal_0_30,Bal_31_60,Bal_61_90,BalOver90
,BalTotal,InsEst, $PatPor, DATEDIFF(CURDATE(), LastAdjDate) AS 'DaysSince',
DATE(DATE_FORMAT(LastPayment,'%m/%d/%Y')) AS LastPayment FROM tmp WHERE DATE(LastPayment)<(CURDATE()- INTERVAL 30 DAY);
DROP TABLE IF EXISTS tmp;

[jordansparks]

The aging queries are something like 4 pages long. But they are all build around the family, not the individual. The big complicate queries would have to be altered to subtract insurance portion. It's really really really complicated.

[nmenon]

Thanks, Jordan. I just need the patient information. Family / Guarantor should be fine... I am trying to get a list of patient balances that the front desk can use to contact patients without having the big list with all insurance payment outstanding. Your help is much appreciated.

[jordansparks]

I have added your request to this page:
http://www.opendental.com/manual/accrual.html
However, it is unlikely to get done if you don't also add it as a feature request and vote for it.

[nmenon]

Thanks, Jordan. Appreciate it.. Sorry for the newbie questions, but where do I go to vote on this (or any other request ) ?

[jordansparks]

No, no problem.

http://www.opendental.com/manual/featurerequests.html