Anyone know of a query report that will give me the number of hygiene patients or hygiene provider patients that came in the past year at least 2 or more times?
Thanks
hygiene report
-
- Posts: 87
- Joined: Tue Jun 19, 2007 3:26 pm
- Location: Manchester, GA
- Contact:
hygiene report
Abel Aguilar, DMD
http://www.DrAguilar.com
http://www.DrAguilar.com
-
- Posts: 172
- Joined: Mon Aug 04, 2008 12:39 pm
Re: hygiene report
I don't have one just like that, you would have to request it unless someone else already has it (because of the two or more appointments requirement, this is no longer a garden variety query).
It would depend upon the hygiene definition. Usually I would include proceures marked 'IsHygiene', but you also mention appointments inolving a hygenist; these are two distinct ways of looking at it. We could look at patients with either condition met (two or more appoinments where either a hygiene proc was completed or a hygenist was a provider). You misght start with this below, then add an inner join to the apt table or group by procdate and then count the results, then limit using a having clause:
Hygiene Summed by Procedure for Date range
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;
It would depend upon the hygiene definition. Usually I would include proceures marked 'IsHygiene', but you also mention appointments inolving a hygenist; these are two distinct ways of looking at it. We could look at patients with either condition met (two or more appoinments where either a hygiene proc was completed or a hygenist was a provider). You misght start with this below, then add an inner join to the apt table or group by procdate and then count the results, then limit using a having clause:
Hygiene Summed by Procedure for Date range
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;
-
- Posts: 87
- Joined: Tue Jun 19, 2007 3:26 pm
- Location: Manchester, GA
- Contact:
Re: hygiene report
Great thanks. Were defining an active patient as having visited our office at least 2 or more appintments. That's really what i'm trying to find out. HOw many active patients we have for a given date range.
Abel Aguilar, DMD
http://www.DrAguilar.com
http://www.DrAguilar.com