hygiene report

[abelaguilar]

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

[nathansparks]

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;

[abelaguilar]

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.