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"
