Utilization Report Query

[jpugeda]

Hi,

We recently purchase Open dental and we didn't know much about it. When we input all our HMO insurance we didn't label them as Capitation. When we try to do our monthly capitation utilization report it didn't produce anything. We now know that we have to label our HMO insurance as Capitation in order to produce that report.

I am wondering if I can have the query for the Capitation report so I can do some revision of my own. we need to submit a capitation report for all of our HMO for last month. The doctor also want the same report generated not only for our HMO insurance but also for every insurance that we carry (private).

May be you can give me a same format report query for any insurance I want not just for Capitation.

Please let me know if this possible

Thanks in advance

June

[jordansparks]

Basically like this:

SELECT carrier.CarrierName,CONCAT(CONCAT(patSub.LName,', '),patSub.FName)
,patSub.SSN,CONCAT(CONCAT(patPat.LName,', '),patPat.FName)
,patPat.Birthdate,procedurecode.ProcCode,procedurecode.Descript
,procedurelog.ToothNum,procedurelog.Surf,procedurelog.ProcDate
,procedurelog.ProcFee,procedurelog.ProcFee-claimproc.WriteOff
FROM procedurelog,patient AS patSub,patient AS patPat
,insplan,carrier,procedurecode,claimproc
WHERE procedurelog.PatNum = patPat.PatNum
AND claimproc.ProcNum = procedurelog.ProcNum
AND claimproc.PlanNum = insplan.PlanNum
AND claimproc.Status = 7
AND claimproc.NoBillIns = 0
AND insplan.Subscriber = patSub.PatNum
AND insplan.CarrierNum = carrier.CarrierNum
AND procedurelog.CodeNum = procedurecode.CodeNum
AND carrier.CarrierName LIKE '%carriernamegoeshere%'
AND procedurelog.ProcDate >= '2007-12-01'
AND procedurelog.ProcDate <= '2007-12-31'
AND insplan.PlanType = 'c'
AND procedurelog.ProcStatus = 2

Make sure to change the two dates and the carrier name to match your situation.