Diagnosis Queries
Back to Query Directory
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
rv_patients a
LEFT OUTER JOIN dx b ON a.dx1 = b.dxnum
LEFT OUTER JOIN dx c ON a.dx2 = c.dxnum
LEFT OUTER JOIN dx d ON a.dx3 = d.dxnum
LEFT OUTER JOIN dx e ON a.dx4 = e.dxnum
a.lastname, a.firstname, a.id,
b.dxcode AS "DxCode1",
c.dxcode AS "DxCode2",
d.dxcode AS "DxCode3",
e.dxcode AS "DxCode4"
FROM
rv_patients a
LEFT OUTER JOIN dx b ON a.dx1 = b.dxnum
LEFT OUTER JOIN dx c ON a.dx2 = c.dxnum
LEFT OUTER JOIN dx d ON a.dx3 = d.dxnum
LEFT OUTER JOIN 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
patients a
LEFT OUTER JOIN ptcsu b ON a.ptnum = b.ptnum
LEFT OUTER JOIN dx c ON b.dx1 = c.dxnum
LEFT OUTER JOIN dx d ON b.dx2 = d.dxnum
LEFT OUTER JOIN dx e ON b.dx3 = e.dxnum
LEFT OUTER JOIN dx f ON b.dx4 = e.dxnum
a.*,
c.dxcode AS "dxcode1",
d.dxcode AS "dxcode2",
e.dxcode AS "dxcode3",
f.dxcode AS "dxcode4"
FROM
patients a
LEFT OUTER JOIN ptcsu b ON a.ptnum = b.ptnum
LEFT OUTER JOIN dx c ON b.dx1 = c.dxnum
LEFT OUTER JOIN dx d ON b.dx2 = d.dxnum
LEFT OUTER JOIN dx e ON b.dx3 = e.dxnum
LEFT OUTER JOIN 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
patients a
LEFT OUTER JOIN ptcsu b ON a.ptnum = b.ptnum
LEFT OUTER JOIN dx c ON b.dx1 = c.dxnum
LEFT OUTER JOIN dx d ON b.dx2 = d.dxnum
LEFT OUTER JOIN dx e ON b.dx3 = e.dxnum
LEFT OUTER JOIN dx f ON b.dx4 = e.dxnum
WHERE
b.ptcsunum = (SELECT MAX(ptcsunum) FROM ptcsu WHERE ptnum = a.ptnum)
a.*,
c.dxcode AS "dxcode1",
d.dxcode AS "dxcode2",
e.dxcode AS "dxcode3",
f.dxcode AS "dxcode4"
FROM
patients a
LEFT OUTER JOIN ptcsu b ON a.ptnum = b.ptnum
LEFT OUTER JOIN dx c ON b.dx1 = c.dxnum
LEFT OUTER JOIN dx d ON b.dx2 = d.dxnum
LEFT OUTER JOIN dx e ON b.dx3 = e.dxnum
LEFT OUTER JOIN dx f ON b.dx4 = e.dxnum
WHERE
b.ptcsunum = (SELECT MAX(ptcsunum) FROM ptcsu WHERE ptnum = a.ptnum)
CategoryQueries
