Insurance Plan Report - Current
Insurance Plan Report - Current
Hi All. We are considering the ramifications of dropping an insurance network. When we run a report by insurance plan of this particular network it appears to show anyone who ever had the specific plan versus patients on the plan at the present time. In other words, patient names are coming up with this plan in the report list, but if we go into their account they have either a different insurance now, no insurance etc... Aside from going through each name and looking up each individual, is there a way around this/a way to run a report of current patients with this plan as active? Thanks in advance.
Re: Insurance Plan Report - Current
The internal insurance plans report includes patients with inactive plans. To get the information you're looking for, I would submit a query request on our website: http://opendentalsoft.com:1942/ODQueryR ... tForm.aspx.
Re: Insurance Plan Report - Current
Thank you! Will do.
Re: Insurance Plan Report - Current
We use this modified query #356 for end of year. It includes email, phone numbers and addresses, if you want to contact or mail your patients
After you export the results to an excel documents, you can sort it by the insurance names
adjust the dates accordingly
After you export the results to an excel documents, you can sort it by the insurance names
adjust the dates accordingly
Code: Select all
/*356 List of Patients that have not been in since a specific date that have insurance benefits remaining.
Includes Insurance Carrier's Name, Patient's Name, Three Phone Numbers, Benefits Remaining,
TP Remaining and Last seen date. Assumes everyone has calendar year benefits*/
SET @FromDate='2017-12-28';
SELECT p.PatNum AS 'Pat#',
p.LName, p.FName, p.HmPhone, p.WkPhone, p.WirelessPhone, p.Email,
p. Address, p.Address2, p.City, p.State, p.Zip,
DATE_FORMAT(lastseen.LastSeen,'%m/%d/%Y')AS LastSeen,
annualmax.AnnualMax '$AnnualMax_',
used.AmtUsed '$AmountUsed_',
annualmax.AnnualMax-COALESCE(used.AmtUsed,0) '$AmtRemaining_',
c.CarrierName
FROM patient p
INNER JOIN patplan ON p.PatNum=patplan.PatNum
INNER JOIN inssub ON inssub.InsSubNum=patplan.InsSubNum
INNER JOIN insplan ip ON ip.PlanNum=inssub.PlanNum
INNER JOIN carrier c ON c.CarrierNum=ip.CarrierNum
INNER JOIN (
SELECT benefit.PlanNum,
MAX(benefit.MonetaryAmt) AS AnnualMax
FROM benefit
LEFT JOIN covcat ON covcat.CovCatNum = benefit.CovCatNum
WHERE benefit.BenefitType = 5 /* limitation */
AND benefit.TimePeriod = 2 /* calendar year */
AND (covcat.EbenefitCat=1 OR ISNULL(covcat.EbenefitCat))
AND benefit.MonetaryAmt > 0 /* (-1) indicates empty */
GROUP BY benefit.PlanNum
) annualmax ON annualmax.PlanNum=inssub.PlanNum
LEFT JOIN (
SELECT patplan.PatPlanNum,
SUM(claimproc.InsPayAmt) AS AmtUsed
FROM claimproc
INNER JOIN inssub ON claimproc.InsSubNum=inssub.InsSubNum
INNER JOIN patplan ON inssub.InsSubNum=patplan.InsSubNum
AND patplan.PatNum=claimproc.PatNum
WHERE claimproc.Status IN (1, 3, 4) -- Rec, Adj, Supp.
AND YEAR(claimproc.ProcDate)=YEAR(CURDATE())
AND claimproc.InsPayAmt!=0
GROUP BY patplan.PatPlanNum
) used ON used.PatPlanNum=patplan.PatPlanNum
INNER JOIN (
SELECT p.PatNum,
MAX(procdate) AS LastSeen
FROM patient p
INNER JOIN procedurelog pl ON pl.PatNum=p.PatNum
AND pl.ProcStatus=2 /*complete*/
AND p.PatStatus=0 /*active patient*/
GROUP BY pl.PatNum
) lastseen ON lastseen.PatNum=p.PatNum AND lastseen.LastSeen<@FromDate
WHERE PatStatus=0
ORDER BY c.CarrierName;
Last edited by rhaber123 on Fri Oct 13, 2017 2:02 pm, edited 3 times in total.
Re: Insurance Plan Report - Current
Query #71 Patients' Names and Insurance Names only
adjust the dates accordingly
adjust the dates accordingly
Code: Select all
/*71*/ SET @Start='2000-01-01' , @Before='2017-12-31';
/*patients name and insurance Adjust above Dates as needed*/
SET @pos=0;
SELECT @pos:=@pos+1 AS Count, carrier.CarrierName, patient.PatNum
FROM carrier, insplan, claim, claimproc, patient
WHERE insplan.PlanNum=claim.PlanNum AND
insplan.CarrierNum=carrier.CarrierNum AND
claimproc.ClaimNum=claim.ClaimNum AND
claimproc.PatNum=patient.PatNum AND
/*this is in case we need additional patient information*/
/*you can add any patient or insurance fields to the SELECT statement*/
ProcDate >= @Start AND
ProcDate < @Before
GROUP BY patient.PatNum, CarrierName
ORDER BY CarrierName;
Last edited by rhaber123 on Fri Oct 13, 2017 2:02 pm, edited 1 time in total.
Re: Insurance Plan Report - Current
query #69 ----/*69 Count of patients by Carrier with procedures completed in date range*/
change the dates accordingly
change the dates accordingly
Code: Select all
/*69 Count of patients by Carrier with procedures
completed in date range*/
SET @FromDate='2000-01-01' , @ToDate='2017-12-31';
/*Adjust above Dates AS needed*/
SELECT carrier.CarrierName, COUNT(DISTINCT claimproc.PatNum) AS 'Patients', carrier.Phone
FROM carrier
INNER JOIN insplan ON carrier.CarrierNum=insplan.CarrierNum
INNER JOIN claim ON insplan.PlanNum=claim.PlanNum
INNER JOIN claimproc ON claim.ClaimNum=claimproc.ClaimNum
INNER JOIN procedurelog ON claimproc.ProcNum=procedurelog.ProcNum
INNER JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum
AND (procedurelog.ProcDate BETWEEN @FromDate AND @ToDate)
AND ProcStatus=2
GROUP BY CarrierName
ORDER BY CarrierName;
Re: Insurance Plan Report - Current
Income by insurance company for date range
Code: Select all
/*70*/ SET @Start='2017-01-01' , @Before='2017-12-30';
/*Adjust above Dates as needed*/
SELECT CarrierName, SUM(CheckAmt) AS $Income
FROM claimpayment
WHERE CheckDate >= @Start
AND CheckDate < @Before
GROUP BY CarrierName;