Reappointment Rate Query & Help

[KevinRossen]

One of the metrics we track in our morning huddles is our reappointment rate. It's simply the % of patients we saw in a given day who left the office with an appointment of any kind scheduled in the future. Here's the query, which works as expected, I've built based on a single date:

Code: Select all

SET @DateDone='2014-02-04';
DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1(
Seen int NOT NULL DEFAULT 0,
Sched int NOT NULL DEFAULT 0,
Rate CHAR(4) NOT NULL DEFAULT 0);
INSERT INTO t1(Seen,Sched,Rate)
SELECT COUNT(DISTINCT PatNum) AS 'Seen', '' AS 'Sched', '' AS 'Rate'
FROM appointment 
WHERE DATE(AptDateTime)=@DateDone AND AptStatus=2;
CREATE TABLE t2
SELECT COUNT(DISTINCT a1.PatNum) AS 'Sched'
FROM appointment a1 LEFT JOIN appointment a2 ON a1.PatNum=a2.PatNum
WHERE (DATE(a1.AptDateTime)=@DateDone AND a1.AptStatus=2) AND (DATE(a2.AptDateTime)>@DateDone AND a2.AptStatus=1);
UPDATE t1,t2 SET t1.Sched=t2.Sched;
DROP TABLE IF EXISTS t2;
UPDATE t1 SET RATE=CONCAT(ROUND((Sched/Seen*100),0),'%');
SELECT * FROM t1

What I'd like to do now is build a report for a range of dates with the same info. Seems simple, but I'm hitting a snag. Here's the full query:

Code: Select all

SET @FromDate='2014-01-01' , @ToDate='2014-01-31';
DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1(
Date date,
Seen int NOT NULL DEFAULT 0,
Sched int NOT NULL DEFAULT 0,
Rate CHAR(4) NOT NULL DEFAULT 0);
INSERT INTO t1(Date,Seen)
SELECT DATE(AptDateTime) AS 'Date', COUNT(DISTINCT PatNum) AS 'Seen'
FROM appointment WHERE (DATE(AptDateTime) BETWEEN @FromDate AND @ToDate) AND AptStatus=2
GROUP BY Date;
CREATE TABLE t2
SELECT DATE(a1.AptDateTime) AS 'Date', 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=1)
GROUP BY Date;
UPDATE t1,t2 SET t1.Sched=t2.Sched;
DROP TABLE IF EXISTS t2;
UPDATE t1 SET RATE=CONCAT(ROUND((Sched/Seen*100),0),'%');
SELECT * FROM t1;

The sched column only pulls the count from the first date when used in query above, but it works correctly if I isolate just this section:

Code: Select all

SELECT DATE(a1.AptDateTime) AS 'Date', 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=1)
GROUP BY Date;

I think the problem might be from this WHERE clause:

Code: Select all

WHERE (DATE(a1.AptDateTime) BETWEEN @FromDate AND @ToDate AND a1.AptStatus=2) AND (DATE(a2.AptDateTime)>DATE(a1.AptDateTime) AND a2.AptStatus=1)

The logical challenge with this query is making sure the rate is calculated correctly for each date. Take, for example, a patient who had an appointment on 1-2, scheduled an appointment that day for 1-9, but did not schedule an appointment for a future date (after 1-31 in query). That example would positively affect the reappointment rate for 1-2, but negatively affect it for 1-9.

My head hurts trying to explain that above example. I could also try to make sure the date that the appointment was created falls outside that date range, but that seems too complex at this point.

Any suggestions?

[KevinRossen]

I figured it out. I left of the WHERE clause when joining t1 & t2 together. Here is the query that works:

SET @FromDate='2014-01-01' , @ToDate='2014-01-31';
DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1(
Date date,
Seen int NOT NULL DEFAULT 0,
Sched int NOT NULL DEFAULT 0,
Rate CHAR(4) NOT NULL DEFAULT 0);
INSERT INTO t1(Date,Seen)
SELECT DATE(AptDateTime) AS 'Date', COUNT(DISTINCT PatNum) AS 'Seen'
FROM appointment WHERE (DATE(AptDateTime) BETWEEN @FromDate AND @ToDate) AND AptStatus=2
GROUP BY Date;
CREATE TABLE t2
SELECT DATE(a1.AptDateTime) AS 'Date', 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=1)
GROUP BY Date;
UPDATE t1,t2 SET t1.Sched=t2.Sched WHERE t1.Date=t2.Date;
DROP TABLE IF EXISTS t2;
UPDATE t1 SET RATE=CONCAT(ROUND((Sched/Seen*100),0),'%');
SELECT * FROM t1;

[Floss]

This query was very useful for me. Unfortunately, there's a security/permissions issue when released (probably from drop table).

Here's a re-write to get around that:

Code: Select all

SET @FromDate=CURDATE()-INTERVAL 30 DAY, @ToDate=CURDATE();

SELECT t1.Date
	, t1.Seen
	, t2.Sched
	, CONCAT(ROUND((t2.Sched/t1.Seen*100),0),'%') AS 'Rate'
FROM (SELECT Date(a.AptDateTime) AS Date, COUNT(DISTINCT a.PatNum) AS Seen
	FROM appointment a WHERE (DATE(a.AptDateTime) BETWEEN @FromDate AND @ToDate) AND a.AptStatus=2
	GROUP BY Date) t1
LEFT JOIN (SELECT Date(a1.AptDateTime) AS Date2, 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=1)
	GROUP BY Date2
	) t2
/*ON a.PatNum = a2.PatNum AND a.AptDateTime = a1.AptDateTime*/
ON t1.Date = t2.Date2
GROUP BY Date;