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)
