Appointment Count by DataSet, Resource, and Month

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"

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.