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.
Writeoff total per insurance company?
Re: Writeoff total per insurance company?
Query 1048 from our examples page should be a good one for you:
1051 will give you more detail on the patient level.
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;
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
Re: Writeoff total per insurance company?
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?
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?
Re: Writeoff total per insurance company?
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;