Count of Intakes for Past 365 Days With Specified Dx

We need the total number of new (the past 365 days) intakes that were given a primary or secondary diagnosis of 304.4, including sub-diagnoses. In addition to the grand total, we also would like a breakdown by provider.

The following query demonstrates some simple date arithmetic in the WHERE clause to provide a dynamic calculation of the date 365 days ago. In addition, we are using the GROUP BY ROLLUP ( … ) structure to get both the grand total and the subtotals in the same result set. When you run the query you will see a top row where NULL appears in the provider and provcode columns. In this context, whereever you see NULL, it actually should be interpreted as “all”. The first row then shows the number of intakes for “all providers” and “all provcodes,” in other words, the grand total.

SELECT
  UPPER(provlname) + ', ' + provfname AS "Provider",provcode, COUNT(DISTINCT pt.ptnum) as "N"
FROM
  sos.patients pt
  JOIN sos.ptcsu csu ON pt.ptnum = csu.ptnum
  JOIN sos.ptcsudx ptdx ON csu.ptcsunum = ptdx.ptcsunum
  JOIN sos.providers prv ON pt.providernum = prv.providernum
WHERE
  (pt.intakedate >= (TODAY()-365))
  AND (dxcode1 LIKE '304.4%' OR dxcode2 LIKE '304.4%')
GROUP BY
  ROLLUP ("Provider",provcode)

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.