Diagnosis Queries

I want to do some queries that include the patients’ diagnoses. Where do I find that information?

That seems like such a simple question, but the answer is not so simple. There are several ways to get to the patient diagnoses. The easiest is if you use the RV_PATIENTS view instead of the PATIENTS table in your query. In that case, you will find integer values in RV_PATIENTS columns named DX1, DX2, DX3, and DX4. Those numbers are NOT the diagnoses, but rather they are foreign keys that provide links back to the DX table. Therefore, if you wanted the codes for those diagnoses, here is how you would query them:

SELECT
  a.lastname, a.firstname, a.id,
  b.dxcode AS "DxCode1",
  c.dxcode AS "DxCode2",
  d.dxcode AS "DxCode3",
  e.dxcode AS "DxCode4"
FROM
  sos.rv_patients a
  LEFT OUTER JOIN sos.dx b ON a.dx1 = b.dxnum
  LEFT OUTER JOIN sos.dx c ON a.dx2 = c.dxnum
  LEFT OUTER JOIN sos.dx d ON a.dx3 = d.dxnum
  LEFT OUTER JOIN sos.dx e ON a.dx4 = e.dxnum

The technical details are that the dx links are actually in the PTCSU table. The patients table is related to ptcsu, which in turn is related (4 times) to the dx table. In OM Pro, there can be many CSU's for each patient, so there can also be many different sets of diagnoses for a single patient. The query above assumes that you want the default CSU's diagnoses. In reality, however, the relationship between patient and diagnosis is more complex, as shown in this query, which will generate more than one row for any patient with more than one Claim Setup. (Note that this issue does not pertain to the standard version of OM, which has only one claim setup per patient.):

SELECT
  a.*,
  c.dxcode AS "dxcode1",
  d.dxcode AS "dxcode2",
  e.dxcode AS "dxcode3",
  f.dxcode AS "dxcode4"
FROM
  sos.patients a
  LEFT OUTER JOIN sos.ptcsu b ON a.ptnum = b.ptnum
  LEFT OUTER JOIN sos.dx c ON b.dx1 = c.dxnum
  LEFT OUTER JOIN sos.dx d ON b.dx2 = d.dxnum
  LEFT OUTER JOIN sos.dx e ON b.dx3 = e.dxnum
  LEFT OUTER JOIN sos.dx f ON b.dx4 = e.dxnum

You can limit to the default Claim setup by adding a condition "typeflag = D", or you could use a subquery to get the most recently added claim setup, using MAX(ptcsunum) or MAX(adddate):

SELECT
  a.*,
  c.dxcode AS "dxcode1",
  d.dxcode AS "dxcode2",
  e.dxcode AS "dxcode3",
  f.dxcode AS "dxcode4"
FROM
  sos.patients a
  LEFT OUTER JOIN sos.ptcsu b ON a.ptnum = b.ptnum
  LEFT OUTER JOIN sos.dx c ON b.dx1 = c.dxnum
  LEFT OUTER JOIN sos.dx d ON b.dx2 = d.dxnum
  LEFT OUTER JOIN sos.dx e ON b.dx3 = e.dxnum
  LEFT OUTER JOIN sos.dx f ON b.dx4 = e.dxnum  
WHERE
  b.ptcsunum = (SELECT MAX(ptcsunum) FROM sos.ptcsu WHERE ptnum = a.ptnum)