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
Avg Insurance Payment
Re: Avg Insurance Payment
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
Re: Avg Insurance Payment
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.
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.
Re: Avg Insurance Payment
Here is the query showing one row per procedure code:
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.
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
Re: Avg Insurance Payment
I just ran this query for 1 month and compared the numbers to month production and income. The totals are totally different?