This query provides appointment counts by DataSet, Resource and Month. Only non-cancelled appointments for existing patients are included in the count. The use of GROUP BY ROLLUP provides us with grand total and subtotals as well as the detail for each month. Wherever you see NULL rather than one of the categories, read it as “ALL”. For example, if you have NULL in the DataSet column, that means the total is for all DataSets.
-- appt count BY dataset, resource, AND month SELECT licnum AS "DataSet", resourcecode, YEAR(apptdate) AS "Year", MONTH(apptdate) AS "Month", COUNT(*) AS "Number" FROM sos.rv_appts WHERE apptdate BETWEEN '2010-06-01' AND '2011-07-28' AND ptnum IS NOT NULL AND cancelflag <> 1 GROUP BY ROLLUP ("DataSet",ResourceCode, "Year", "Month") ORDER BY "DataSet",ResourceCode, "Year", "Month"