This query returns a list of diagnoses that are currently in use in Claim Setups in OM and in DSM Profiles in CM. These are the ones that you will have to replace by the new ICD-10 diagnosis codes on or before October of 2014.
The query itself is a main query that lists diagnoses that appear in any of a bunch of fields in OM and CM and a subquery used in the WHERE clause. The subquery contains a simple query for the dxnum in each possible location, and all the simple queries are UNIONed together to get a single list.
The second query below is a revision of the first, adding a column for the ICD-10 suggestion, according to the CMS GEMs crosswalk.
SELECT
dxcode, dxtype, dxdesc
FROM
sos.dx
WHERE
dxnum IN
(
SELECT dx1 FROM sos.ptcsu
UNION
SELECT dx2 FROM sos.ptcsu
UNION
SELECT dx3 FROM sos.ptcsu
UNION
SELECT dx4 FROM sos.ptcsu
UNION
SELECT box67 FROM sos.ptubsu
UNION
SELECT box67 FROM sos.ptubsu
UNION
SELECT box67 FROM sos.ptubsu
UNION
SELECT box67 FROM sos.ptubsu
UNION
SELECT box67 FROM sos.ptubsu
UNION
SELECT box67 FROM sos.ptubsu
UNION
SELECT box67 FROM sos.ptubsu
UNION
SELECT box67 FROM sos.ptubsu
UNION
SELECT box67 FROM sos.ptubsu
UNION
SELECT box67 FROM sos.ptubsu
UNION
SELECT fl67i FROM sos.ptubsu
UNION
SELECT fl67i FROM sos.ptubsu
UNION
SELECT fl67i FROM sos.ptubsu
UNION
SELECT fl67i FROM sos.ptubsu
UNION
SELECT fl67i FROM sos.ptubsu
UNION
SELECT fl67i FROM sos.ptubsu
UNION
SELECT fl67i FROM sos.ptubsu
UNION
SELECT fl67i FROM sos.ptubsu
UNION
SELECT fl67i FROM sos.ptubsu
UNION
SELECT dxnum FROM sos.v_ptdsmlist WHERE dxnum <> 0
)
ORDER BY dxcode
The following variation adds a JOIN to the CMS GEMS table to show the suggested ICD-10 code for each of the current ICD-9 codes in use in your data.
SELECT
a.dxcode, a.dxtype, a.dxdesc, b.icd10 AS "GEMS suggested ICD10", c.longdesc AS "ICD10 Desc"
FROM
sos.dx a
LEFT OUTER JOIN sos.v_ICD9to10 b ON a.dxcode = b.icd9
LEFT OUTER JOIN sos.dx c ON b.icd10 = c.dxcode
WHERE
a.dxnum IN
(
SELECT dx1 FROM sos.ptcsu
UNION
SELECT dx2 FROM sos.ptcsu
UNION
SELECT dx3 FROM sos.ptcsu
UNION
SELECT dx4 FROM sos.ptcsu
UNION
SELECT box67 FROM sos.ptubsu
UNION
SELECT box67 FROM sos.ptubsu
UNION
SELECT box67 FROM sos.ptubsu
UNION
SELECT box67 FROM sos.ptubsu
UNION
SELECT box67 FROM sos.ptubsu
UNION
SELECT box67 FROM sos.ptubsu
UNION
SELECT box67 FROM sos.ptubsu
UNION
SELECT box67 FROM sos.ptubsu
UNION
SELECT box67 FROM sos.ptubsu
UNION
SELECT box67 FROM sos.ptubsu
UNION
SELECT fl67i FROM sos.ptubsu
UNION
SELECT fl67i FROM sos.ptubsu
UNION
SELECT fl67i FROM sos.ptubsu
UNION
SELECT fl67i FROM sos.ptubsu
UNION
SELECT fl67i FROM sos.ptubsu
UNION
SELECT fl67i FROM sos.ptubsu
UNION
SELECT fl67i FROM sos.ptubsu
UNION
SELECT fl67i FROM sos.ptubsu
UNION
SELECT fl67i FROM sos.ptubsu
UNION
SELECT dxnum FROM sos.v_ptdsmlist
WHERE dxnum <> 0 ) ORDER BY a.dxcode