I would like a simple count of the number of intakes each month.
Easy enough. The GROUP BY contains the ROLLUP keyword which gives sub and grand totals as well as the monthly counts. Wherever you see “NULL” in the result set, think “All”. The WHERE clause restrict the output to the past two years (730 days). You can easily change that to a date range or different number of days if you like.
SELECT
YEAR(pt.intakedate) AS "YEAR",
MONTH(pt.intakedate) AS "MONTH",
COUNT(DISTINCT pt.ptnum) AS "N"
FROM
sos.patients pt
WHERE
(pt.intakedate >= (TODAY()-730))
GROUP BY
ROLLUP ("YEAR","MONTH")
ORDER BY -"YEAR", -"MONTH"
