Count of Intakes by Year and Month

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"

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.