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;
|