Avg Insurance Payment

For complex topics that regular users would not be interested in. For power users and database administrators.

Avg Insurance Payment

Postby golg » Sun Mar 05, 2017 3: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
golg
 
Posts: 2
Joined: Sun Mar 05, 2017 3:17 am

Re: Avg Insurance Payment

Postby cmcgehee » Mon Mar 06, 2017 9: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
User avatar
cmcgehee
 
Posts: 126
Joined: Tue Aug 25, 2015 5:06 pm

Re: Avg Insurance Payment

Postby golg » Mon Mar 06, 2017 10: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.
golg
 
Posts: 2
Joined: Sun Mar 05, 2017 3:17 am

Re: Avg Insurance Payment

Postby cmcgehee » Mon Mar 06, 2017 11: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
User avatar
cmcgehee
 
Posts: 126
Joined: Tue Aug 25, 2015 5:06 pm

Re: Avg Insurance Payment

Postby 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?
enamelrod
 
Posts: 435
Joined: Tue Jul 24, 2007 9:51 am


Return to Advanced Topics

Who is online

Users browsing this forum: No registered users and 1 guest