Page History
...
Code Block | ||||
---|---|---|---|---|
| ||||
-- SQL Script to calculate the column i2b2.c_totalnum
-- 1. Backup the i2b2 table
CREATE TABLE t_i2b2 AS (SELECT * FROM i2b2);
-- 2a. Calculate count of distinct patients for all branches and leaves of concept hierarchy (regular concepts
CREATE TABLE t_totalnum AS
SELECT i2b.c_tablename,
i2b.c_fullname,
COUNT(DISTINCT obs.patient_num) AS c_totalnum
FROM observation_fact obs
JOIN concept_dimension con ON obs.concept_cd = con.concept_cd
JOIN i2b2 i2b ON con.concept_path LIKE i2b.c_fullname || '%'
GROUP BY i2b.c_tablename,i2b.c_fullname;
-- 2b. (Optionally) Calculate count of distinct patients for all branches and leaves of concept hierarchy (modifier concepts
INSERT INTO t_totalnum (c_tablename, c_fullname, c_totalnum)
SELECT i2b.c_tablename,
i2b.c_fullname,
COUNT(DISTINCT obs.patient_num) AS c_totalnum
FROM observation_fact obs
JOIN modifier_dimension con ON obs.modifier_cd = con.modifier_cd
JOIN i2b2 i2b ON con.modifier_path LIKE i2b.c_fullname || '%'
GROUP BY i2b.c_tablename,i2b.c_fullname;
-- 3. Truncate i2b2 table
TRUNCATE TABLE i2b2;
-- 4. Repopulate the i2b2 table from its backup and the generated counts
INSERT INTO i2b2 (c_hlevel, c_fullname, c_name, c_synonym_cd,
c_visualattributes, c_totalnum, c_basecode, c_metadataxml,
c_facttablecolumn, c_tablename, c_columnname, c_columndatatype,
c_operator, c_dimcode, c_comment, c_tooltip, m_applied_path,
update_date, download_date, import_date, sourcesystem_cd, valuetype_cd,
m_exclusion_cd, c_path, c_symbol)
SELECT i2b.c_hlevel,
i2b.c_fullname, i2b.c_name, i2b.c_synonym_cd, i2b.c_visualattributes,
NVL(tnm.c_totalnum, 0), i2b.c_basecode, i2b.c_metadataxml,
i2b.c_facttablecolumn, i2b.c_tablename, i2b.c_columnname,
i2b.c_columndatatype, i2b.c_operator, i2b.c_dimcode, i2b.c_comment,
i2b.c_tooltip, i2b.m_applied_path, i2b.update_date, i2b.download_date,
i2b.import_date, i2b.sourcesystem_cd, i2b.valuetype_cd,
i2b.m_exclusion_cd, i2b.c_path, i2b.c_symbol
FROM t_i2b2 i2b
LEFT JOIN t_totalnum tnm ON i2b.c_tablename = tnm.c_tablename AND i2b.c_fullname = tnm.c_fullname;
-- 5. Drop temporary tables
DROP TABLE t_totalnum;
DROP TABLE t_i2b2;
-- 6. Commit changes
COMMIT;
|
Note | ||
---|---|---|
| ||
The script temporarily truncates the i2b2 table and repopulates it from a backup. This should be kept in mind if local modifications of i2b2 installations include triggers on this table. The script creates and drops 2 temporary tables during execution. |
As an additional advantage, the i2b2 web client displays values of C_TOTALNUM in the concept hierarchy. Please note that i2b2 currently does not show counts for modifier concepts and to our knowledge also does not utilize the patient counts for modifier concepts for query optimization. Step 2b of the above SQL script (which is runtime intensive) can thus be commented out without negative effects.