Patient Rescheduling Query Help

[jfdmanager]

Hello. I found the following query that shows the number of patients an hygienist saw on a particular date and the percentage of those patients that were rescheduled. I would like to expand this query to show a date range per provider and including all providers--doctors and hygienists. Is there anyone that could quickly change those two parameters for me? You'd earn my everlasting appreciation!

/*711 Count of patients that had a hygiene appointment on @DateFrom by provider and if they were rescheduled on that day.*/
SET @FromDate='2014-07-01' , @ToDate='2014-07-31';@DateFrom='2013-08-27';
SELECT COALESCE(pv2.Abbr,pv1.Abbr) AS 'Hygienist',
COUNT(a.PatNum) AS '#PatSeen',
COUNT(af.PatNum) AS '#PatRescheduled',
FORMAT((CASE WHEN NOT ISNULL(af.PatNum) THEN (COUNT(af.PatNum)/COUNT(a.PatNum))*100 ELSE 0.00 END),2) AS '%Rescheduled'
FROM appointment a
LEFT JOIN (
SELECT sl.PatNum
FROM securitylog sl
WHERE DATE(LogDateTime)=@DateFrom
AND (sl.PermType=25 OR sl.PermType=26)
GROUP BY sl.PatNum
)af ON af.PatNum=a.PatNum
LEFT JOIN provider pv1 ON a.ProvNum=pv1.ProvNum
LEFT JOIN provider pv2 ON a.ProvHyg=pv2.ProvNum
WHERE a.IsHygiene=1
AND a.AptStatus=2
AND DATE(a.AptDateTime)=@DateFrom
GROUP BY a.ProvHyg

[KevinRossen]

Are you wanting it grouped by date or provider?

[jfdmanager]

Provider.

[KevinRossen]

Try this and let me know if it works for what you're wanting.

Code: Select all

SET @FromDate='2014-07-01' , @ToDate='2014-07-31';
DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1(
ProvNum VARCHAR(8),
Seen int NOT NULL DEFAULT 0,
Sched int NOT NULL DEFAULT 0,
Rate CHAR(4) NOT NULL DEFAULT 0);
INSERT INTO t1(ProvNum,Seen)
SELECT IF(IsHygiene=1,ProvHyg,ProvNum) AS 'ProvNum', COUNT(DISTINCT PatNum) AS 'Seen'
FROM appointment WHERE (DATE(AptDateTime) BETWEEN @FromDate AND @ToDate) AND AptStatus=2
GROUP BY IF(IsHygiene=1,ProvHyg,ProvNum);
CREATE TABLE t2
SELECT IF(a1.IsHygiene=1,a1.ProvHyg,a1.ProvNum) AS 'ProvNum', COUNT(DISTINCT a2.PatNum) AS 'Sched'
FROM appointment a1 LEFT JOIN appointment a2 ON a1.PatNum=a2.PatNum
WHERE (DATE(a1.AptDateTime) BETWEEN @FromDate AND @ToDate AND a1.AptStatus=2) AND (DATE(a2.AptDateTime)>DATE(a1.AptDateTime) AND a2.AptStatus IN (1,2,4))
GROUP BY IF(a1.IsHygiene=1,a1.ProvHyg,a1.ProvNum);
UPDATE t1,t2 SET t1.Sched=t2.Sched WHERE t1.ProvNum=t2.ProvNum;
DROP TABLE IF EXISTS t2;
UPDATE t1 SET RATE=CONCAT(ROUND((Sched/Seen*100),0),'%');
SELECT * FROM t1;
DROP TABLE IF EXISTS t1,t2;

*EDIT: Added the line at the bottom of the query to drop the temp tables after query is complete.

[jfdmanager]

It's perfect! Thank you so much!

[KevinRossen]

I should mention one quirk about the report. Since hygiene/recall might appointments have two providers (dentist & hygienist), I made the decision to only apply that appointment as a count toward the hygienist, not both. In the grand scheme of things, that one appointment is more applicable toward the hygienist than the dentist. You may disagree, but that's how I built the report.