update ICD9CM table

[Duy Dinh]

/
-- @author: Duy Dinh
-- @date: 01 July 2013
-- @desc: Create and Pop up data in table ICD9CM-xxxx with data from ICD9CM source tables.
Replace the year '2009' by the year corresponding to the version of ICD9CM you want
/

USE icd9cm_2009;

drop table if exists name_synonym_relationship; -- this table contains names and synonyms denoting concepts as well as relationships between concepts
drop table if exists codes_2009; -- this table contains information about ICD9CM concepts (just a copy from the original version with slight modification)

-- create structure for table codes
delimiter $$

CREATE TABLE codes_2009 (
ID int(11) NOT NULL AUTO_INCREMENT,
chapter varchar(5) DEFAULT NULL,
block_begin varchar(5) DEFAULT NULL,
code varchar(10) DEFAULT NULL,
code_without_dot varchar(10) DEFAULT NULL,
title mediumtext,
notes longtext,
excludes longtext,
includes longtext,
PRIMARY KEY (ID),
KEY code (code)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8$$

delimiter ;

-- pop up table with data from icd9cm 2009 codes
INSERT INTO icd9cm_2009.codes_2009
(ID,
chapter,
block_begin,
code,
code_without_dot,
title,
notes,
excludes,
includes)
(
SELECT * from icd09cm.2009_codes
);

-- create structure for table name synonym relationship

delimiter $$

CREATE TABLE name_synonym_relationship (
ID int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
CONCEPTID varchar(12) DEFAULT NULL COMMENT 'concept CUI',
CONCEPTSTATUS varchar(45) DEFAULT '0',
NAME longtext COMMENT 'preferred term',
SYNONYM longtext COMMENT 'description',
SUP mediumtext COMMENT 'foreign key (Super concepts of this concept, each of which is separated by a pipe |)',
SUB mediumtext COMMENT 'foreign key (Sub concepts of this concept, each of which is separated by a pipe |)',
SIB mediumtext COMMENT 'foreign key (Sibling concepts of this concept, each of which is separated by a pipe |)',
TREENUMBER varchar(100),
PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8$$

delimiter ;

-- popup data for table containing information about (name, synonym and relationships) of each concept

-- Get terms by (int) identifier
DROP FUNCTION IF EXISTS getSynonyms;
DELIMITER $$
CREATE FUNCTION getSynonyms(CID INT(11)) RETURNS LONGTEXT
BEGIN
DECLARE s LONGTEXT;
SELECT CONCAT_WS('|', IF(LENGTH(trim(notes)), trim(notes), NULL), IF(LENGTH(trim(includes)), trim(includes), NULL),
IF(LENGTH(trim(excludes)), trim(excludes), NULL)) INTO s
FROM codes_2009 as c
WHERE c.ID=CID;
RETURN trim(s);
END
$$
DELIMITER ;

-- select getSynonyms(11303);

INSERT INTO icd9cm_2009.name_synonym_relationship
(
ID,
CONCEPTID,
-- CONCEPTSTATUS,
NAME,
SYNONYM -- ,
-- SUP,
-- SUB,
-- SIB
)
(
SELECT ID, CODE, title, getSynonyms(ID) FROM codes_2009
);

-- user defined functions
DROP FUNCTION IF EXISTS SPLIT_STR;
delimiter $$
CREATE FUNCTION SPLIT_STR(
x VARCHAR(255),
delim VARCHAR(12),
pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
delim, '');
$$
delimiter ;

-- update sup, sub and sib relationships

DROP FUNCTION IF EXISTS getSupconceptsByCUI_2009;

DELIMITER $$
-- Get super concepts of a given concept identified by an ID
CREATE FUNCTION getSupconceptsByCUI_2009(CUI varchar(10)) RETURNS MEDIUMTEXT
BEGIN
DECLARE s, ids MEDIUMTEXT;

IF instr(CUI, '.') != 0 THEN
    -- split CUI by '.'
    SET s = SPLIT_STR(CUI, '.', 1);
    SELECT group_concat(c.ID SEPARATOR "|") into IDs from codes_2009 as c
    where c.`code` like s;
END IF;
RETURN ids;

END
$$
DELIMITER ;

DROP FUNCTION IF EXISTS getSupconcepts_2009;
DELIMITER $$
-- Get super concepts of a given concept identified by an ID
CREATE FUNCTION getSupconcepts_2009(CID int(11)) RETURNS MEDIUMTEXT
BEGIN
DECLARE CUI MEDIUMTEXT;
SELECT c.code into CUI from codes_2009 as c
where c.ID = CID;

RETURN getSupconceptsByCUI_2009(CUI);

END
$$
DELIMITER ;

DROP FUNCTION IF EXISTS getSupConceptCUI_2009;
DELIMITER $$

-- Get super concepts of a given concept identified by an ID
CREATE FUNCTION getSupConceptCUI_2009(CID int(11)) RETURNS MEDIUMTEXT
BEGIN
DECLARE CUI MEDIUMTEXT;
SELECT c.code INTO CUI FROM codes_2009 AS c
WHERE c.ID = CID;
IF instr(CUI, '.') != 0 THEN
-- split CUI by '.'
SET CUI = SPLIT_STR(CUI, '.', 1);
END IF;

RETURN CUI;

END
$$
DELIMITER ;

DROP FUNCTION IF EXISTS getSubconceptsByCUI_2009;
DELIMITER $$

-- Get sub concepts of a given concept identified by an ID
CREATE FUNCTION getSubconceptsByCUI_2009(CUI varchar(10)) RETURNS MEDIUMTEXT
BEGIN
DECLARE s, ids MEDIUMTEXT;

select group_concat(id SEPARATOR "|") into ids 
from codes_2009 as c
where c.`code` like concat(CUI, '.%');
RETURN ids;

END
$$
DELIMITER ;

DROP FUNCTION IF EXISTS getSubconceptsByCUI_2009;
DELIMITER $$

-- Get sub concepts of a given concept identified by an ID
CREATE FUNCTION getSubconceptsByCUI_2009(CUI varchar(10)) RETURNS MEDIUMTEXT
BEGIN
DECLARE s, ids MEDIUMTEXT;

select group_concat(id SEPARATOR "|") into ids 
from codes_2009 as c
where c.`code` like concat(CUI, '.%');
RETURN ids;

END
$$
DELIMITER ;

DROP FUNCTION IF EXISTS getSubconcepts_2009;
DELIMITER $$

-- Get sub concepts of a given concept identified by an ID
CREATE FUNCTION getSubconcepts_2009(CID int(11)) RETURNS MEDIUMTEXT
BEGIN
DECLARE CUI MEDIUMTEXT;
SELECT c.code into CUI from codes_2009 as c
where c.ID = CID;

RETURN getSubconceptsByCUI_2009(CUI);

END
$$
DELIMITER ;

-- Get sibling concepts of a given concept identified by an ID

DROP FUNCTION IF EXISTS getSibconcepts_2009;
DELIMITER $$

-- Get sibling concepts of a given concept
CREATE FUNCTION getSibconcepts_2009(cID INT(11)) RETURNS MEDIUMTEXT
BEGIN
DECLARE ids MEDIUMTEXT;

-- get CUI of this concept
DECLARE CUI MEDIUMTEXT;

-- get the father of this concept
set CUI = getSupConceptCUI_2009(cID);

-- select sibling concepts
SELECT GROUP_CONCAT(ID SEPARATOR "|") INTO ids
FROM codes_2009 as c
WHERE ((c.ID <> cID) AND (c.`code` like concat_ws('.', CUI, '%')) AND (getSupconcepts_2009(c.ID) = getSupconcepts_2009(cID)));

RETURN ids;

END
$$
DELIMITER ;

-- update SUP, SUB and SIB for each concept
UPDATE name_synonym_relationship SET
sup = getSupconcepts_2009(ID);

UPDATE name_synonym_relationship SET
sub = getSubconcepts_2009(ID);

UPDATE name_synonym_relationship SET
sib = getSibconcepts_2009(ID);

-- select getsynonyms(11);

-- SELECT ID, CODE, title, getSynonyms(ID) FROM codes_2009
-- where id = 6;

---

-- SELECT trim(concat_ws('|', notes, includes, excludes))
-- FROM codes_2009 as c
-- WHERE c.ID=11;
--
-- SELECT * FROM icd9cm_2009.name_synonym_relationship as t;
--

-- tests super concepts

-- select getSupconceptsByCUI_2009('002.9');
-- select getSupconcepts_2009(10);
-- select getSupconcepts_2009(2);
-- select getSupconcepts_2009(1);

-- tests sub concepts
/
select getSubconceptsByCUI_2009('002');
select getSubconcepts_2009(5);
select getSubconcepts_2009(6);/

-- tests sibling concepts
/
select getSibconcepts_2009(10);
select getSibconcepts_2009(24);
select getSibconcepts_2009(1);/

-- -- SELECT GROUP_CONCAT(ID SEPARATOR "|")
-- SELECT *
-- FROM codes_2009 as c
-- WHERE ((c.code like '002.%') AND (c.ID <> 10) AND (getSupconcepts_2009(c.ID) = 5));
--
-- SELECT GROUP_CONCAT(ID SEPARATOR "|")
-- FROM codes_2009 as c
-- WHERE ((c.ID <> 10) AND (c.code like '002.%') AND (getSupconcepts_2009(c.ID) = getSupconcepts_2009(10)));
-- WHERE ((c.ID <> 10) AND (c.code like '002.%') AND (getSupconcepts_2009(c.ID) = getSupconcepts_2009(10)));
--
--
--
-- SELECT GROUP_CONCAT(ID SEPARATOR "|")
-- FROM codes_2009 as c
-- WHERE ((c.ID <> 10) AND (c.code like concat_ws('.', '002', '%')) AND (getSupconcepts_2009(c.ID) = getSupconcepts_2009(10)));
--
--