Has anyone done this type of report?
I pulled my procedures report for one year but I'd like to see if I can streamline the report.
I tried the query examples but wasn't able to find one close to this concept.
Can you point me in a direction?
Top 35 most utilized codes by revenue (Report)
Re: Top 35 most utilized codes by revenue (Report)
TRY this QUERY
OD support can assist too, but all depends on how the payments were entered in OD
this one sort it by production $$ and not by revenue $$
it is hard to find, specially if you post your payments "as a TOTAL" instead of "BY PROCEDURE"
top 100 procedures monthly or yearly:
SET @FromDate='2016-01-01' , @ToDate='2016-12-31';
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)
GROUP BY pc.ProcCode
ORDER BY Count(*)DESC Limit 100
Once the report is available, export it as an excel document, and to sort it by production, click on sort the result "Tot Prod" from largest to smallest in excel
OD support can assist too, but all depends on how the payments were entered in OD
this one sort it by production $$ and not by revenue $$
it is hard to find, specially if you post your payments "as a TOTAL" instead of "BY PROCEDURE"
top 100 procedures monthly or yearly:
SET @FromDate='2016-01-01' , @ToDate='2016-12-31';
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)
GROUP BY pc.ProcCode
ORDER BY Count(*)DESC Limit 100
Once the report is available, export it as an excel document, and to sort it by production, click on sort the result "Tot Prod" from largest to smallest in excel
Re: Top 35 most utilized codes by revenue (Report)
Thank you so much! I think this will work perfectly! Have an amazing evening!