SQL Script to build mappings between SNOMED-CT and ICD9cm

[Duy Dinh]

USE sct_icd9cm_mappings;

/* Populate mapping table with information about mappings

• a mapping m = (c_s, c_t, rel, string_source, string_target)
• where
• c_s : concept id from source
• c_t : concept id from target
• rel : relationship between c_s and c_t
• string_source : denotes a string representing attributes of each source concept. Each attribute is separated from one to another by a pipe symbol and associated with a label (e.g., FULLYSPECIFIEDNAME, TERM, ...)
• string_target : same as previous but for each target concept
  */
  CREATE TABLE IF NOT EXISTS sct_jan_2010_icd9cm_2009 (
  CONCEPTSOURCEID int(11) NOT NULL COMMENT 'The identifier of concept source',
  CONCEPTTARGETID int(11) NOT NULL,
  RELATIONSHIP int(11) DEFAULT NULL COMMENT '0: Unmappable\n1: Equivalent\n2: Narrow-to-Broad\n3: Broad-to-Narrow\n4: Partial Overlapped',
  STRINGSOURCE longtext COMMENT 'Attributes in source concept, each one is separated by a ''|'' symbol',
  STRINGTARGET longtext COMMENT 'Attributes in target concept, each one is separated by a ''|'' symbol',
  PRIMARY KEY (CONCEPTSOURCEID,CONCEPTTARGETID)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8sct_jan_2010_icd9cm_2009;

/** Delete or remove objects /
DROP FUNCTION IF EXISTS getTerms_20100131;
DROP FUNCTION IF EXISTS getNotes_20100131;
DROP FUNCTION IF EXISTS getIncludes_20100131;
DROP FUNCTION IF EXISTS getExcludes_20100131;

/ Create functions /
DELIMITER $$
/Get terms having a concept ID as varchar(12) in table descriptions_january **/
CREATE FUNCTION getTerms_20100131(ID VARCHAR(12)) RETURNS longtext
BEGIN
DECLARE s longtext;
SELECT GROUP_CONCAT(CONCAT(TERM, "#[TERM]") SEPARATOR "|") into s
FROM snomedct_2010.descriptions_january as sct
WHERE sct.CONCEPTID like ID;
return s;
END
$$

CREATE FUNCTION getNotes_20100131(cID VARCHAR(45)) RETURNS longtext
BEGIN
DECLARE s longtext;
SELECT GROUP_CONCAT(CONCAT(notes, "#[NOTES]") SEPARATOR "|") into s
FROM icd9cm.2009_codes
WHERE ID=cID
GROUP BY ID;
return s;
END
$$

CREATE FUNCTION getIncludes_20100131(cID VARCHAR(45)) RETURNS longtext
BEGIN
DECLARE s longtext;
SELECT GROUP_CONCAT(CONCAT(Includes, "#[INCLUDES]") SEPARATOR "|") into s
FROM icd9cm.2009_codes
WHERE ID=cID
GROUP BY ID;
return s;
END
$$

CREATE FUNCTION getExcludes_20100131(cID VARCHAR(45)) RETURNS longtext
BEGIN
DECLARE s longtext;
SELECT GROUP_CONCAT(CONCAT(Excludes, "#[EXCLUDES]") SEPARATOR "|") into s
FROM icd9cm.2009_codes
WHERE ID=cID
GROUP BY ID;
return s;
END
$$

DELIMITER ;

/** Delete old mappings /
DELETE FROM sct_jan_2010_icd9cm_2009;

/** Populating mappings with VALID mappings /
INSERT INTO sct_jan_2010_icd9cm_2009
(
SELECT sct.ID, icd.ID, cm.mapadvice, CONCAT(sct.FULLYSPECIFIEDNAME, "#[FULLYSPECIFIEDNAME]", "|", getTerms_20100131(sct.CONCEPTID)),
CONCAT(icd.title, "#[title]", "|", getNotes_20100131(icd.ID), "|", getIncludes_20100131(icd.ID), "|", getExcludes_20100131(icd.ID))
FROM sct_icd9cm_mappings.crossmaps_icd9_20100131 AS cm,
sct_icd9cm_mappings.crossmaptargets_icd9_20100131_unique_codes AS mt,
icd9cm.2009_codes AS icd,
snomedct_2010.concepts_january AS sct
WHERE (cm.maptargetid=mt.targetid) AND (cm.mapconceptid = sct.conceptid) AND (mt.targetcodes = icd.code)
);

valid mappings

SELECT count(*) FROM sct_jan_2010_icd9cm_2009;
SELECT * FROM sct_jan_2010_icd9cm_2009;