Open Dental Forum

Hello,

Ill run my AR report from all sources, it will give me the AR by time grouping, such as 0-30, 30-60, and then 90 days and older. Then it will tell me insurance is x and patients are y. But i can't see what the breakdowns between insurance and patients are in the time chunks. How is that done?

Try this. (below)
Most reports / queries are found here http://opendentalsoft.com:1942/ODQueryL ... yList.aspx
If you don't know how to do this, let us know.
Change the date in line #2

/*781 Aging for families with any balance with patient and insurance estimates separated*/
SET @AsOf='2014-02-03';
SELECT E.Guarantor AS Guarantor,E.Total AS 'Total Balance',
E.Pat0to30 AS 'Pat 0-30 Days',E.Pat31to60 AS 'Pat 31-60 Days',
E.Pat61to90 AS 'Pat 61-90 Days',E.PatOver90 AS 'Pat >90 Days',
E.InsEst0to30 AS 'Ins 0-30 Days',E.InsEst31to60 AS 'Ins 31-60 Days',
E.InsEst61to90 AS 'Ins 61-90 Days',E.InsEstOver90 AS 'Ins >90 Days'
FROM (
SELECT 0 AS ItemOrder,CONCAT(D.LName,', ',D.FName) AS Guarantor,
FORMAT(ROUND(
((CASE WHEN D.FamCredits<D.FamChargesOver90+D.FamCharges61to90+D.FamCharges31to60 THEN D.FamCharges0to30
WHEN D.FamChargesOver90+D.FamCharges61to90+D.FamCharges31to60+D.FamCharges0to30<=D.FamCredits THEN 0
ELSE D.FamChargesOver90+D.FamCharges61to90+D.FamCharges31to60+D.FamCharges0to30-D.FamCredits END)-COALESCE(FamInsEst0to30,0))
,2),2) AS Pat0to30,
FORMAT(ROUND(
((CASE WHEN D.FamCredits<D.FamChargesOver90+D.FamCharges61to90 THEN D.FamCharges31to60
WHEN D.FamChargesOver90+D.FamCharges61to90+D.FamCharges31to60<=D.FamCredits THEN 0
ELSE D.FamChargesOver90+D.FamCharges61to90+D.FamCharges31to60-D.FamCredits END)-COALESCE(FamInsEst31to60,0))
,2),2) AS Pat31to60,
FORMAT(ROUND(
((CASE WHEN D.FamCredits<=D.FamChargesOver90 THEN D.FamCharges61to90
WHEN D.FamChargesOver90+D.FamCharges61to90<=D.FamCredits THEN 0
ELSE D.FamChargesOver90+D.FamCharges61to90-D.FamCredits END)-COALESCE(FamInsEst61to90,0))
,2),2) AS Pat61to90,
FORMAT(ROUND(
((CASE WHEN D.FamCredits>=D.FamChargesOver90 THEN 0
ELSE D.FamChargesOver90-D.FamCredits END)-COALESCE(FamInsEstOver90,0))
,2),2) AS PatOver90,
FORMAT(ROUND(D.FamBal,2),2) AS Total,
FORMAT(ROUND(COALESCE(FamInsEst0to30,0),2),2) AS InsEst0to30,
FORMAT(ROUND(COALESCE(FamInsEst31to60,0),2),2) AS InsEst31to60,
FORMAT(ROUND(COALESCE(FamInsEst61to90,0),2),2) AS InsEst61to90,
FORMAT(ROUND(COALESCE(FamInsEstOver90,0),2),2) AS InsEstOver90
FROM (
/*Get the family level charges, credits, and ins estimates*/
SELECT g.PatNum,g.LName,g.FName,SUM(B.PatBal) AS FamBal,SUM(B.Charges0to30) AS FamCharges0to30,SUM(B.Charges31to60) AS FamCharges31to60,
SUM(B.Charges61to90) AS FamCharges61to90,SUM(B.ChargesOver90) AS FamChargesOver90,SUM(B.Credits) AS FamCredits,
SUM(COALESCE(C.InsEst0to30,0)) AS FamInsEst0to30,SUM(COALESCE(C.InsEst31to60,0)) AS FamInsEst31to60,
SUM(COALESCE(C.InsEst61to90,0)) AS FamInsEst61to90,SUM(COALESCE(C.InsEstOver90,0)) AS FamInsEstOver90
FROM (
/*Get Patient level charges and credits*/
SELECT RawPatTrans.PatNum,SUM(RawPatTrans.TranAmount) AS PatBal,
(SUM(CASE WHEN (RawPatTrans.TranAmount>0 AND RawPatTrans.TranDate<=@AsOf AND RawPatTrans.TranDate>=(@AsOf-INTERVAL 30 DAY)) THEN RawPatTrans.TranAmount ELSE 0 END)) AS Charges0to30,
(SUM(CASE WHEN (RawPatTrans.TranAmount>0 AND RawPatTrans.TranDate<(@AsOf-INTERVAL 30 DAY) AND RawPatTrans.TranDate>=(@AsOf-INTERVAL 60 DAY)) THEN RawPatTrans.TranAmount ELSE 0 END)) AS Charges31to60,
(SUM(CASE WHEN (RawPatTrans.TranAmount>0 AND RawPatTrans.TranDate<(@AsOf-INTERVAL 60 DAY) AND RawPatTrans.TranDate>=(@AsOf-INTERVAL 90 DAY)) THEN RawPatTrans.TranAmount ELSE 0 END)) AS Charges61to90,
(SUM(CASE WHEN (RawPatTrans.TranAmount>0 AND RawPatTrans.TranDate<(@AsOf-INTERVAL 90 DAY)) THEN RawPatTrans.TranAmount ELSE 0 END)) AS ChargesOver90,
-(SUM(CASE WHEN (RawPatTrans.TranAmount<0 AND RawPatTrans.TranDate<=@AsOf) THEN RawPatTrans.TranAmount ELSE 0 END)) AS Credits
FROM (
/*Get the completed procedure dates and charges for the entire office history*/
SELECT 'Fee' AS TranType,pl.PatNum AS PatNum,pl.ProcDate AS TranDate,
pl.ProcFee*(pl.UnitQty+pl.BaseUnits) AS TranAmount
FROM procedurelog pl
WHERE pl.ProcStatus=2
UNION ALL
/*Paysplits for the entire office history*/
SELECT 'Pay' AS TranType,ps.PatNum AS PatNum,ps.DatePay AS TranDate,
-ps.SplitAmt AS TranAmount
FROM paysplit ps
WHERE ps.PayPlanNum=0 /*Only splits not attached to payment plans*/
UNION ALL
/*Get the adjustment dates and amounts for the entire office history*/
SELECT 'Adj' AS TranType,a.PatNum AS PatNum,a.AdjDate AS TranDate,
a.AdjAmt AS TranAmount
FROM adjustment a
WHERE a.AdjAmt!=0
UNION ALL
/*Claim payments for the entire office history*/
SELECT 'InsPay' AS TranType,cp.PatNum AS PatNum,cp.DateCp AS TranDate,
-cp.InsPayAmt-cp.Writeoff AS TranAmount
FROM claimproc cp
WHERE cp.Status IN (1,4,5,7)/*received,supplemental,CapClaim, or CapComplete*/
UNION ALL
/*Payment plan principal for the entire office history on or before the given date*/
SELECT 'PayPlan' AS TranType,pp.PatNum AS PatNum,pp.PayPlanDate AS TranDate,
-pp.CompletedAmt AS TranAmount
FROM payplan pp
WHERE pp.CompletedAmt!=0
GROUP BY pp.PayPlanNum
) RawPatTrans
WHERE TranDate<=@AsOf
GROUP BY RawPatTrans.PatNum
) B
LEFT JOIN (
SELECT insests.PatNum,
(SUM(CASE WHEN (insests.ProcDate<=@AsOf AND insests.ProcDate>=(@AsOf-INTERVAL 30 DAY)) THEN insests.InsEst ELSE 0 END)) AS InsEst0to30,
(SUM(CASE WHEN (insests.ProcDate<(@AsOf-INTERVAL 30 DAY) AND insests.ProcDate>=(@AsOf-INTERVAL 60 DAY)) THEN insests.InsEst ELSE 0 END)) AS InsEst31to60,
(SUM(CASE WHEN (insests.ProcDate<(@AsOf-INTERVAL 60 DAY) AND insests.ProcDate>=(@AsOf-INTERVAL 90 DAY)) THEN insests.InsEst ELSE 0 END)) AS InsEst61to90,
(SUM(CASE WHEN (insests.ProcDate<(@AsOf-INTERVAL 90 DAY)) THEN insests.InsEst ELSE 0 END)) AS InsEstOver90
/*Get patient level InsPayEst and Writeoffs from unreceived claims.*/
FROM(
SELECT cp.PatNum,SUM(cp.InsPayEst+cp.Writeoff) AS InsEst,cp.ProcDate
FROM claimproc cp
WHERE cp.PatNum!=0
AND ((cp.Status=0 AND cp.ProcDate<=@AsOf) OR (cp.Status=1 AND cp.DateCP>@AsOf))
AND cp.ProcDate<=@ASOf
GROUP BY cp.PatNum,cp.ProcDate
) insests
GROUP BY insests.PatNum
) C ON C.PatNum=B.PatNum
INNER JOIN patient p ON p.PatNum=B.PatNum
INNER JOIN patient g ON g.PatNum=p.Guarantor
GROUP BY g.PatNum
) D
WHERE ABS(D.FamBal)>0.005

UNION ALL

SELECT 1 AS ItemOrder,'--------------------------------','---------------','---------------','---------------','---------------','---------------','---------------','---------------','---------------','---------------'

UNION ALL

SELECT 2 AS ItemOrder,'Totals:' AS Guarantor,
FORMAT(ROUND(SUM(
((CASE WHEN D.FamCredits<D.FamChargesOver90+D.FamCharges61to90+D.FamCharges31to60 THEN D.FamCharges0to30
WHEN D.FamChargesOver90+D.FamCharges61to90+D.FamCharges31to60+D.FamCharges0to30<=D.FamCredits THEN 0
ELSE D.FamChargesOver90+D.FamCharges61to90+D.FamCharges31to60+D.FamCharges0to30-D.FamCredits END)-COALESCE(FamInsEst0to30,0)))
,2),2) AS Pat0to30,
FORMAT(ROUND(SUM(
((CASE WHEN D.FamCredits<D.FamChargesOver90+D.FamCharges61to90 THEN D.FamCharges31to60
WHEN D.FamChargesOver90+D.FamCharges61to90+D.FamCharges31to60<=D.FamCredits THEN 0
ELSE D.FamChargesOver90+D.FamCharges61to90+D.FamCharges31to60-D.FamCredits END)-COALESCE(FamInsEst31to60,0)))
,2),2) AS Pat31to60,
FORMAT(ROUND(SUM(
((CASE WHEN D.FamCredits<=D.FamChargesOver90 THEN D.FamCharges61to90
WHEN D.FamChargesOver90+D.FamCharges61to90<=D.FamCredits THEN 0
ELSE D.FamChargesOver90+D.FamCharges61to90-D.FamCredits END)-COALESCE(FamInsEst61to90,0)))
,2),2) AS Pat61to90,
FORMAT(ROUND(SUM(
((CASE WHEN D.FamCredits>=D.FamChargesOver90 THEN 0
ELSE D.FamChargesOver90-D.FamCredits END)-COALESCE(FamInsEstOver90,0)))
,2),2) AS PatOver90,
FORMAT(ROUND(SUM(D.FamBal),2),2) AS Total,
FORMAT(ROUND(COALESCE(SUM(FamInsEst0to30),0),2),2) AS InsEst0to30,
FORMAT(ROUND(COALESCE(SUM(FamInsEst31to60),0),2),2) AS InsEst31to60,
FORMAT(ROUND(COALESCE(SUM(FamInsEst61to90),0),2),2) AS InsEst61to90,
FORMAT(ROUND(COALESCE(SUM(FamInsEstOver90),0),2),2) AS InsEstOver90
FROM (
/*Get the family level charges, credits, and ins estimates*/
SELECT g.PatNum,g.LName,g.FName,SUM(B.PatBal) AS FamBal,SUM(B.Charges0to30) AS FamCharges0to30,SUM(B.Charges31to60) AS FamCharges31to60,
SUM(B.Charges61to90) AS FamCharges61to90,SUM(B.ChargesOver90) AS FamChargesOver90,SUM(B.Credits) AS FamCredits,
SUM(COALESCE(C.InsEst0to30,0)) AS FamInsEst0to30,SUM(COALESCE(C.InsEst31to60,0)) AS FamInsEst31to60,
SUM(COALESCE(C.InsEst61to90,0)) AS FamInsEst61to90,SUM(COALESCE(C.InsEstOver90,0)) AS FamInsEstOver90
FROM (
/*Get Patient level charges and credits*/
SELECT RawPatTrans.PatNum,SUM(RawPatTrans.TranAmount) AS PatBal,
(SUM(CASE WHEN (RawPatTrans.TranAmount>0 AND RawPatTrans.TranDate<=@AsOf AND RawPatTrans.TranDate>=(@AsOf-INTERVAL 30 DAY)) THEN RawPatTrans.TranAmount ELSE 0 END)) AS Charges0to30,
(SUM(CASE WHEN (RawPatTrans.TranAmount>0 AND RawPatTrans.TranDate<(@AsOf-INTERVAL 30 DAY) AND RawPatTrans.TranDate>=(@AsOf-INTERVAL 60 DAY)) THEN RawPatTrans.TranAmount ELSE 0 END)) AS Charges31to60,
(SUM(CASE WHEN (RawPatTrans.TranAmount>0 AND RawPatTrans.TranDate<(@AsOf-INTERVAL 60 DAY) AND RawPatTrans.TranDate>=(@AsOf-INTERVAL 90 DAY)) THEN RawPatTrans.TranAmount ELSE 0 END)) AS Charges61to90,
(SUM(CASE WHEN (RawPatTrans.TranAmount>0 AND RawPatTrans.TranDate<(@AsOf-INTERVAL 90 DAY)) THEN RawPatTrans.TranAmount ELSE 0 END)) AS ChargesOver90,
-(SUM(CASE WHEN (RawPatTrans.TranAmount<0 AND RawPatTrans.TranDate<=@AsOf) THEN RawPatTrans.TranAmount ELSE 0 END)) AS Credits
FROM (
/*Get the completed procedure dates and charges for the entire office history*/
SELECT 'Fee' AS TranType,pl.PatNum AS PatNum,pl.ProcDate AS TranDate,
pl.ProcFee*(pl.UnitQty+pl.BaseUnits) AS TranAmount
FROM procedurelog pl
WHERE pl.ProcStatus=2
UNION ALL
/*Paysplits for the entire office history*/
SELECT 'Pay' AS TranType,ps.PatNum AS PatNum,ps.DatePay AS TranDate,
-ps.SplitAmt AS TranAmount
FROM paysplit ps
WHERE ps.PayPlanNum=0 /*Only splits not attached to payment plans*/
UNION ALL
/*Get the adjustment dates and amounts for the entire office history*/
SELECT 'Adj' AS TranType,a.PatNum AS PatNum,a.AdjDate AS TranDate,
a.AdjAmt AS TranAmount
FROM adjustment a
WHERE a.AdjAmt!=0
UNION ALL
/*Claim payments for the entire office history*/
SELECT 'InsPay' AS TranType,cp.PatNum AS PatNum,cp.DateCp AS TranDate,
-cp.InsPayAmt-cp.Writeoff AS TranAmount
FROM claimproc cp
WHERE cp.Status IN (1,4,5,7)/*received,supplemental,CapClaim, or CapComplete*/
UNION ALL
/*Payment plan principal for the entire office history on or before the given date*/
SELECT 'PayPlan' AS TranType,pp.PatNum AS PatNum,pp.PayPlanDate AS TranDate,
-pp.CompletedAmt AS TranAmount
FROM payplan pp
WHERE pp.CompletedAmt!=0
GROUP BY pp.PayPlanNum
) RawPatTrans
WHERE TranDate<=@AsOf
GROUP BY RawPatTrans.PatNum
) B
LEFT JOIN (
SELECT insests.PatNum,
(SUM(CASE WHEN (insests.ProcDate<=@AsOf AND insests.ProcDate>=(@AsOf-INTERVAL 30 DAY)) THEN insests.InsEst ELSE 0 END)) AS InsEst0to30,
(SUM(CASE WHEN (insests.ProcDate<(@AsOf-INTERVAL 30 DAY) AND insests.ProcDate>=(@AsOf-INTERVAL 60 DAY)) THEN insests.InsEst ELSE 0 END)) AS InsEst31to60,
(SUM(CASE WHEN (insests.ProcDate<(@AsOf-INTERVAL 60 DAY) AND insests.ProcDate>=(@AsOf-INTERVAL 90 DAY)) THEN insests.InsEst ELSE 0 END)) AS InsEst61to90,
(SUM(CASE WHEN (insests.ProcDate<(@AsOf-INTERVAL 90 DAY)) THEN insests.InsEst ELSE 0 END)) AS InsEstOver90
/*Get patient level InsPayEst and Writeoffs from unreceived claims.*/
FROM(
SELECT cp.PatNum,SUM(cp.InsPayEst+cp.Writeoff) AS InsEst,cp.ProcDate
FROM claimproc cp
WHERE cp.PatNum!=0
AND ((cp.Status=0 AND cp.ProcDate<=@AsOf) OR (cp.Status=1 AND cp.DateCP>@AsOf))
AND cp.ProcDate<=@ASOf
GROUP BY cp.PatNum,cp.ProcDate
) insests
GROUP BY insests.PatNum
) C ON C.PatNum=B.PatNum
INNER JOIN patient p ON p.PatNum=B.PatNum
INNER JOIN patient g ON g.PatNum=p.Guarantor
GROUP BY g.PatNum
) D
WHERE ABS(D.FamBal)>0.005
) E
ORDER BY E.ItemOrder,E.Guarantor;

drtmz

instead of putting in the date, is there something i can put in so its the current date?

You can put in today's date or try
SET @AsOf=CURDATE();

drtmz

Thanks!

BTW Our founder, Jordan, turned me on to a way to control the AR many, many moons ago. Send bills out every day.
Get get most of the payments upfront before the patient's go back for Tx. The balances from insurance payments or whatever are then billed every day. Insurances we follow up on after two weeks.

drtmz