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")

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.