query for insurance percentages

[drtbar]

I checked the list of query examples, but did not find this query. I tried #67 but got "unhandled exception."What I want is a query to show all my insurance carriers and what percentage of my patients have that insurance. I'd also like to know how many patients have no insurance. Is there a way to do this?

Thanks in advance.

[nathansparks]

I will have some queries posted for you within an hour or so
nathan

[nathansparks]

OK, the question is, are you wanting to count primary insurance only or any insurance? If you count any insurance then your percentages will be greater than 100% added together. Also it might be more effective if we limit it to a time period where you have seen the patients, typically 2 years or whatever you define, because it is less informative to include former patients.

[nathansparks]

Use this for the count of patients with each insurance, the unhandled exception eror you got was because there was an error in your SQL, probably you put a date in wrong format, try this exactly as written, save to favorites, THEN change the date each time you run
Call me if you have any problems, or reply in this thread

I will update this to do percentages next week, but you can do the math in excel in the meantime
Nathan

SET @Start='2007-01-01' , @Before='2008-01-01';
/*Adjust above Dates AS needed*/
SELECT carrier.CarrierName, COUNT(DISTINCT claimproc.PatNum) AS 'Patients'
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 >= @Start
AND procedurelog.ProcDate < @Before
AND ProcStatus=2
GROUP BY CarrierName
ORDER BY CarrierName;

For the patient counts with and without insurance, use this
SET @Start='2007-01-01' , @Before='2008-01-01';
SELECT (SELECT COUNT(DISTINCT PatNum)
FROM procedurelog pl
WHERE pl.ProcDate Between @Start AND @Before
AND ProcStatus=2) AS 'TotPatients',
(SELECT COUNT(DISTINCT p.PatNum)
FROM procedurelog pl
INNER JOIN patient p ON p.PatNum=pl.PatNum
WHERE pl.ProcDate Between @Start AND @Before
AND ProcStatus=2 AND p.HasIns='I') AS 'InsPatients',
(SELECT COUNT(DISTINCT p.PatNum)
FROM procedurelog pl
INNER JOIN patient p ON p.PatNum=pl.PatNum
WHERE pl.ProcDate Between @Start AND @Before
AND ProcStatus=2 AND NOT(p.HasIns='I')) AS 'NoInsPatients';

[drtbar]

Awesome, thanks for the reply. I've only had OpenDental since April so limiting patients is probably not necessary. Will check out the query and let you know. Thanks again.

[drtbar]

Query works great. I did export to excel to calculate percentages. Thanks again.