I need a count of the number of undischarged patients who have one or more dx codes, by dataset and program. We store the program in UserSort2 in the Patient Info screen. It does not matter whether the diagnosis is found in the first, second, third, of fourth Dx field. Sometimes I need the data in just a summary format, but other times I need the names and id’s of the patients in each group.
There are three queries below. The first is the list of patients, which is straightforward enough. We use a conditional expression for the second column so if there is no data, or just spaces, in UserSort2, we can display the word “None” instead. In the WHERE clause we have two IN expressions. These expressions evaluate to TRUE if the value on the left is found in the list on the right in parentheses. In our example, we are looking for just the Dx codes ‘296.22’ or ‘296.23’, but you can replace them with one or more codes of interest to you. Be sure to enclose each Dx code in apostrophes, and separate them with commas.
Next, we have a summary version of the same query, using the GROUP BY ROLLUP( ) syntax to provide group totals and subtotals. In the resulting matrix of results remember that anywhere you see “(NULL)”, you should interpret it as meaning “All”. Just as with the list query, be sure to adjust the Dx codes in the WHERE condition to meet your needs.
Finally, we have a consolidation of the two queries above using the UNION keyword to combine the results of the two queries. In order to do so, it is necessary to match the order and type of columns in the two result sets. In this case, we simply insert some dummy strings for columns 4, 5, and 6 in the summary query. Another quirk of UNIONed queries is that the ORDER BY clause just uses column numbers rather than column names.
Here is the first query, which includes a detailed list of patient names and ID’s:
SELECT
b.licnum AS "Dataset",
(IF TRIM(b.usersort2) = '' THEN 'None' ELSE COALESCE(b.usersort2,'None') END IF) AS "Program",
a.DxCode,
b.lastname AS "Last Name",
b.firstname AS "First Name",
b.id AS "Account ID",
1 AS "Total"
FROM
sos.dx a, sos.rv_patients b
WHERE
a.dxnum IN (b.dx1,b.dx2,b.dx3,b.dx4)
AND "Dataset" > 100
AND b.dischargedate IS NULL
AND a.dxcode IN ('296.22','296.23')
ORDER BY
"Dataset", "Program", a.dxcode, "Last Name", "First Name", "Account ID"
Next, this one is just the summary counts:
SELECT
b.licnum AS "Dataset",
(IF TRIM(b.usersort2) = '' THEN 'None' ELSE COALESCE(b.usersort2,'None') END IF) AS "Program",
a.DxCode,
COUNT(b.id) AS "Total"
FROM
sos.dx a, sos.rv_patients b
WHERE
a.dxnum IN (b.dx1,b.dx2,b.dx3,b.dx4)
AND "Dataset" > 100
AND b.dischargedate IS NULL
AND a.dxcode IN ('296.22','296.23')
GROUP BY
ROLLUP("Dataset", "Program", a.dxcode)
ORDER BY
"Dataset", "Program", a.dxcode
Finally, below we have the consolidated query, using UNION to combine the results of the two separate queries above:
SELECT
b.licnum AS "Dataset",
(IF TRIM(b.usersort2) = '' THEN 'None' ELSE COALESCE(b.usersort2,'None') END IF) AS "Program",
a.DxCode,
b.lastname AS "Last Name",
b.firstname AS "First Name",
b.id AS "Account ID",
1 AS "Total"
FROM
sos.dx a, sos.rv_patients b
WHERE
a.dxnum IN (b.dx1,b.dx2,b.dx3,b.dx4)
AND "Dataset" > 100
AND b.dischargedate IS NULL
and a.dxcode in ('296.22','296.23')
UNION
SELECT
b.licnum AS "Dataset",
(IF TRIM(b.usersort2) = '' THEN 'None' ELSE COALESCE(b.usersort2,'None') END IF) AS "Program",
a.DxCode,
'',
'',
'COUNT...',
COUNT(b.id) AS "Total"
FROM
sos.dx a, sos.rv_patients b
WHERE
a.dxnum IN (b.dx1,b.dx2,b.dx3,b.dx4)
AND "Dataset" > 100
AND b.dischargedate IS NULL
and a.dxcode in ('296.22','296.23')
GROUP BY
ROLLUP("Dataset", "Program", a.dxcode)
ORDER BY
1,2,3,4,5,6
The queries above all require you to enter specific diagnoses in the WHERE condition. Instead, let’s say that you want to return ANY diagnosis starting with 296 or 305. To do that, we can replace the line:
AND a.dxcode IN (‘296.22′,’296.23’)
with the following:
AND (a.dxcode LIKE ‘296%’ OR a.dxcode LIKE ‘305%’)
Within those parentheses you can add as many OR a.dxcode LIKE ‘…’ as you want. The key is that the LIKE keyword sets up a condition in which you can use percent signs (%) to match any characters – what we call a “wildcard”. Here are some more examples:
LIKE ‘ABC%’ … matches any character string starting with “ABC”.
LIKE ‘%ABC’ … matches any character string ending with “ABC”.
LIKE ‘%ABC%’ … matches any character string containing “ABC” in any position.
A variation of the above queries might be to limit the results to patients who had been seen during some specified date range. To do that we would add another condition in the WHERE clause like this:
AND b.ptnum IN (SELECT ptnum
FROM journal
WHERE trantype = ‘S’ AND trandate BETWEEN ‘2014-01-01’ AND ‘2014-06-30’)
Adding that new condition to the WHERE clause of the above queries would look like:
WHERE
a.dxnum IN (b.dx1,b.dx2,b.dx3,b.dx4)
AND "Dataset" > 100
AND b.dischargedate IS NULL
AND a.dxcode in ('296.22','296.23')
AND b.ptnum IN (SELECT ptnum FROM journal
WHERE trantype = 'S'
AND trandate BETWEEN '2014-01-01' AND '2014-06-30')
