Query 126 - Does pv.Abbr need to be here?

[V Suite]

Hygiene production sum - Query 126 - Does pv.Abbr need to be here? I don't think it is doing what it should be doing.

Code: Select all

SET @FromDate='2008-06-01' , @ToDate='2008-06-30';
SELECT Count(*),pv.Abbr, pc.ProcCode, pc.AbbrDesc, AVG(pl.ProcFee) as '$Ave. Fee' , SUM(pl.ProcFee) AS '$Tot. Prod' FROM patient 
INNER JOIN procedurelog  pl ON patient.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum
INNER JOIN provider pv ON pl.ProvNum=pv.ProvNum
WHERE pl.ProcStatus = '2' AND (pl.ProcDate >=@FromDate
AND pl.ProcDate <= @ToDate) AND pc.IsHygiene=1
GROUP BY pc.ProcCode
ORDER BY ProcCode;

[V Suite]

Does any one use this query?
If so, it does not appear that pv.Abbr works, could anyone advise me please, thanks.

[jordansparks]

pv.Abbr just means the provider abbreviation. That column looks like it's working fine to me in the result. Is there something about the result that looks wrong to you?

[V Suite]

To my understanding, the query should list each proc code, and each provider who performs that treatment code.
So,
100, #1, D1110 , Pro
189, #2, D1110, Pro
115, #3, D1110, Pro
25, #1, D1112, Pro2
12, #2, D1112, Pro2
7, #1, D1120, ProChild
9, #3, D1120, ProChild
etc

However it only lists one provider for each proc code, so it is not possible to tabulate the comparative performance.
404, #2, D1110, Pro
37, #1, D1112, Pro2
16, #3, D1120, ProChild

The numbers listed are the correct sums but the totals are ascribed to the provider with most of each procedure.

I just figured it out. To get the list separated by provider, simply change the second to last line to
GROUP BY pc.ProcCode, pv.ProvNum

Jordan, Please update Query #126 to remedy this.