I need another query, please !!!!

mzrdmd · Post by **mzrdmd** » Thu Mar 06, 2008 6:01 am

Can anyone write a query for me, or does one already exist somewhere, that would allow me to see how many patients I have that are covered by a particular insurance company AND what my procedure mix has been for those patients ?

I need this because United Concordia has sent me a letter saying that I am going more crowns, one surface, two surface, and three surface restorations than the "norm", and I need to gather this information for myself so that I can fight with them about it.

I have already gotten a list of the number of procedures that I have done in total, and I also have a list of all of my UC patients, but I don't know how to determine the number of which procedures were done ON UC patients, other than doing it manually, which will take forever and I know that a query for OD will spit it out in about 2 seconds, if not faster.

If anyone wants more details of my fight, let me know, or go to http://www.dentaltown.com. I detailed everything in a thread called "Letter from insurance company - UGH! " The more we get together and fight, the stronger we will become. (For instance, they are complaining that, out of 100 patients, I do an average of 23.08 crowns, while the "norm" is 21.95. So, out of 100 patients, I do 1.13 more crowns than the "norm". They are making a big deal out of this, because it costs them money. But enough of my rant. Sorry !)

So, if anyone can write a query like that, I'd appreciate it.

Lastly, since it seems to me like I'm on here asking for queries to be written all the time, if anyone can teach me how to write queries, I will be happy to write them myself and make them available to all. This way, I won't have to bother everyone so often, and can contribute instead of being a parasite.

Thanks,

Mike

Jorgebon · Post by **Jorgebon** » Thu Mar 06, 2008 10:50 am

I read your post in Dental Town a couple of days ago and my wife and I have been thinking about what we would do if we received the same letter. I think that unless United Concordia has a big chunk of your business, the best thing would be to cancel your contract with them. Anyway, I edited one of the queries from the examples page and came up with this. Let me know if it works for you:

SET @FromDate= '2007-01-01', @ToDate='2007-12-31';
SELECT definition.ItemName,procedurecode.ProcCode,
procedurecode.Descript,Count(*),
AVG(procedurelog.ProcFee) AS $AvgFee,
SUM(procedurelog.ProcFee) AS $TotFee
FROM procedurelog,procedurecode,definition,patient,insplan,carrier
WHERE procedurelog.ProcStatus =2
AND procedurelog.patnum=patient.patnum
AND patient.patnum=insplan.subscriber
AND insplan.carriernum=carrier.carriernum
AND carrier.carriername LIKE '%United Concordia%'
AND procedurelog.CodeNum=procedurecode.CodeNum
AND definition.DefNum=procedurecode.ProcCat
AND procedurelog.ProcDate >= @FromDate
AND procedurelog.Procdate <= @ToDate
GROUP BY procedurecode.ProcCode
ORDER BY definition.ItemOrder,procedureCode.ProcCode

Jorge Bonilla, DMD

drtech · Post by **drtech** » Thu Mar 06, 2008 11:45 am

I agree. Don't put up with that.

drtech · Post by **drtech** » Thu Mar 06, 2008 11:57 am

Change one capitalization at the end or it will crash.
Corrected:

SET @FromDate= '2007-01-01', @ToDate='2007-12-31';
SELECT definition.ItemName,procedurecode.ProcCode,
procedurecode.Descript,Count(*),
AVG(procedurelog.ProcFee) AS $AvgFee,
SUM(procedurelog.ProcFee) AS $TotFee
FROM procedurelog,procedurecode,definition,patient,insplan,carrier
WHERE procedurelog.ProcStatus =2
AND procedurelog.patnum=patient.patnum
AND patient.patnum=insplan.subscriber
AND insplan.carriernum=carrier.carriernum
AND carrier.carriername LIKE '%United Concordia%'
AND procedurelog.CodeNum=procedurecode.CodeNum
AND definition.DefNum=procedurecode.ProcCat
AND procedurelog.ProcDate >= @FromDate
AND procedurelog.Procdate <= @ToDate
GROUP BY procedurecode.ProcCode
ORDER BY definition.ItemOrder,procedurecode.ProcCode

mzrdmd · Post by **mzrdmd** » Fri Mar 07, 2008 8:22 pm

Jorge, I saw that you had replied to my thread on DT, and hoped that you would find this one, too. Thanks for your help.

Drtech, thanks for your help and your support, as well.

Unfortunately, UC patients account for about a quarter of my practice. The one major employer in town is the hospital, and they have UC. I'm still not sure what I'm going to do yet, as I am still gathering my own data and am still too angry to trust myself to reply professionally and in a manner that would be in my best interest. I don't want to burn any bridges that I might have to retreat across in the future.

I will run this and see if it gives me what I need. I will post a reply either way to let you know.

Thanks a million,

Mike

mzrdmd · Post by **mzrdmd** » Sat Mar 08, 2008 10:48 am

OK, I ran the above query, and it gave everything that I need except the actual count of UC patients that I saw in the given time frame. I think that there is a query listed on the query page that will give me that info. Will report back after I check that out.

Thanks again,

Mike

Jorgebon · Post by **Jorgebon** » Sat Mar 08, 2008 11:58 am

You could use query #26 or #41 from the examples page. If you need the number of patients, try this:

SET @pos=0;
SELECT @pos:=@pos+1 as numberofpatients, carrier.CarrierName,patient.*
FROM patient,carrier,insplan
WHERE patient.PatNum=insplan.Subscriber
AND insplan.CarrierNum=carrier.CarrierNum
AND carrier.CarrierName LIKE '%United Concordia%'
ORDER BY carrier.CarrierName

You would need to add a couple of lines to limit the dates they came to your office. I'll see if I can do that tomorrow and I'll post it for you.
Jorge Bonilla, DMD

OK, I got home early so I changed the query to read like this for number of patients with United Concordia who were treated in 2007:

SET @pos=0;
SELECT @pos:=@pos+1 as numberofpatients, carrier.CarrierName,patient.*
FROM patient,carrier,insplan,procedurelog
WHERE patient.PatNum=insplan.Subscriber
AND insplan.CarrierNum=carrier.CarrierNum
AND carrier.CarrierName LIKE '%United Concordia%'
AND patient.PatNum=procedurelog.PatNum
AND procedurelog.ProcDate>='2007-01-01'
AND procedurelog.ProcDate<='2007-12-31'
GROUP BY patient.PatNum
ORDER BY patient.LName

Jorgebon · Post by **Jorgebon** » Sat Mar 08, 2008 7:20 pm

And if you want to know what you did to each of the patients that the previous query counted, use this query and it will give you the name of each patient with the procedures done:

SET @FromDate= '2007-01-01', @ToDate='2007-12-31';
SELECT patient.PatNum,definition.ItemName,procedurecode.ProcCode,
procedurecode.Descript,procedurelog.ProcFee
FROM procedurelog,procedurecode,definition,patient,insplan,carrier
WHERE procedurelog.ProcStatus =2
AND procedurelog.patnum=patient.patnum
AND patient.patnum=insplan.subscriber
AND insplan.carriernum=carrier.carriernum
AND carrier.carriername LIKE '%United Concordia%'
AND procedurelog.CodeNum=procedurecode.CodeNum
AND definition.DefNum=procedurecode.ProcCat
AND procedurelog.ProcDate >= @FromDate
AND procedurelog.Procdate <= @ToDate
ORDER BY patient.LName, patient.FName, procedurecode.ProcCode

Jorge Bonilla, DMD