Appointment Counts By Month

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
  sos.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
  sos.appt_d a JOIN sos.providers b ON a.providernum = b.providernum
  LEFT OUTER JOIN sos.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"