In Collection Balances by Provider, Age Group, and Diagnosis

Would it be possible to create a query to show total due from collections accounts by primary provider, primary diagnostic code, and age groups of the patient…. 1-5; 6-12; 13-18; 19-28; 29-40; 41 to 60; above 60?

This query demonstrates several interesting features. The first is the use of a CASE expression to evaluate the appropriate age group for each patient, using a custom function, AGEINYEARS( ) that we include with the SOS database. Next is the use of COALESCE( ) to return a desired string (‘None’) when no primary provider or diagnosis has been entered. Finally, we use the GROUP BY ROLLUP(     ) structure to provide us with all possible totals. When examining the result set, you will see NULL under one or more columns on some rows. When reading the results, you should interpret NULL as meaning “ALL”. Therefore, the row that shows:

Primary Provider  = NULL
AgeGroup   = NULL
PrimaryDx = NULL
Balance Due = 100,000

should be read as the grand total of all balances (all age groups, all providers, and all Dx codes)

SELECT
  COALESCE(d.provcode,'None') AS "PrimaryProvider",
  (CASE
     WHEN sos.AGEINYEARS(c.dob,TODAY() ) BETWEEN 0 AND 5 THEN 'Age under 5'
     WHEN sos.AGEINYEARS(c.dob,TODAY() ) BETWEEN 6 AND 12 THEN 'Age 6 - 12'
     WHEN sos.AGEINYEARS(c.dob,TODAY() ) BETWEEN 13 AND 18 THEN 'Age 13 - 18'
     WHEN sos.AGEINYEARS(c.dob,TODAY() ) BETWEEN 19 AND 28 THEN 'Age 19 - 28'
     WHEN sos.AGEINYEARS(c.dob,TODAY() ) BETWEEN 29 AND 40 THEN 'Age 29 - 40'
     WHEN sos.AGEINYEARS(c.dob,TODAY() ) BETWEEN 41 AND 60 THEN 'Age 41 - 60'
     WHEN sos.AGEINYEARS(c.dob,TODAY() ) > 60 THEN 'Age above 60'
     ELSE 'Age unknown'
  END CASE) AS "AgeGroup",
  COALESCE(e.dxcode1,'None') AS "PrimaryDx",
  SUM(b.balance) AS "Balance Due"
FROM
  sos.payors a
  JOIN sos.ptpayors b ON a.payornum = b.payornum
  JOIN sos.patients c ON b.ptnum = c.ptnum
  LEFT OUTER JOIN sos.ptcsudx e ON c.ptcsunum = e.ptcsunum
  LEFT OUTER JOIN sos.providers d ON c.providernum = d.providernum
WHERE
  b.balance > 0
  AND a.collectdate IS NOT NULL
GROUP BY ROLLUP( "PrimaryProvider", "AgeGroup", "PrimaryDx")
ORDER BY "PrimaryProvider", "AgeGroup", "PrimaryDx"