Payment by one insurance company over a given period of time

For users or potential users.
Post Reply
msmfd
Posts: 21
Joined: Mon Feb 22, 2021 7:32 am

Payment by one insurance company over a given period of time

Post by msmfd » Thu Mar 24, 2022 11:27 am

We are considering changing our participation status with a particular insurance company. To help us weigh the pros and cons, I would like to be able to generate a report showing payments from only this insurance company for 2021. I do not see a way to do this using any of the reports features offered in Open Dental. Am I missing something, or is this not a thing? Thank you!

Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Re: Payment by one insurance company over a given period of time

Post by Tom Zaccaria » Thu Mar 24, 2022 11:45 am

Try this.
Change the Name, second to last line, and Date range, first line, to fit your needs
drtmz


/*Modified #93, Insurance and Patient income for time period summed carrier. Patient income only shows on primary insurance carrier*/
SET @FromDate='2021-1-01', @ToDate='2021-12-31';
Set @row_number=0;
SELECT
(@row_number:=@row_number+1)AS num,
A.CarrierName AS 'Carrier Name',
A.PatName AS 'Patient Name',
A.$InsPayment AS '$InsPayment_',
A.$PatPayment AS '$PatPayment_',
$InsPayment+$PatPayment AS '$Total_'
FROM (
SELECT 0 AS ItemOrder,
carrier.CarrierName,
CONCAT(patient.LName,', ',patient.FName,' ',patient.MiddleI) AS PatName,
IFNULL(SUM(claimproc.InsPayAmt),0) AS $InsPayment,
(CASE WHEN patplan.Ordinal=1 THEN COALESCE(patPayment.TotalPay,0) ELSE 0 END) AS $PatPayment
FROM patient
INNER JOIN patplan ON patplan.PatNum=patient.PatNum
INNER JOIN inssub ON patplan.InsSubNum=inssub.InsSubNum
INNER JOIN insplan ON insplan.PlanNum=inssub.PlanNum
INNER JOIN carrier ON carrier.CarrierNum=insplan.CarrierNum
INNER JOIN claimproc ON claimproc.PatNum=patient.PatNum AND claimproc.PlanNum=insplan.PlanNum
LEFT JOIN(
SELECT SUM(paysplit.SplitAmt) AS TotalPay,paysplit.PatNum
FROM paysplit
WHERE paysplit.DatePay BETWEEN @FromDate AND @ToDate
GROUP BY paysplit.PatNum
) patPayment ON patPayment.PatNum=patient.PatNum AND patPlan.Ordinal=1
WHERE patient.PatStatus=0
AND claimproc.DateCP BETWEEN @Fromdate AND @Todate
AND (claimproc.Status=1 OR claimproc.Status=4)
GROUP BY patient.PatNum,carrier.CarrierNum

UNION ALL

SELECT 1 AS ItemOrder,
'--------------------' AS CarrierName,
'-----' AS PatNum,
NULL AS $InsPayment,
NULL AS $PatPayment

UNION ALL

SELECT 2 AS ItemOrder,
'Total:' AS CarrierName,
NULL AS PatNum,
SUM(B.$InsPayment) AS $InsPayment,
SUM(B.$PatPayment) AS $PatPayment
FROM(
SELECT carrier.CarrierName,
CONCAT(patient.LName,', ',patient.FName,' ',patient.MiddleI) AS PatName,
IFNULL(SUM(claimproc.InsPayAmt),0) AS $InsPayment,
(CASE WHEN patplan.Ordinal=1 THEN COALESCE(patPayment.TotalPay,0) ELSE 0 END) AS $PatPayment
FROM patient
INNER JOIN patplan ON patplan.PatNum=patient.PatNum
INNER JOIN inssub ON patplan.InsSubNum=inssub.InsSubNum
INNER JOIN insplan ON insplan.PlanNum=inssub.PlanNum
INNER JOIN carrier ON carrier.CarrierNum=insplan.CarrierNum
INNER JOIN claimproc ON claimproc.PatNum=patient.PatNum AND claimproc.PlanNum=insplan.PlanNum
LEFT JOIN(
SELECT SUM(paysplit.SplitAmt) AS TotalPay,paysplit.PatNum
FROM paysplit
WHERE paysplit.DatePay BETWEEN @FromDate AND @ToDate
GROUP BY paysplit.PatNum
) patPayment ON patPayment.PatNum=patient.PatNum AND patPlan.Ordinal=1
WHERE patient.PatStatus=0
AND claimproc.DateCP BETWEEN @Fromdate AND @Todate
AND (claimproc.Status=1 OR claimproc.Status=4)

GROUP BY patient.PatNum,carrier.CarrierNum
)B
) A
Where A.carriername LIKE '%Cigna%'
ORDER BY A.ItemOrder,A.CarrierName,A.PatName

msmfd
Posts: 21
Joined: Mon Feb 22, 2021 7:32 am

Re: Payment by one insurance company over a given period of time

Post by msmfd » Thu Mar 24, 2022 12:43 pm

Thank you so much, this is exactly what I was looking for, to include the amounts paid by the patients. Have a great day!

Post Reply