ICD-9 Diagosis Codes In Use and ICD-10 Suggestions from CMS GEMs

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