Patient Demographic Statistics

We are completing the NMHS Survey and need to run a query that gives us a break down of the following patient information for April 2014:  total male and female clients; clients aged 0-17, 18-64, & 65 and older; totals for ethnicity (coded in patient UD field 25 (including “Unknown”); and totals for race (including “Unknown”). I would need these statistics for just those patients seen for services during a particular date range.

This is not an unusual request. The query below uses two interesting techniques through which we combine several different small queries using, the UNION syntax, to create the final result set. In addition, in each of the small queries we have a subquery that provides a list of patients who were seen in the desired date range. That list is used in each of the WHERE clauses to limit the population on which the totals are based.  Note that when you use UNION, there can be only one ORDER BY, and it must be on the last query. In addition, unlike with standalone queries, you ORDER by column number instead of column name. Another essential when using UNION is that each of the smaller queries must have the same number of columns and the data type of each column must be the same in each of the queries you are UNIONing.

IMPORTANT — be sure to adjust the date range in every one of the queries! In this example there are five demographic queries and each one includes the date range that you will have to adjust.

--- clients seen for period
SELECT
   '** Clients Seen' AS "Description",
   '' AS "Group",
   COUNT(DISTINCT c.ptnum) AS "Totals"
FROM
   sos.patients c
WHERE
   ptnum IN (SELECT ptnum 
             FROM sos.journal a 
             WHERE trantype = 'S' AND trandate BETWEEN '2014-04-01' AND '2014-04-30' AND amount > 0)


--- Racial Identity
UNION
SELECT
   '3. Racial Identity' AS "Description",
   (IF COALESCE(c.fld6,'') = '' THEN 'Unknown' ELSE c.fld6 END IF) AS "Group",
   COUNT(*) AS "Totals"
FROM
   sos.patients b LEFT OUTER JOIN sos.uddatapt c ON b.uddatanum = c.uddatanum
WHERE
   ptnum IN (SELECT ptnum 
             FROM sos.journal  
             WHERE trantype = 'S' AND trandate BETWEEN '2014-04-01' AND '2014-04-30' AND amount > 0)
GROUP BY "Group"


--- Ethnicity
UNION
SELECT
   '4. Ethnic Identity' AS "Description",
   (IF COALESCE(c.fld25,'') = '' THEN 'Unknown' ELSE c.fld25 END IF) AS "Group",
   COUNT(*) AS "Totals"
FROM
   sos.patients b LEFT OUTER JOIN sos.uddatapt c ON b.uddatanum = c.uddatanum
WHERE
   ptnum IN (SELECT ptnum 
             FROM sos.journal  
             WHERE trantype = 'S' AND trandate BETWEEN '2014-04-01' AND '2014-04-30' AND amount > 0)
GROUP BY  "Group"

--- Age Group
UNION
SELECT
   '2. Age Group' AS "Description",
   (CASE 
      WHEN sos.AGEINYEARS(c.dob,'2014-04-30') BETWEEN 0 AND 17 THEN '0-17 yrs'
      WHEN sos.AGEINYEARS(c.dob,'2014-04-30') BETWEEN 18 AND 64 THEN '18-64 yrs'
      WHEN sos.AGEINYEARS(c.dob,'2014-04-30') > 64 THEN '65+ yrs'
      ELSE 'unknown'
    END) AS "Group",
   COUNT(*) AS "Totals"
FROM
   sos.patients c 
WHERE
ptnum IN (SELECT ptnum 
          FROM sos.journal  
          WHERE trantype = 'S' AND trandate BETWEEN '2014-04-01' AND '2014-04-30' AND amount > 0)
GROUP BY "Group"

---- gender
UNION
SELECT
   '1. Gender' AS "Description",
   COALESCE(sex,'unknown') AS "Group",
   COUNT(*) AS "Totals"
FROM
   sos.patients c 
WHERE
ptnum IN (SELECT ptnum 
          FROM sos.journal  
          WHERE trantype = 'S' AND trandate BETWEEN '2014-04-01' AND '2014-04-30' AND amount > 0)
GROUP BY "Group"
ORDER BY 1,2