Appointment Count by Patient Category and Appointment Type

We need a report that provides total number of a particular type of appointment by pt category for a specified period.

Be sure to adjust the date range in the WHERE clause for your desired period.

This query uses the GROUP BY ROLLUP (  ) statement to give grand and subtotals as well as the results for each specific patient category, provider type, and service code combination. Wherever you see NULL in the result set, interpret as “ALL”.

SELECT
  COALESCE(d.categcode,'None') AS "PtCategory",
  COALESCE(f.ApptType,'None') AS "ApptType",
  COUNT(distinct a.detailnum) AS ApptCount
FROM
  sos.appt_d a
  JOIN sos.patients c ON a.ptnum = c.ptnum
  LEFT OUTER JOIN sos.ptcategs d ON c.ptcategnum = d.ptcategnum 
  LEFT OUTER JOIN sos.appttypes f ON a.appttypenum = f.appttypenum
WHERE 
  a.adate BETWEEN '2012-01-01' AND '2012-12-31'
  AND c.licnum = 101
GROUP BY ROLLUP ("PtCategory","ApptType")