Hello
I would like to know how to run a report for number of procedures for 2015 per category. Can you help? Thank you
as an example Category fixed crowns = #
Report for number of procedures per category
Re: Report for number of procedures per category
top 100 procedures monthly or yearly
=================================
SET @FromDate='2015-01-01' , @ToDate='2015-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
====================================================
click on ProcCode to sort them by the code of each procedure in each category
=================================
SET @FromDate='2015-01-01' , @ToDate='2015-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
====================================================
click on ProcCode to sort them by the code of each procedure in each category
Re: Report for number of procedures per category
want to know more details, run this query: "procedures by date range"
==================================================================
SET @FromDate='2015-01-01' , @ToDate='2015-12-10'; SELECT CONCAT(patient.LName, ', ',patient.FName, ' ', patient.MiddleI) As Patient, pl.ProcDate, pv.Abbr,pc.ProcCode, pc.AbbrDesc, toothnum, surf, pl.ProcFee 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) ORDER BY ProcDate,patient.LName, patient.FName ASC;
==================================================================
OD offer many examples of different queries on their samples webpage
http://opendentalsoft.com:1942/ODQueryL ... yList.aspx
==================================================================
SET @FromDate='2015-01-01' , @ToDate='2015-12-10'; SELECT CONCAT(patient.LName, ', ',patient.FName, ' ', patient.MiddleI) As Patient, pl.ProcDate, pv.Abbr,pc.ProcCode, pc.AbbrDesc, toothnum, surf, pl.ProcFee 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) ORDER BY ProcDate,patient.LName, patient.FName ASC;
==================================================================
OD offer many examples of different queries on their samples webpage
http://opendentalsoft.com:1942/ODQueryL ... yList.aspx