Appointment Counts By Month
Back to Query Directory
We are looking at the Televox phone reminder system and want to get a good idea on what our costs will be. It would be helpful to know the number of appointments scheduled for a given month? I would also like to run it by Provider type (M.D., Ph.D., M.A.).
Note that you can adjust the date range in the examples below. In addition, these count only those appointments for patients already in the system, not ad-hoc write-in's. If you want every appointment (which will include meetings and other non-service appointments if you put them in the scheduler), then remove the "AND ptnum IS NOT NULL" clause.
// appointment count BY month
SELECT YEAR(adate) AS "Year", MONTH(adate) AS "Month", COUNT(*)
FROM appt_d
WHERE adate BETWEEN '2000-09-1' AND '2009-08-31' AND ptnum IS NOT NULL
GROUP BY "Year", "Month"
ORDER BY "Year", "Month";
SELECT YEAR(adate) AS "Year", MONTH(adate) AS "Month", COUNT(*)
FROM appt_d
WHERE adate BETWEEN '2000-09-1' AND '2009-08-31' AND ptnum IS NOT NULL
GROUP BY "Year", "Month"
ORDER BY "Year", "Month";
// appt count BY provider type AND month
SELECT provtypecode, YEAR(adate) AS "Year", MONTH(adate) AS "Month", COUNT(*)
FROM appt_d a JOIN providers b ON a.providernum = b.providernum LEFT OUTER JOIN provtype c ON b.provtypenum = c.provtypenum
WHERE adate BETWEEN '2000-09-1' AND '2009-08-31' AND ptnum IS NOT NULL
GROUP BY provtypecode, "Year", "Month"
ORDER BY provtypecode, "Year", "Month"
SELECT provtypecode, YEAR(adate) AS "Year", MONTH(adate) AS "Month", COUNT(*)
FROM appt_d a JOIN providers b ON a.providernum = b.providernum LEFT OUTER JOIN provtype c ON b.provtypenum = c.provtypenum
WHERE adate BETWEEN '2000-09-1' AND '2009-08-31' AND ptnum IS NOT NULL
GROUP BY provtypecode, "Year", "Month"
ORDER BY provtypecode, "Year", "Month"
CategoryQueries
