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"