Avg Insurance Payment

For complex topics that regular users would not be interested in. For power users and database administrators.
Post Reply
golg
Posts: 2
Joined: Sun Mar 05, 2017 2:17 am

Avg Insurance Payment

Post by golg » Sun Mar 05, 2017 2:25 am

Hi there, I was looking to get the AVG and SUM of insurance payments for the procedures, The query 14 was used and modified, Please let me know if the Query is correct Thank you.

SET @FromDate='2016-01-01' , @ToDate='2017-01-13';
SELECT definition.ItemName,
procedurecode.ProcCode,
procedurecode.Descript,
Count(*) AS Tot,
AVG( claim.InsPayAmt) AS `$AvgInsPayAmt`,
SUM(claim.InsPayAmt) AS `$TotInsPayAmt`
FROM procedurelog,
procedurecode,
definition,
claimproc,
claim
WHERE (procedurelog.CodeNum = procedurecode.CodeNum)
AND (definition.DefNum = procedurecode.ProcCat)
AND (claimproc.ClaimNum = claim.ClaimNum)
AND (procedurelog.ProcNum = claimproc.ProcNum)
AND ( procedurelog.ProcStatus = 2)
AND (procedurelog.ProcDate BETWEEN @FromDate AND @ToDate)
ORDER BY definition.ItemOrder ASC, procedurecode.ProcCode ASC

User avatar
cmcgehee
Posts: 711
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: Avg Insurance Payment

Post by cmcgehee » Mon Mar 06, 2017 8:01 am

I made one change: COUNT(*) to COUNT(DISTINCT procedurelog.ProcNum). That way you will get the count of the procedures completed. My next question is if you want to see one row for each procedure code or if you want one row for the entire practice.

Code: Select all

SET @FromDate='2016-01-01' , @ToDate='2017-01-13';
SELECT definition.ItemName,
procedurecode.ProcCode,
procedurecode.Descript,
COUNT(DISTINCT procedurelog.ProcNum) AS TotProcs,
AVG( claim.InsPayAmt) AS `$AvgInsPayAmt`,
SUM(claim.InsPayAmt) AS `$TotInsPayAmt`
FROM procedurelog,
procedurecode,
definition,
claimproc,
claim
WHERE (procedurelog.CodeNum = procedurecode.CodeNum)
AND (definition.DefNum = procedurecode.ProcCat)
AND (claimproc.ClaimNum = claim.ClaimNum)
AND (procedurelog.ProcNum = claimproc.ProcNum)
AND ( procedurelog.ProcStatus = 2)
AND (procedurelog.ProcDate BETWEEN @FromDate AND @ToDate)
ORDER BY definition.ItemOrder ASC, procedurecode.ProcCode ASC
Chris McGehee
Open Dental Software
http://www.opendental.com

golg
Posts: 2
Joined: Sun Mar 05, 2017 2:17 am

Re: Avg Insurance Payment

Post by golg » Mon Mar 06, 2017 9:12 am

Thank you Chris.
I wanted to know one row for each procedure code .
Additionally, I am not able to see all the InsPayAmts when I use the query.
looking in the claim table (Select * from claim), there are over 400 records,
Not all of them are shown in the procedure query.
Thanks.

User avatar
cmcgehee
Posts: 711
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: Avg Insurance Payment

Post by cmcgehee » Mon Mar 06, 2017 10:35 am

Here is the query showing one row per procedure code:

Code: Select all

SET @FromDate='2016-01-01' , @ToDate='2017-01-13';
SELECT definition.ItemName,
procedurecode.ProcCode,
procedurecode.Descript,
COUNT(DISTINCT procedurelog.ProcNum) AS TotProcs,
AVG( claim.InsPayAmt) AS `$AvgInsPayAmt`,
SUM(claim.InsPayAmt) AS `$TotInsPayAmt`
FROM procedurelog,
procedurecode,
definition,
claimproc,
claim
WHERE (procedurelog.CodeNum = procedurecode.CodeNum)
AND (definition.DefNum = procedurecode.ProcCat)
AND (claimproc.ClaimNum = claim.ClaimNum)
AND (procedurelog.ProcNum = claimproc.ProcNum)
AND ( procedurelog.ProcStatus = 2)
AND (procedurelog.ProcDate BETWEEN @FromDate AND @ToDate)
GROUP BY procedurecode.CodeNum
ORDER BY definition.ItemOrder ASC, procedurecode.ProcCode ASC
A claim payment will not show on this query if the claim payment is not attached to a procedure. This happens when in the Edit Claim window in the Enter Payment section, you click "As Total" to enter insurance payments.
Chris McGehee
Open Dental Software
http://www.opendental.com

enamelrod
Posts: 462
Joined: Tue Jul 24, 2007 9:51 am

Re: Avg Insurance Payment

Post by enamelrod » Wed Mar 22, 2017 6:14 am

I just ran this query for 1 month and compared the numbers to month production and income. The totals are totally different?

Post Reply