Writeoff total per insurance company?

For users or potential users.
Post Reply
khdilger
Posts: 112
Joined: Wed May 05, 2010 5:56 am

Writeoff total per insurance company?

Post by khdilger » Tue Jun 27, 2017 4:57 am

Anybody have a query to find the amount of writeoffs for a perticular insurance company? I contract with Delta premier and one other insurance company because the other company was a local thing for years. This local insurance has gotten really bad on writeoffs. I bet at least 25% of my patients have this insurance.

I need to find out how much I am writing off for these patients to determine what impact dropping the insurance would have.

User avatar
cmcgehee
Posts: 711
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: Writeoff total per insurance company?

Post by cmcgehee » Tue Jun 27, 2017 8:19 am

Query 1048 from our examples page should be a good one for you:

Code: Select all

/*1048 Net production and writeoffs by insurance carrier.*/
/*Query code written/modified: 07/20/2015*/
SET @FromDate='2017-01-01', @ToDate='2017-07-01'; /*Set date here YYYY-MM-DD*/
SELECT carrier.CarrierName,
SUM(claimproc.FeeBilled-claimproc.WriteOff) AS '$Net Production_',
SUM(claimproc.WriteOff) AS '$WriteOff_'
FROM claimproc
INNER JOIN insplan ON claimproc.PlanNum = insplan.PlanNum
INNER JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum
WHERE claimproc.Status IN (1,4,0) /*received or supplemental or notreceived*/  
AND claimproc.ProcDate BETWEEN @FromDate AND @ToDate
GROUP BY carrier.CarrierNum 
ORDER BY carrier.CarrierName;
1051 will give you more detail on the patient level.

Code: Select all

/*1051 Total writeoff amount by carrier with writeoffs for each patient on that carrier. Date range is based on procedure date.*/
/*Query code written/modified: 08/07/2015*/
SET @StartDate='2017-01-01', @EndDate='2017-12-31';
SELECT A.Name,A.WriteOff AS 'WriteOff'
FROM (
	SELECT 1 AS 'ItemOrder',ca.CarrierName AS 'Name',FORMAT(ROUND(SUM(cp.WriteOff),2),2) AS 'WriteOff',ca.CarrierName,ca.CarrierNum
	FROM claimproc cp
	INNER JOIN procedurelog pl ON cp.ProcNum=pl.ProcNum
		AND pl.ProcDate BETWEEN @StartDate AND @EndDate
	INNER JOIN claim cl ON cp.ClaimNum=cl.ClaimNum
	INNER JOIN insplan ip ON cl.PlanNum=ip.PlanNum
	INNER JOIN carrier ca ON ip.CarrierNum=ca.CarrierNum
	GROUP BY ca.CarrierNum

	UNION ALL

	SELECT 2,CONCAT(p.LName,', ',p.FName),FORMAT(ROUND(SUM(cp.WriteOff),2),2),ca.CarrierName,ca.CarrierNum
	FROM claimproc cp
	INNER JOIN procedurelog pl ON cp.ProcNum=pl.ProcNum
		AND pl.ProcDate BETWEEN @StartDate AND @EndDate
	INNER JOIN claim cl ON cp.ClaimNum=cl.ClaimNum
	INNER JOIN insplan ip ON cl.PlanNum=ip.PlanNum
	INNER JOIN carrier ca ON ip.CarrierNum=ca.CarrierNum
	INNER JOIN patient p ON cp.PatNum=p.PatNum
	GROUP BY ca.CarrierNum,cp.PatNum
	HAVING SUM(cp.WriteOff)>0.005
	
	UNION ALL
	
	SELECT 3,"------------------","----------",ca.CarrierName,ca.CarrierNum
	FROM claimproc cp
	INNER JOIN procedurelog pl ON cp.ProcNum=pl.ProcNum
		AND pl.ProcDate BETWEEN @StartDate AND @EndDate
	INNER JOIN claim cl ON cp.ClaimNum=cl.ClaimNum
	INNER JOIN insplan ip ON cl.PlanNum=ip.PlanNum
	INNER JOIN carrier ca ON ip.CarrierNum=ca.CarrierNum
	GROUP BY ca.CarrierNum
) A
ORDER BY A.CarrierName,A.CarrierNum,A.ItemOrder
Chris McGehee
Open Dental Software
http://www.opendental.com

khdilger
Posts: 112
Joined: Wed May 05, 2010 5:56 am

Re: Writeoff total per insurance company?

Post by khdilger » Tue Jun 27, 2017 8:50 am

Net production would be production after writeoffs. The production numbers that this
query is calculating is the number after writeoffs correct? And is also letting you know the
writeoff amount.

In order to determine how much I am actually writing off and how much money I am loosing due to insurance do I simply need to take
out the "-claimproc.WriteOff" and that will give Gross production?

User avatar
cmcgehee
Posts: 711
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: Writeoff total per insurance company?

Post by cmcgehee » Tue Jun 27, 2017 9:15 am

For the first query, net production is calculated as the fees billed minus the writeoff amount. I added a fee billed column and a percent writeoff column. The percent writeoff column = amount written off / amount billed.

Code: Select all

/*1048 Net production and writeoffs by insurance carrier.*/
/*Query code written/modified: 07/20/2015*/
SET @FromDate='2017-01-01', @ToDate='2017-07-01'; /*Set date here YYYY-MM-DD*/
SELECT carrier.CarrierName,
SUM(claimproc.FeeBilled) AS '$Fee Billed_',
SUM(claimproc.FeeBilled-claimproc.WriteOff) AS '$Net Production_',
SUM(claimproc.WriteOff) AS '$WriteOff_',
FORMAT(ROUND(SUM(claimproc.WriteOff)/SUM(claimproc.FeeBilled)*100,1),1) AS '% WriteOff'
FROM claimproc
INNER JOIN insplan ON claimproc.PlanNum = insplan.PlanNum
INNER JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum
WHERE claimproc.Status IN (1,4,0) /*received or supplemental or notreceived*/  
AND claimproc.ProcDate BETWEEN @FromDate AND @ToDate
GROUP BY carrier.CarrierNum 
ORDER BY carrier.CarrierName;
Chris McGehee
Open Dental Software
http://www.opendental.com

Post Reply