/*************************************************************************** Healthcare System Dynamics (HSD) Extensions for i2b2 http://HealthcareSystemDynamics.org Griffin Weber weber@hms.harvard.edu August 1, 2016 This script adds HSD extensions to i2b2 version 1.7 or higher. The first part of the script should be run on the i2b2 clinical research chart (CRC) cell, and the second part should be run on the i2b2 ontology (ONT) cell. This script requires Microsoft SQL Server 2008 (or newer). The following HSD concepts are included in this script: 1) Fact Count (the total number of data facts a patient has) 2) Fact Count Percentile (in the range 0%-99%) 3) Time Period - Weekday (day of the week, Sunday to Saturday) 4) Time Period - Month (month of the year, January to December) 4) Time Period - Hour (hours of the day, 12am to 11pm) This software was funded by NIH/NIGMS grant U01CA198934. ***************************************************************************/ --########################################################################## --########################################################################## --########################################################################## --### DELETE EVERYTHING --########################################################################## --########################################################################## --########################################################################## DROP TABLE #n -- CRC Cell Changes DELETE FROM CRC.VISIT_DIMENSION WHERE SOURCESYSTEM_CD = 'HSD_PATIENT_DERIVED' DELETE FROM CRC.OBSERVATION_FACT WHERE SOURCESYSTEM_CD = 'HSD_PATIENT_DERIVED' DELETE FROM CRC.CONCEPT_DIMENSION WHERE SOURCESYSTEM_CD = 'HSD' DELETE FROM CRC.QT_BREAKDOWN_PATH WHERE NAME = 'PATIENT_FACT_COUNT_PERCENTILE_XML' DROP TABLE CRC.DATETIME_DIMENSION DROP VIEW CRC.vw_DATETIME_DIMENSION UPDATE CRC.OBSERVATION_FACT SET MODIFIER_CD = '@' WHERE CONCEPT_CD LIKE 'LOINC%' AND MODIFIER_CD LIKE 'HSD:%' DROP TABLE CRC.MODIFIER_HSD_DIMENSION DELETE FROM CRC.MODIFIER_DIMENSION WHERE SOURCESYSTEM_CD = 'HSD' DELETE FROM CRC.OBSERVATION_FACT WHERE SOURCESYSTEM_CD = 'HSD' -- ONT Cell Changes DELETE FROM CRC.TABLE_ACCESS WHERE C_TABLE_CD = 'i2b2_HSD' DELETE FROM ONT.I2B2 WHERE C_FULLNAME LIKE '\i2b2\HSD\%' DELETE FROM ONT.I2B2 WHERE C_FULLNAME LIKE '\i2b2\Visit Details\Start date\%' DELETE FROM ONT.I2B2 WHERE C_FULLNAME LIKE '\HSD\%' --########################################################################## --########################################################################## --########################################################################## --### TEMP TABLE OF DATE PARTS --########################################################################## --########################################################################## --########################################################################## ;WITH i AS ( SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ), n AS ( SELECT n.n*10 p, n.n*10+i.n n FROM i CROSS JOIN i n ) SELECT ISNULL(n,0) n, CAST(n AS VARCHAR(5)) s, CAST(n+1 AS VARCHAR(5)) n1, CAST(n*10 AS VARCHAR(5)) t, CAST(n*10+9 AS VARCHAR(5)) t9, CAST(p AS VARCHAR(5)) v, CAST(p+9 AS VARCHAR(5)) v9, CAST(n AS VARCHAR(5)) + (CASE WHEN n IN (11,12) THEN 'th' WHEN n % 10 = 1 THEN 'st' WHEN n % 10 = 2 THEN 'nd' WHEN n % 10 = 3 THEN 'rd' ELSE 'th' END) nth, (CASE n WHEN 0 THEN 'Weekday' WHEN 1 THEN 'Weekend' ELSE NULL END) w, (CASE n WHEN 0 THEN '2,3,4,5,6' WHEN 1 THEN '1,7' ELSE NULL END) wi, (CASE WHEN n IN (0,6) THEN 'Weekend' WHEN n < 6 THEN 'Weekday' ELSE NULL END) e, ltrim(ms) m, ms, ltrim(ds) d, ds, hn+lower(ha) hs, ltrim(hn)+lower(ha) h, ltrim(hn)+':00'+ha+'-'+ltrim(hn)+':59'+ha hp INTO #n FROM n CROSS APPLY ( SELECT (case n when 0 then ' January' when 1 then ' February' when 2 then ' March' when 3 then ' April' when 4 then ' May' when 5 then ' June' when 6 then ' July' when 7 then ' August' when 8 then ' September' when 9 then ' October' when 10 then ' November' when 11 then 'December' else null end) ms, (case n when 0 then 'Sunday' when 1 then ' Monday' when 2 then ' Tuesday' when 3 then ' Wednesday' when 4 then ' Thursday' when 5 then 'Friday' when 6 then 'Saturday' else null end) ds, (CASE WHEN n = 0 THEN ' ' WHEN n < 10 THEN ' ' WHEN n < 13 THEN ' ' WHEN n < 22 THEN ' ' WHEN n < 24 THEN '' ELSE NULL END) +CAST((case when n = 0 then 12 when n <= 12 then n when n < 24 then n-12 else null end) AS VARCHAR(5)) hn, (case when n < 12 then 'AM' when n < 24 then 'PM' else null end) ha ) t ALTER TABLE #n ADD PRIMARY KEY (n) --########################################################################## --########################################################################## --########################################################################## --### CLINICAL RESERCH CHART (CRC) CELL UPDATES --########################################################################## --########################################################################## --########################################################################## --************************************************************************** --************************************************************************** --*** FACT COUNT --************************************************************************** --************************************************************************** ---------------------------------------------------------------------------- -- Compute fact count and fact count percentile ---------------------------------------------------------------------------- SELECT e.ENCOUNTER_NUM + ROW_NUMBER() OVER (ORDER BY PATIENT_NUM) ENCOUNTER_NUM, PATIENT_NUM, FACT_COUNT, FLOOR((ROW_NUMBER() OVER (ORDER BY FACT_COUNT, NEWID())-1)/((COUNT(*) OVER ())*1.0)*100) FACT_COUNT_PERCENTILE INTO #PatientFactCount FROM ( SELECT PATIENT_NUM, COUNT(*) FACT_COUNT FROM CRC.OBSERVATION_FACT GROUP BY PATIENT_NUM ) t CROSS JOIN (SELECT MAX(ENCOUNTER_NUM) ENCOUNTER_NUM FROM CRC.VISIT_DIMENSION) e ALTER TABLE #PatientFactCount ADD PRIMARY KEY (PATIENT_NUM) ---------------------------------------------------------------------------- -- Add fact count and fact count percentile to CRC data tables ---------------------------------------------------------------------------- -- Add to the PATIENT_DIMENSION table ALTER TABLE CRC.PATIENT_DIMENSION ADD FACT_COUNT INT; ALTER TABLE CRC.PATIENT_DIMENSION ADD FACT_COUNT_PERCENTILE INT; UPDATE p SET p.FACT_COUNT = f.FACT_COUNT, p.FACT_COUNT_PERCENTILE = f.FACT_COUNT_PERCENTILE FROM CRC.PATIENT_DIMENSION p INNER JOIN #PatientFactCount f ON p.PATIENT_NUM = f.PATIENT_NUM -- Add to the VISIT_DIMENSION table INSERT INTO CRC.VISIT_DIMENSION SELECT ENCOUNTER_NUM, PATIENT_NUM, '' ACTIVE_STATUS_CD, GETDATE() START_DATE, NULL END_DATE, '@' INOUT_CD, '' LOCATION_CD, '' LOCATION_PATH, NULL LENGTH_OF_STAY, '' VISIT_BLOB, GETDATE() UPDATE_DATE, GETDATE() DOWNLOAD_DATE, GETDATE() IMPORT_DATE, 'HSD_PATIENT_DERIVED' SOURCESYSTEM_CD, NULL UPLOAD_ID FROM #PatientFactCount -- Add to the OBSERVATION_FACT table INSERT INTO CRC.OBSERVATION_FACT SELECT ENCOUNTER_NUM, PATIENT_NUM, CONCEPT_CD, '@' PROVIDER_ID, GETDATE() START_DATE, '@' MODIFIER_CD, 1 INSTANCE_NUM, 'N' VALTYPE_CD, 'E' TVAL_CHAR, NVAL_NUM, '' VALUEFLAG_CD, NULL QUANTITY_NUM, '' UNITS_CD, NULL END_DATE, '@' LOCATION_CD, '' OBSERVATION_BLOB, NULL CONFIDENCE_NUM, GETDATE() UPDATE_DATE, GETDATE() DOWNLOAD_DATE, GETDATE() IMPORT_DATE, 'HSD_PATIENT_DERIVED' SOURCESYSTEM_CD, NULL UPLOAD_ID FROM #PatientFactCount CROSS APPLY ( SELECT 'HSD|PATIENT:FactCount' CONCEPT_CD, FACT_COUNT NVAL_NUM UNION ALL SELECT 'HSD|PATIENT:FactCountPercentile' CONCEPT_CD, FACT_COUNT_PERCENTILE NVAL_NUM ) t ---------------------------------------------------------------------------- -- Add rows to additional CRC tables for fact counts ---------------------------------------------------------------------------- -- Add fact count and fact count percentile to the CONCEPT_DIMENSION table INSERT INTO CRC.CONCEPT_DIMENSION SELECT '\i2b2\HSD\Fact Count\Fact Count Percentile\Fact Count Percentile\' CONCEPT_PATH, 'HSD|PATIENT:FactCountPercentile' CONCEPT_CD, 'Fact Count Percentile' NAME_CHAR, '' CONCEPT_BLOG, '9/7/2015' UPDATE_DATE, '9/7/2015' DOWNLOAD_DATE, '9/7/2015' IMPORT_DATE, 'HSD' SOURCESYSTEM_CD, NULL UPLOAD_ID UNION ALL SELECT '\i2b2\HSD\Fact Count\Fact Count Percentile\Fact Count\' CONCEPT_PATH, 'HSD|PATIENT:FactCount' CONCEPT_CD, 'Fact Count' NAME_CHAR, '' CONCEPT_BLOG, '9/7/2015' UPDATE_DATE, '9/7/2015' DOWNLOAD_DATE, '9/7/2015' IMPORT_DATE, 'HSD' SOURCESYSTEM_CD, NULL UPLOAD_ID -- Add fact count percentile to the QT_BREAKDOWN_PATH table INSERT INTO CRC.QT_BREAKDOWN_PATH SELECT 'PATIENT_FACT_COUNT_PERCENTILE_XML' NAME, '\\i2b2_HSD\i2b2\HSD\Fact Count\Fact Count Percentile\' VALUE, '8/24/2015' CREATE_DATE, NULL UPDATE_DATE, NULL USER_ID --************************************************************************** --************************************************************************** --*** DATE PARTS --************************************************************************** --************************************************************************** ---------------------------------------------------------------------------- -- datetime_dimension table ---------------------------------------------------------------------------- CREATE TABLE CRC.DATETIME_DIMENSION ( START_DATE DATETIME PRIMARY KEY, DATE_PART_MONTH TINYINT, DATE_PART_WEEKDAY TINYINT, DATE_PART_HOUR TINYINT ) INSERT INTO CRC.DATETIME_DIMENSION WITH (TABLOCK) SELECT START_DATE, DATEPART(month,START_DATE), DATEPART(weekday,START_DATE), DATEPART(hour,START_DATE) FROM (SELECT DISTINCT START_DATE FROM CRC.OBSERVATION_FACT) t CREATE NONCLUSTERED INDEX IDX_MONTH ON CRC.DATETIME_DIMENSION (DATE_PART_MONTH) CREATE NONCLUSTERED INDEX IDX_WEEKDAY ON CRC.DATETIME_DIMENSION (DATE_PART_WEEKDAY) CREATE NONCLUSTERED INDEX IDX_HOUR ON CRC.DATETIME_DIMENSION (DATE_PART_HOUR) ---------------------------------------------------------------------------- -- vw_datetime_dimension view ---------------------------------------------------------------------------- GO CREATE VIEW CRC.vw_DATETIME_DIMENSION AS SELECT START_DATE, DATEPART(month,START_DATE) DATE_PART_MONTH, DATEPART(weekday,START_DATE) DATE_PART_WEEKDAY, DATEPART(hour,START_DATE) DATE_PART_HOUR FROM (SELECT DISTINCT START_DATE FROM CRC.OBSERVATION_FACT) t GO ---------------------------------------------------------------------------- -- Create a unique modifier code for each fact ---------------------------------------------------------------------------- ;WITH t AS ( SELECT ENCOUNTER_NUM, PATIENT_NUM, CONCEPT_CD, PROVIDER_ID, START_DATE, MODIFIER_CD, INSTANCE_NUM, 'HSD:'+CAST(ROW_NUMBER() OVER (ORDER BY ENCOUNTER_NUM, CONCEPT_CD, PROVIDER_ID, START_DATE, MODIFIER_CD, INSTANCE_NUM, PATIENT_NUM) AS VARCHAR(50)) MODIFIER_CD_NEW FROM CRC.OBSERVATION_FACT WHERE CONCEPT_CD LIKE 'LOINC%' ) UPDATE f SET f.MODIFIER_CD = t.MODIFIER_CD_NEW FROM CRC.OBSERVATION_FACT f INNER JOIN t ON f.ENCOUNTER_NUM=t.ENCOUNTER_NUM AND f.PATIENT_NUM=t.PATIENT_NUM AND f.CONCEPT_CD=t.CONCEPT_CD AND f.PROVIDER_ID=t.PROVIDER_ID AND f.START_DATE=t.START_DATE AND f.MODIFIER_CD=t.MODIFIER_CD AND f.INSTANCE_NUM=t.INSTANCE_NUM CREATE TABLE CRC.MODIFIER_HSD_DIMENSION ( MODIFIER_CD VARCHAR(50) PRIMARY KEY, START_DATE DATETIME, DATE_PART_MONTH TINYINT, DATE_PART_WEEKDAY TINYINT, DATE_PART_HOUR TINYINT ) INSERT INTO CRC.MODIFIER_HSD_DIMENSION WITH (TABLOCK) SELECT MODIFIER_CD, START_DATE, DATEPART(month,START_DATE) DATE_PART_MONTH, DATEPART(weekday,START_DATE) DATE_PART_WEEKDAY, DATEPART(hour,START_DATE) DATE_PART_HOUR FROM CRC.OBSERVATION_FACT WHERE CONCEPT_CD LIKE 'LOINC%' CREATE NONCLUSTERED INDEX IDX_START_DATE ON CRC.MODIFIER_HSD_DIMENSION (START_DATE) CREATE NONCLUSTERED INDEX IDX_MONTH ON CRC.MODIFIER_HSD_DIMENSION (DATE_PART_MONTH) CREATE NONCLUSTERED INDEX IDX_WEEKDAY ON CRC.MODIFIER_HSD_DIMENSION (DATE_PART_WEEKDAY) CREATE NONCLUSTERED INDEX IDX_HOUR ON CRC.MODIFIER_HSD_DIMENSION (DATE_PART_HOUR) ---------------------------------------------------------------------------- -- Add items to the regular modifier_dimension table ---------------------------------------------------------------------------- INSERT INTO CRC.MODIFIER_DIMENSION SELECT MODIFIER_PATH, MODIFIER_CD, NAME_CHAR, '' MODIFIER_BLOB, '8/1/2016' UPDATE_DATE, NULL DOWNLOAD_DATE, NULL IMPORT_DATE, 'HSD' SOURCESYSTEM_CD, NULL UPLOAD_ID FROM ( SELECT null MODIFIER_PATH, null MODIFIER_CD, null NAME_CHAR WHERE 1=0 UNION ALL SELECT '\HSD\Fact Date\Modifier Code\Month of the year\'+m+'\', 'HSD|MONTH:'+n1, ltrim(ms) FROM #n WHERE n<12 UNION ALL SELECT '\HSD\Fact Date\Modifier Value\Month of the year\', 'HSD|DATEPART:MONTH', 'Month of the year' -- *** Day of the week *** UNION ALL SELECT '\HSD\Fact Date\Modifier Code\Day of the week\'+e+'\'+d+'\', 'HSD|WEEKDAY:'+n1, ltrim(ds) FROM #n WHERE n<7 UNION ALL SELECT '\HSD\Fact Date\Modifier Value\Day of the week\', 'HSD|DATEPART:WEEKDAY', 'Day of the week' -- *** Hour of the day *** UNION ALL SELECT '\HSD\Fact Date\Modifier Code\Hour of the day\'+h+'\', 'HSD|HOUR:'+s, ltrim(hs) FROM #n WHERE n<24 UNION ALL SELECT '\HSD\Fact Date\Modifier Value\Hour of the day\', 'HSD|DATEPART:HOUR', 'Hour of the day' ) t ---------------------------------------------------------------------------- -- Add modifier rows to the observation_fact table ---------------------------------------------------------------------------- INSERT INTO CRC.OBSERVATION_FACT SELECT ENCOUNTER_NUM, PATIENT_NUM, CONCEPT_CD, PROVIDER_ID, START_DATE, m.MODIFIER_CD, INSTANCE_NUM, m.VALTYPE_CD, m.TVAL_CHAR, null NVAL_NUM, VALUEFLAG_CD, QUANTITY_NUM, UNITS_CD, END_DATE, LOCATION_CD, OBSERVATION_BLOB, CONFIDENCE_NUM, UPDATE_DATE, DOWNLOAD_DATE, IMPORT_DATE, 'HSD' SOURCESYSTEM_CD, UPLOAD_ID FROM CRC.OBSERVATION_FACT f CROSS APPLY ( SELECT 'HSD|MONTH:'+CAST(DATEPART(month,START_DATE) AS VARCHAR(50)) MODIFIER_CD, '' VALTYPE_CD, '' TVAL_CHAR UNION ALL SELECT 'HSD|WEEKDAY:'+CAST(DATEPART(weekday,START_DATE) AS VARCHAR(50)) MODIFIER_CD, '' VALTYPE_CD, '' TVAL_CHAR UNION ALL SELECT 'HSD|HOUR:'+CAST(DATEPART(hour,START_DATE) AS VARCHAR(50)) MODIFIER_CD, '' VALTYPE_CD, '' TVAL_CHAR UNION ALL SELECT 'HSD|DATEPART:MONTH' MODIFIER_CD, 'T' VALTYPE_CD, CAST(DATEPART(month,START_DATE) AS VARCHAR(50)) VALTYPE_CD UNION ALL SELECT 'HSD|DATEPART:WEEKDAY' MODIFIER_CD, 'T' VALTYPE_CD, CAST(DATEPART(weekday,START_DATE) AS VARCHAR(50)) VALTYPE_CD UNION ALL SELECT 'HSD|DATEPART:HOUR' MODIFIER_CD, 'T' VALTYPE_CD, CAST(DATEPART(hour,START_DATE) AS VARCHAR(50)) VALTYPE_CD ) m WHERE CONCEPT_CD LIKE 'NDC:%' AND f.MODIFIER_CD = '@' --########################################################################## --########################################################################## --########################################################################## --### ONTOLOGY (ONT) CELL UPDATES --########################################################################## --########################################################################## --########################################################################## --************************************************************************** --************************************************************************** --*** HSD Root --************************************************************************** --************************************************************************** ---------------------------------------------------------------------------- -- Table Access ---------------------------------------------------------------------------- INSERT INTO CRC.TABLE_ACCESS SELECT 'i2b2_HSD', 'I2B2', 'N', 1, '\i2b2\HSD\', 'Healthcare System Dynamics', 'N', 'CA ', null, null, null, 'concept_cd', 'concept_dimension', 'concept_path', 'T', 'LIKE', '\i2b2\HSD\', null, 'Healthcare System Dynamics', null, null, null, null ---------------------------------------------------------------------------- -- Ontology Table ---------------------------------------------------------------------------- INSERT INTO ONT.I2B2 SELECT C_HLEVEL, C_FULLNAME, C_NAME, C_SYNONYM_CD, C_VISUALATTRIBUTES, null C_TOTALNUM, C_BASECODE, C_METADATAXML, C_FACTTABLECOLUMN, C_TABLENAME, C_COLUMNNAME, C_COLUMNDATATYPE, C_OPERATOR, C_DIMCODE, null C_COMMENT, C_TOOLTIP, '@' M_APPLIED_PATH, '8/1/2016' UPDATE_DATE, '8/1/2016' DOWNLOAD_DATE, '8/1/2016' IMPORT_DATE, 'HSD' SOURCESYSTEM_CD, null VALUETYPE_CD, null M_EXCLUSION_CD, null C_PATH, null C_SYMBOL FROM ( SELECT null C_HLEVEL, null C_FULLNAME, null C_NAME, null C_SYNONYM_CD, null C_VISUALATTRIBUTES, null C_BASECODE, null C_METADATAXML, null C_FACTTABLECOLUMN, null C_TABLENAME, null C_COLUMNNAME, null C_COLUMNDATATYPE, null C_OPERATOR, null C_DIMCODE, null C_TOOLTIP WHERE 1=0 -------------------------------------------------------------------- UNION ALL SELECT '1', '\i2b2\HSD\', 'Healthcare System Dynamics', 'N', 'CA ', NULL, NULL, 'concept_cd', 'concept_dimension', 'concept_path', 'T', 'LIKE', '\i2b2\HSD\', 'Healthcare System Dynamics' ) t --************************************************************************** --************************************************************************** --*** Fact Count --************************************************************************** --************************************************************************** INSERT INTO ONT.I2B2 SELECT C_HLEVEL, C_FULLNAME, C_NAME, C_SYNONYM_CD, C_VISUALATTRIBUTES, null C_TOTALNUM, C_BASECODE, C_METADATAXML, C_FACTTABLECOLUMN, C_TABLENAME, C_COLUMNNAME, C_COLUMNDATATYPE, C_OPERATOR, C_DIMCODE, null C_COMMENT, C_TOOLTIP, '@' M_APPLIED_PATH, '8/1/2016' UPDATE_DATE, '8/1/2016' DOWNLOAD_DATE, '8/1/2016' IMPORT_DATE, 'HSD' SOURCESYSTEM_CD, null VALUETYPE_CD, null M_EXCLUSION_CD, null C_PATH, null C_SYMBOL FROM ( SELECT null C_HLEVEL, null C_FULLNAME, null C_NAME, null C_SYNONYM_CD, null C_VISUALATTRIBUTES, null C_BASECODE, null C_METADATAXML, null C_FACTTABLECOLUMN, null C_TABLENAME, null C_COLUMNNAME, null C_COLUMNDATATYPE, null C_OPERATOR, null C_DIMCODE, null C_TOOLTIP WHERE 1=0 -------------------------------------------------------------------- UNION ALL SELECT '2', '\i2b2\HSD\Fact Count\', 'Fact Count', 'N', 'CA ', NULL, NULL, 'concept_cd', 'concept_dimension', 'concept_path', 'T', 'LIKE', '\i2b2\HSD\Fact Count\', 'Healthcare System Dynamics \ Fact Count' -- *** BY VALUE *** UNION ALL SELECT '3', '\i2b2\HSD\Fact Count\Fact Count Percentile\Fact Count Percentile\', 'Fact Count Percentile', 'N', 'LA ', 'HSD|PATIENT:FactCountPercentile', '3.029/7/2015FactCountPercentileFact Count PercentilePosFloatGRPPercentile', 'concept_cd', 'concept_dimension', 'concept_path', 'T', 'LIKE', '\i2b2\HSD\Fact Count\Fact Count Percentile\Fact Count Percentile\', 'Healthcare System Dynamics \ Fact Count \ Fact Count Percentile' UNION ALL SELECT '3', '\i2b2\HSD\Fact Count\Fact Count Percentile\Fact Count\', 'Fact Count', 'N', 'LA ', 'HSD|PATIENT:FactCount', '3.029/7/2015FactCountFact CountPosFloatGRPCount', 'concept_cd', 'concept_dimension', 'concept_path', 'T', 'LIKE', '\i2b2\HSD\Fact Count\Fact Count Percentile\Fact Count\', 'Healthcare System Dynamics \ Fact Count \ Fact Count' -- *** BY CONCEPT *** UNION ALL SELECT '3', '\i2b2\HSD\Fact Count\Fact Count Percentile\', 'zz Fact Count Percentile', 'N', 'CA ', NULL, NULL, 'patient_num', 'patient_dimension', 'fact_count_percentile', 'N', 'BETWEEN', '0 AND 99', 'Healthcare System Dynamics \ Fact Count \ Fact Count Percentile' UNION ALL SELECT '4', '\i2b2\HSD\Fact Count\Fact Count Percentile\'+t+'-'+t9+'\', t+'th to '+t9+'th Fact Count Percentile', 'N', 'FA ', NULL, NULL, 'patient_num', 'patient_dimension', 'fact_count_percentile', 'N', 'BETWEEN', t+' AND '+t9, 'Healthcare System Dynamics \ Fact Count \ Fact Count Percentile \ '+t+'th to '+t9+'th Fact Count Percentile' FROM #n WHERE n<10 UNION ALL SELECT '5', '\i2b2\HSD\Fact Count\Fact Count Percentile\'+v+'-'+v9+'\'+s+'\', nth+' Fact Count Percentile', 'N', 'LA ', NULL, NULL, 'patient_num', 'patient_dimension', 'fact_count_percentile', 'N', '=', s, 'Healthcare System Dynamics \ Fact Count \ Fact Count Percentile \ '+t+'th to '+t9+'th Fact Count Percentile \ '+nth+' Fact Count Percentile' FROM #n WHERE n<100 ) t --************************************************************************** --************************************************************************** --*** Date Parts --************************************************************************** --************************************************************************** ---------------------------------------------------------------------------- -- Visit Dimension ---------------------------------------------------------------------------- INSERT INTO ONT.I2B2 SELECT C_HLEVEL, C_FULLNAME, C_NAME, C_SYNONYM_CD, C_VISUALATTRIBUTES, null C_TOTALNUM, C_BASECODE, C_METADATAXML, C_FACTTABLECOLUMN, C_TABLENAME, C_COLUMNNAME, C_COLUMNDATATYPE, C_OPERATOR, C_DIMCODE, null C_COMMENT, C_TOOLTIP, '@' M_APPLIED_PATH, '8/1/2016' UPDATE_DATE, '8/1/2016' DOWNLOAD_DATE, '8/1/2016' IMPORT_DATE, 'HSD' SOURCESYSTEM_CD, null VALUETYPE_CD, null M_EXCLUSION_CD, null C_PATH, null C_SYMBOL FROM ( SELECT null C_HLEVEL, null C_FULLNAME, null C_NAME, null C_SYNONYM_CD, null C_VISUALATTRIBUTES, null C_BASECODE, null C_METADATAXML, null C_FACTTABLECOLUMN, null C_TABLENAME, null C_COLUMNNAME, null C_COLUMNDATATYPE, null C_OPERATOR, null C_DIMCODE, null C_TOOLTIP WHERE 1=0 -------------------------------------------------------------------- UNION ALL SELECT '2', '\i2b2\Visit Details\Start date\', 'Start date', 'N', 'FA ', NULL, NULL, 'encounter_num', 'visit_dimension', 'start_date', 'N', 'IS NOT', 'NULL', 'Visit Details \ Start date' -- *** Month of the year *** UNION ALL SELECT '3', '\i2b2\Visit Details\Start date\Month of the year\', 'zz Month of the year', 'N', 'FA ', NULL, NULL, 'encounter_num', 'visit_dimension', 'start_date', 'N', 'IS NOT', 'NULL', 'Visit Details \ Start date \ Month of the year' UNION ALL SELECT '4', '\i2b2\Visit Details\Start date\Month of the year\'+m+'\', ms, 'N', 'LA ', NULL, NULL, 'encounter_num', 'visit_dimension', 'start_date', 'N', '=', '(case when datepart(month,start_date)='+n1+' then start_date else null end)', 'Visit Details \ Start date \ Month of the year \ '+m FROM #n WHERE n<12 -- *** Day of the week *** UNION ALL SELECT '3', '\i2b2\Visit Details\Start date\Day of the week\', 'zz Day of the week', 'N', 'FA ', NULL, NULL, 'encounter_num', 'visit_dimension', 'start_date', 'N', 'IS NOT', 'NULL', 'Visit Details \ Start date \ Day of the week' UNION ALL SELECT '4', '\i2b2\Visit Details\Start date\Day of the week\'+w+'\', w, 'N', 'FA ', NULL, NULL, 'encounter_num', 'visit_dimension', 'start_date', 'N', '=', '(case when datepart(weekday,start_date) in ('+wi+') then start_date else null end)', 'Visit Details \ Start date \ Day of the week \ '+w FROM #n WHERE n<2 UNION ALL SELECT '5', '\i2b2\Visit Details\Start date\Day of the week\'+e+'\'+d+'\', ds, 'N', 'LA ', NULL, NULL, 'encounter_num', 'visit_dimension', 'start_date', 'N', '=', '(case when datepart(weekday,start_date)='+n1+' then start_date else null end)', 'Visit Details \ Start date \ Day of the week \ '+e+' \ '+d FROM #n WHERE n<7 -- *** Hour of the day *** UNION ALL SELECT '3', '\i2b2\Visit Details\Start date\Hour of the day\', 'Hour of the day', 'N', 'FA ', NULL, NULL, 'encounter_num', 'visit_dimension', 'start_date', 'N', 'IS NOT', 'NULL', 'Visit Details \ Start date \ Hour of the day' UNION ALL SELECT '4', '\i2b2\Visit Details\Start date\Hour of the day\'+h+'\', hs, 'N', 'LA ', NULL, NULL, 'encounter_num', 'visit_dimension', 'start_date', 'N', '=', '(case when datepart(hour,start_date)='+s+' then start_date else null end)', 'Visit Details \ Start date \ Hour of the day \ '+h+' ('+hp+')' FROM #n WHERE n<24 ) t ---------------------------------------------------------------------------- -- Observation (Root) ---------------------------------------------------------------------------- INSERT INTO ONT.I2B2 SELECT C_HLEVEL, C_FULLNAME, C_NAME, C_SYNONYM_CD, C_VISUALATTRIBUTES, null C_TOTALNUM, C_BASECODE, C_METADATAXML, C_FACTTABLECOLUMN, C_TABLENAME, C_COLUMNNAME, C_COLUMNDATATYPE, C_OPERATOR, C_DIMCODE, null C_COMMENT, C_TOOLTIP, '@' M_APPLIED_PATH, '8/1/2016' UPDATE_DATE, '8/1/2016' DOWNLOAD_DATE, '8/1/2016' IMPORT_DATE, 'HSD' SOURCESYSTEM_CD, null VALUETYPE_CD, null M_EXCLUSION_CD, null C_PATH, null C_SYMBOL FROM ( SELECT null C_HLEVEL, null C_FULLNAME, null C_NAME, null C_SYNONYM_CD, null C_VISUALATTRIBUTES, null C_BASECODE, null C_METADATAXML, null C_FACTTABLECOLUMN, null C_TABLENAME, null C_COLUMNNAME, null C_COLUMNDATATYPE, null C_OPERATOR, null C_DIMCODE, null C_TOOLTIP WHERE 1=0 -- ########## Fact Date ########## UNION ALL SELECT '2', '\i2b2\HSD\Fact Date\', 'Fact Date', 'N', 'FA ', NULL, NULL, 'start_date', 'vw_DATETIME_DIMENSION', 'start_date', 'N', 'IS NOT', 'NULL', 'Healthare System Dynamics \ Fact Date' ) t ---------------------------------------------------------------------------- -- Observation (Date Dimension View) ---------------------------------------------------------------------------- INSERT INTO ONT.I2B2 SELECT C_HLEVEL, C_FULLNAME, C_NAME, C_SYNONYM_CD, C_VISUALATTRIBUTES, null C_TOTALNUM, C_BASECODE, C_METADATAXML, C_FACTTABLECOLUMN, C_TABLENAME, C_COLUMNNAME, C_COLUMNDATATYPE, C_OPERATOR, C_DIMCODE, null C_COMMENT, C_TOOLTIP, '@' M_APPLIED_PATH, '8/1/2016' UPDATE_DATE, '8/1/2016' DOWNLOAD_DATE, '8/1/2016' IMPORT_DATE, 'HSD' SOURCESYSTEM_CD, null VALUETYPE_CD, null M_EXCLUSION_CD, null C_PATH, null C_SYMBOL FROM ( SELECT null C_HLEVEL, null C_FULLNAME, null C_NAME, null C_SYNONYM_CD, null C_VISUALATTRIBUTES, null C_BASECODE, null C_METADATAXML, null C_FACTTABLECOLUMN, null C_TABLENAME, null C_COLUMNNAME, null C_COLUMNDATATYPE, null C_OPERATOR, null C_DIMCODE, null C_TOOLTIP WHERE 1=0 -------------------------------------------------------------------- UNION ALL SELECT '3', '\i2b2\HSD\Fact Date\Date Dimension View\', 'Date Dimension View', 'N', 'FA ', NULL, NULL, 'start_date', 'vw_DATETIME_DIMENSION', 'start_date', 'N', 'IS NOT', 'NULL', 'Healthare System Dynamics \ Fact Date \ Date Dimension View' -- *** Month of the year *** UNION ALL SELECT '4', '\i2b2\HSD\Fact Date\Date Dimension View\Month of the year\', 'zz Month of the year', 'N', 'FA ', NULL, NULL, 'start_date', 'vw_DATETIME_DIMENSION', 'start_date', 'N', 'IS NOT', 'NULL', 'Healthare System Dynamics \ Fact Date \ Date Dimension View \ Month of the year' UNION ALL SELECT '5', '\i2b2\HSD\Fact Date\Date Dimension View\Month of the year\'+m+'\', ms, 'N', 'LA ', NULL, NULL, 'start_date', 'vw_DATETIME_DIMENSION', 'date_part_month', 'N', '=', n1, 'Healthare System Dynamics \ Fact Date \ Date Dimension View \ Month of the year \ '+m FROM #n WHERE n<12 -- *** Day of the week *** UNION ALL SELECT '4', '\i2b2\HSD\Fact Date\Date Dimension View\Day of the week\', 'zz Day of the week', 'N', 'FA ', NULL, NULL, 'start_date', 'vw_DATETIME_DIMENSION', 'start_date', 'N', 'IS NOT', 'NULL', 'Healthare System Dynamics \ Fact Date \ Date Dimension View \ Day of the week' UNION ALL SELECT '5', '\i2b2\HSD\Fact Date\Date Dimension View\Day of the week\'+w+'\', w, 'N', 'FA ', NULL, NULL, 'start_date', 'vw_DATETIME_DIMENSION', 'date_part_weekday', 'N', 'IN', wi, 'Healthare System Dynamics \ Fact Date \ Date Dimension View \ Day of the week \ '+w FROM #n WHERE n<2 UNION ALL SELECT '6', '\i2b2\HSD\Fact Date\Date Dimension View\Day of the week\'+e+'\'+d+'\', ds, 'N', 'LA ', NULL, NULL, 'start_date', 'vw_DATETIME_DIMENSION', 'date_part_weekday', 'N', '=', n1, 'Healthare System Dynamics \ Fact Date \ Date Dimension View \ Day of the week \ '+e+' \ '+d FROM #n WHERE n<7 -- *** Hour of the day *** UNION ALL SELECT '4', '\i2b2\HSD\Fact Date\Date Dimension View\Hour of the day\', 'Hour of the day', 'N', 'FA ', NULL, NULL, 'start_date', 'vw_DATETIME_DIMENSION', 'start_date', 'N', 'IS NOT', 'NULL', 'Healthare System Dynamics \ Fact Date \ Date Dimension View \ Hour of the day' UNION ALL SELECT '5', '\i2b2\HSD\Fact Date\Date Dimension View\Hour of the day\'+h+'\', hs, 'N', 'LA ', NULL, NULL, 'start_date', 'vw_DATETIME_DIMENSION', 'date_part_hour', 'N', '=', s, 'Healthare System Dynamics \ Fact Date \ Date Dimension View \ Hour of the day \ '+h+' ('+hp+')' FROM #n WHERE n<24 ) t ---------------------------------------------------------------------------- -- Observation (Date Dimension Table) ---------------------------------------------------------------------------- INSERT INTO ONT.I2B2 SELECT C_HLEVEL, C_FULLNAME, C_NAME, C_SYNONYM_CD, C_VISUALATTRIBUTES, null C_TOTALNUM, C_BASECODE, C_METADATAXML, C_FACTTABLECOLUMN, C_TABLENAME, C_COLUMNNAME, C_COLUMNDATATYPE, C_OPERATOR, C_DIMCODE, null C_COMMENT, C_TOOLTIP, '@' M_APPLIED_PATH, '8/1/2016' UPDATE_DATE, '8/1/2016' DOWNLOAD_DATE, '8/1/2016' IMPORT_DATE, 'HSD' SOURCESYSTEM_CD, null VALUETYPE_CD, null M_EXCLUSION_CD, null C_PATH, null C_SYMBOL FROM ( SELECT null C_HLEVEL, null C_FULLNAME, null C_NAME, null C_SYNONYM_CD, null C_VISUALATTRIBUTES, null C_BASECODE, null C_METADATAXML, null C_FACTTABLECOLUMN, null C_TABLENAME, null C_COLUMNNAME, null C_COLUMNDATATYPE, null C_OPERATOR, null C_DIMCODE, null C_TOOLTIP WHERE 1=0 -------------------------------------------------------------------- UNION ALL SELECT '3', '\i2b2\HSD\Fact Date\Date Dimension Table\', 'Date Dimension Table', 'N', 'FA ', NULL, NULL, 'start_date', 'DATETIME_DIMENSION', 'start_date', 'N', 'IS NOT', 'NULL', 'Healthare System Dynamics \ Fact Date \ Date Dimension Table' -- *** Month of the year *** UNION ALL SELECT '4', '\i2b2\HSD\Fact Date\Date Dimension Table\Month of the year\', 'zz Month of the year', 'N', 'FA ', NULL, NULL, 'start_date', 'DATETIME_DIMENSION', 'start_date', 'N', 'IS NOT', 'NULL', 'Healthare System Dynamics \ Fact Date \ Date Dimension Table \ Month of the year' UNION ALL SELECT '5', '\i2b2\HSD\Fact Date\Date Dimension Table\Month of the year\'+m+'\', ms, 'N', 'LA ', NULL, NULL, 'start_date', 'DATETIME_DIMENSION', 'date_part_month', 'N', '=', n1, 'Healthare System Dynamics \ Fact Date \ Date Dimension Table \ Month of the year \ '+m FROM #n WHERE n<12 -- *** Day of the week *** UNION ALL SELECT '4', '\i2b2\HSD\Fact Date\Date Dimension Table\Day of the week\', 'zz Day of the week', 'N', 'FA ', NULL, NULL, 'start_date', 'DATETIME_DIMENSION', 'start_date', 'N', 'IS NOT', 'NULL', 'Healthare System Dynamics \ Fact Date \ Date Dimension Table \ Day of the week' UNION ALL SELECT '5', '\i2b2\HSD\Fact Date\Date Dimension Table\Day of the week\'+w+'\', w, 'N', 'FA ', NULL, NULL, 'start_date', 'DATETIME_DIMENSION', 'date_part_weekday', 'N', 'IN', wi, 'Healthare System Dynamics \ Fact Date \ Date Dimension Table \ Day of the week \ '+w FROM #n WHERE n<2 UNION ALL SELECT '6', '\i2b2\HSD\Fact Date\Date Dimension Table\Day of the week\'+e+'\'+d+'\', ds, 'N', 'LA ', NULL, NULL, 'start_date', 'DATETIME_DIMENSION', 'date_part_weekday', 'N', '=', n1, 'Healthare System Dynamics \ Fact Date \ Date Dimension Table \ Day of the week \ '+e+' \ '+d FROM #n WHERE n<7 -- *** Hour of the day *** UNION ALL SELECT '4', '\i2b2\HSD\Fact Date\Date Dimension Table\Hour of the day\', 'Hour of the day', 'N', 'FA ', NULL, NULL, 'start_date', 'DATETIME_DIMENSION', 'start_date', 'N', 'IS NOT', 'NULL', 'Healthare System Dynamics \ Fact Date \ Date Dimension Table \ Hour of the day' UNION ALL SELECT '5', '\i2b2\HSD\Fact Date\Date Dimension Table\Hour of the day\'+h+'\', hs, 'N', 'LA ', NULL, NULL, 'start_date', 'DATETIME_DIMENSION', 'date_part_hour', 'N', '=', s, 'Healthare System Dynamics \ Fact Date \ Date Dimension Table \ Hour of the day \ '+h+' ('+hp+')' FROM #n WHERE n<24 ) t ---------------------------------------------------------------------------- -- Observation (Start Date Formula) ---------------------------------------------------------------------------- INSERT INTO ONT.I2B2 SELECT C_HLEVEL, C_FULLNAME, C_NAME, C_SYNONYM_CD, C_VISUALATTRIBUTES, null C_TOTALNUM, C_BASECODE, C_METADATAXML, C_FACTTABLECOLUMN, C_TABLENAME, C_COLUMNNAME, C_COLUMNDATATYPE, C_OPERATOR, C_DIMCODE, null C_COMMENT, C_TOOLTIP, '@' M_APPLIED_PATH, '8/1/2016' UPDATE_DATE, '8/1/2016' DOWNLOAD_DATE, '8/1/2016' IMPORT_DATE, 'HSD' SOURCESYSTEM_CD, null VALUETYPE_CD, null M_EXCLUSION_CD, null C_PATH, null C_SYMBOL FROM ( SELECT null C_HLEVEL, null C_FULLNAME, null C_NAME, null C_SYNONYM_CD, null C_VISUALATTRIBUTES, null C_BASECODE, null C_METADATAXML, null C_FACTTABLECOLUMN, null C_TABLENAME, null C_COLUMNNAME, null C_COLUMNDATATYPE, null C_OPERATOR, null C_DIMCODE, null C_TOOLTIP WHERE 1=0 -------------------------------------------------------------------- UNION ALL SELECT '3', '\i2b2\HSD\Fact Date\Fact Table Formula\', 'Fact Table Formula', 'N', 'FA ', NULL, NULL, 'start_date', 'observation_fact', 'start_date', 'N', 'IS NOT', 'NULL', 'Healthare System Dynamics \ Fact Date \ Fact Table Formula' -- *** Month of the year *** UNION ALL SELECT '4', '\i2b2\HSD\Fact Date\Fact Table Formula\Month of the year\', 'zz Month of the year', 'N', 'FA ', NULL, NULL, 'start_date', 'observation_fact', 'start_date', 'N', 'IS NOT', 'NULL', 'Healthare System Dynamics \ Fact Date \ Fact Table Formula \ Month of the year' UNION ALL SELECT '5', '\i2b2\HSD\Fact Date\Fact Table Formula\Month of the year\'+m+'\', ms, 'N', 'LA ', NULL, NULL, 'start_date', 'observation_fact', 'start_date', 'N', '=', '(case when datepart(month,start_date)='+n1+' then start_date else null end)', 'Healthare System Dynamics \ Fact Date \ Fact Table Formula \ Month of the year \ '+m FROM #n WHERE n<12 -- *** Day of the week *** UNION ALL SELECT '4', '\i2b2\HSD\Fact Date\Fact Table Formula\Day of the week\', 'zz Day of the week', 'N', 'FA ', NULL, NULL, 'start_date', 'observation_fact', 'start_date', 'N', 'IS NOT', 'NULL', 'Healthare System Dynamics \ Fact Date \ Fact Table Formula \ Day of the week' UNION ALL SELECT '5', '\i2b2\HSD\Fact Date\Fact Table Formula\Day of the week\'+w+'\', w, 'N', 'FA ', NULL, NULL, 'start_date', 'observation_fact', 'start_date', 'N', '=', '(case when datepart(weekday,start_date) in ('+wi+') then start_date else null end)', 'Healthare System Dynamics \ Fact Date \ Fact Table Formula \ Day of the week \ '+w FROM #n WHERE n<2 UNION ALL SELECT '6', '\i2b2\HSD\Fact Date\Fact Table Formula\Day of the week\'+e+'\'+d+'\', ds, 'N', 'LA ', NULL, NULL, 'start_date', 'observation_fact', 'start_date', 'N', '=', '(case when datepart(weekday,start_date)='+n1+' then start_date else null end)', 'Healthare System Dynamics \ Fact Date \ Fact Table Formula \ Day of the week \ '+e+' \ '+d FROM #n WHERE n<7 -- *** Hour of the day *** UNION ALL SELECT '4', '\i2b2\HSD\Fact Date\Fact Table Formula\Hour of the day\', 'Hour of the day', 'N', 'FA ', NULL, NULL, 'start_date', 'observation_fact', 'start_date', 'N', 'IS NOT', 'NULL', 'Healthare System Dynamics \ Fact Date \ Fact Table Formula \ Hour of the day' UNION ALL SELECT '5', '\i2b2\HSD\Fact Date\Fact Table Formula\Hour of the day\'+h+'\', hs, 'N', 'LA ', NULL, NULL, 'start_date', 'observation_fact', 'start_date', 'N', '=', '(case when datepart(hour,start_date)='+s+' then start_date else null end)', 'Healthare System Dynamics \ Fact Date \ Fact Table Formula \ Hour of the day \ '+h+' ('+hp+')' FROM #n WHERE n<24 ) t ---------------------------------------------------------------------------- -- Modifier (Diagnoses, start_date Field, datetime_dimension Table) ---------------------------------------------------------------------------- INSERT INTO ONT.I2B2 SELECT C_HLEVEL, C_FULLNAME, C_NAME, C_SYNONYM_CD, C_VISUALATTRIBUTES, null C_TOTALNUM, C_BASECODE, C_METADATAXML, C_FACTTABLECOLUMN, C_TABLENAME, C_COLUMNNAME, C_COLUMNDATATYPE, C_OPERATOR, C_DIMCODE, null C_COMMENT, C_TOOLTIP, '\i2b2\Diagnoses\%' M_APPLIED_PATH, '8/1/2016' UPDATE_DATE, '8/1/2016' DOWNLOAD_DATE, '8/1/2016' IMPORT_DATE, 'HSD' SOURCESYSTEM_CD, null VALUETYPE_CD, null M_EXCLUSION_CD, null C_PATH, null C_SYMBOL FROM ( SELECT null C_HLEVEL, null C_FULLNAME, null C_NAME, null C_SYNONYM_CD, null C_VISUALATTRIBUTES, null C_BASECODE, null C_METADATAXML, null C_FACTTABLECOLUMN, null C_TABLENAME, null C_COLUMNNAME, null C_COLUMNDATATYPE, null C_OPERATOR, null C_DIMCODE, null C_TOOLTIP WHERE 1=0 -------------------------------------------------------------------- UNION ALL SELECT '1', '\HSD\Fact Date\', 'zz Fact Date', 'N', 'DA ', NULL, NULL, 'start_date', 'DATETIME_DIMENSION', 'start_date', 'N', 'IS NOT', 'NULL', 'Healthare System Dynamics \ Fact Date' UNION ALL SELECT '2', '\HSD\Fact Date\Date Dimension Table\', 'Date Dimension Table', 'N', 'DA ', NULL, NULL, 'start_date', 'DATETIME_DIMENSION', 'start_date', 'N', 'IS NOT', 'NULL', 'Healthare System Dynamics \ Fact Date \ Date Dimension Table' -- *** Month of the year *** UNION ALL SELECT '3', '\HSD\Fact Date\Date Dimension Table\Month of the year\', 'zz Month of the year', 'N', 'DA ', NULL, NULL, 'start_date', 'DATETIME_DIMENSION', 'start_date', 'N', 'IS NOT', 'NULL', 'Healthare System Dynamics \ Fact Date \ Date Dimension Table \ Month of the year' UNION ALL SELECT '4', '\HSD\Fact Date\Date Dimension Table\Month of the year\'+m+'\', ms, 'N', 'RA ', NULL, NULL, 'start_date', 'DATETIME_DIMENSION', 'date_part_month', 'N', '=', n1, 'Healthare System Dynamics \ Fact Date \ Date Dimension Table \ Month of the year \ '+m FROM #n WHERE n<12 -- *** Day of the week *** UNION ALL SELECT '3', '\HSD\Fact Date\Date Dimension Table\Day of the week\', 'zz Day of the week', 'N', 'DA ', NULL, NULL, 'start_date', 'DATETIME_DIMENSION', 'start_date', 'N', 'IS NOT', 'NULL', 'Healthare System Dynamics \ Fact Date \ Date Dimension Table \ Day of the week' UNION ALL SELECT '4', '\HSD\Fact Date\Date Dimension Table\Day of the week\'+w+'\', w, 'N', 'DA ', NULL, NULL, 'start_date', 'DATETIME_DIMENSION', 'date_part_weekday', 'N', 'IN', wi, 'Healthare System Dynamics \ Fact Date \ Date Dimension Table \ Day of the week \ '+w FROM #n WHERE n<2 UNION ALL SELECT '5', '\HSD\Fact Date\Date Dimension Table\Day of the week\'+e+'\'+d+'\', ds, 'N', 'RA ', NULL, NULL, 'start_date', 'DATETIME_DIMENSION', 'date_part_weekday', 'N', '=', n1, 'Healthare System Dynamics \ Fact Date \ Date Dimension Table \ Day of the week \ '+e+' \ '+d FROM #n WHERE n<7 -- *** Hour of the day *** UNION ALL SELECT '3', '\HSD\Fact Date\Date Dimension Table\Hour of the day\', 'Hour of the day', 'N', 'DA ', NULL, NULL, 'start_date', 'DATETIME_DIMENSION', 'start_date', 'N', 'IS NOT', 'NULL', 'Healthare System Dynamics \ Fact Date \ Date Dimension Table \ Hour of the day' UNION ALL SELECT '4', '\HSD\Fact Date\Date Dimension Table\Hour of the day\'+h+'\', hs, 'N', 'RA ', NULL, NULL, 'start_date', 'DATETIME_DIMENSION', 'date_part_hour', 'N', '=', s, 'Healthare System Dynamics \ Fact Date \ Date Dimension Table \ Hour of the day \ '+h+' ('+hp+')' FROM #n WHERE n<24 ) t ---------------------------------------------------------------------------- -- Modifier (Labtests, Unique Modifier Code for Each Fact) ---------------------------------------------------------------------------- INSERT INTO ONT.I2B2 SELECT C_HLEVEL, C_FULLNAME, C_NAME, C_SYNONYM_CD, C_VISUALATTRIBUTES, null C_TOTALNUM, C_BASECODE, C_METADATAXML, C_FACTTABLECOLUMN, C_TABLENAME, C_COLUMNNAME, C_COLUMNDATATYPE, C_OPERATOR, C_DIMCODE, null C_COMMENT, C_TOOLTIP, '\i2b2\Labtests\%' M_APPLIED_PATH, '8/1/2016' UPDATE_DATE, '8/1/2016' DOWNLOAD_DATE, '8/1/2016' IMPORT_DATE, 'HSD' SOURCESYSTEM_CD, null VALUETYPE_CD, null M_EXCLUSION_CD, null C_PATH, null C_SYMBOL FROM ( SELECT null C_HLEVEL, null C_FULLNAME, null C_NAME, null C_SYNONYM_CD, null C_VISUALATTRIBUTES, null C_BASECODE, null C_METADATAXML, null C_FACTTABLECOLUMN, null C_TABLENAME, null C_COLUMNNAME, null C_COLUMNDATATYPE, null C_OPERATOR, null C_DIMCODE, null C_TOOLTIP WHERE 1=0 -------------------------------------------------------------------- UNION ALL SELECT '1', '\HSD\Fact Date\', 'zz Fact Date', 'N', 'DA ', NULL, NULL, 'modifier_cd', 'modifier_hsd_dimension', 'start_date', 'N', 'IS NOT', 'NULL', 'Healthare System Dynamics \ Fact Date' UNION ALL SELECT '2', '\HSD\Fact Date\HSD Modifier Table\', 'HSD Modifier Table', 'N', 'DA ', NULL, NULL, 'modifier_cd', 'modifier_hsd_dimension', 'start_date', 'N', 'IS NOT', 'NULL', 'Healthare System Dynamics \ Fact Date \ HSD Modifier Table' -- *** Month of the year *** UNION ALL SELECT '3', '\HSD\Fact Date\HSD Modifier Table\Month of the year\', 'Month of the year', 'N', 'DA ', NULL, NULL, 'modifier_cd', 'modifier_hsd_dimension', 'start_date', 'N', 'IS NOT', 'NULL', 'Healthare System Dynamics \ Fact Date \ HSD Modifier Table \ Month of the year' UNION ALL SELECT '4', '\HSD\Fact Date\HSD Modifier Table\Month of the year\'+m+'\', ms, 'N', 'RA ', NULL, NULL, 'modifier_cd', 'modifier_hsd_dimension', 'date_part_month', 'N', '=', n1, 'Healthare System Dynamics \ Fact Date \ HSD Modifier Table \ Month of the year \ '+m FROM #n WHERE n<12 -- *** Day of the week *** UNION ALL SELECT '3', '\HSD\Fact Date\HSD Modifier Table\Day of the week\', 'Day of the week', 'N', 'DA ', NULL, NULL, 'modifier_cd', 'modifier_hsd_dimension', 'start_date', 'N', 'IS NOT', 'NULL', 'Healthare System Dynamics \ Fact Date \ HSD Modifier Table \ Day of the week' UNION ALL SELECT '4', '\HSD\Fact Date\HSD Modifier Table\Day of the week\'+w+'\', w, 'N', 'DA ', NULL, NULL, 'modifier_cd', 'modifier_hsd_dimension', 'date_part_weekday', 'N', 'IN', wi, 'Healthare System Dynamics \ Fact Date \ HSD Modifier Table \ Day of the week \ '+w FROM #n WHERE n<2 UNION ALL SELECT '5', '\HSD\Fact Date\HSD Modifier Table\Day of the week\'+e+'\'+d+'\', ds, 'N', 'RA ', NULL, NULL, 'modifier_cd', 'modifier_hsd_dimension', 'date_part_weekday', 'N', '=', n1, 'Healthare System Dynamics \ Fact Date \ HSD Modifier Table \ Day of the week \ '+e+' \ '+d FROM #n WHERE n<7 -- *** Hour of the day *** UNION ALL SELECT '3', '\HSD\Fact Date\HSD Modifier Table\Hour of the day\', ' Hour of the day', 'N', 'DA ', NULL, NULL, 'modifier_cd', 'modifier_hsd_dimension', 'start_date', 'N', 'IS NOT', 'NULL', 'Healthare System Dynamics \ Fact Date \ HSD Modifier Table \ Hour of the day' UNION ALL SELECT '4', '\HSD\Fact Date\HSD Modifier Table\Hour of the day\'+h+'\', hs, 'N', 'RA ', NULL, NULL, 'modifier_cd', 'modifier_hsd_dimension', 'date_part_hour', 'N', '=', s, 'Healthare System Dynamics \ Fact Date \ HSD Modifier Table \ Hour of the day \ '+h+' ('+hp+')' FROM #n WHERE n<24 ) t ---------------------------------------------------------------------------- -- Modifier (Medications, Modifier Code) ---------------------------------------------------------------------------- INSERT INTO ONT.I2B2 SELECT C_HLEVEL, C_FULLNAME, C_NAME, C_SYNONYM_CD, C_VISUALATTRIBUTES, null C_TOTALNUM, C_BASECODE, C_METADATAXML, C_FACTTABLECOLUMN, C_TABLENAME, C_COLUMNNAME, C_COLUMNDATATYPE, C_OPERATOR, C_DIMCODE, null C_COMMENT, C_TOOLTIP, '\i2b2\Medications\%' M_APPLIED_PATH, '8/1/2016' UPDATE_DATE, '8/1/2016' DOWNLOAD_DATE, '8/1/2016' IMPORT_DATE, 'HSD' SOURCESYSTEM_CD, null VALUETYPE_CD, null M_EXCLUSION_CD, null C_PATH, null C_SYMBOL FROM ( SELECT null C_HLEVEL, null C_FULLNAME, null C_NAME, null C_SYNONYM_CD, null C_VISUALATTRIBUTES, null C_BASECODE, null C_METADATAXML, null C_FACTTABLECOLUMN, null C_TABLENAME, null C_COLUMNNAME, null C_COLUMNDATATYPE, null C_OPERATOR, null C_DIMCODE, null C_TOOLTIP WHERE 1=0 -------------------------------------------------------------------- UNION ALL SELECT '1', '\HSD\Fact Date\', 'zz Fact Date', 'N', 'DA ', NULL, NULL, 'modifier_cd', 'modifier_dimension', 'modifier_path', 'T', 'LIKE', '\HSD\Fact Date\Modifier Code\', 'Healthare System Dynamics \ Fact Date' UNION ALL SELECT '2', '\HSD\Fact Date\Modifier Code\', 'Modifier Code', 'N', 'DA ', NULL, NULL, 'modifier_cd', 'modifier_dimension', 'modifier_path', 'T', 'LIKE', '\HSD\Fact Date\Modifier Code\', 'Healthare System Dynamics \ Fact Date \ Modifier Code' -- *** Month of the year *** UNION ALL SELECT '3', '\HSD\Fact Date\Modifier Code\Month of the year\', 'Month of the year', 'N', 'DA ', NULL, NULL, 'modifier_cd', 'modifier_dimension', 'modifier_path', 'T', 'LIKE', '\HSD\Fact Date\Modifier Code\Month of the year\', 'Healthare System Dynamics \ Fact Date \ Modifier Code \ Month of the year' UNION ALL SELECT '4', '\HSD\Fact Date\Modifier Code\Month of the year\'+m+'\', ms, 'N', 'RA ', NULL, NULL, 'modifier_cd', 'modifier_dimension', 'modifier_path', 'T', '=', '\HSD\Fact Date\Modifier Code\Month of the year\'+m+'\', 'Healthare System Dynamics \ Fact Date \ Modifier Code \ Month of the year \ '+m FROM #n WHERE n<12 -- *** Day of the week *** UNION ALL SELECT '3', '\HSD\Fact Date\Modifier Code\Day of the week\', 'Day of the week', 'N', 'DA ', NULL, NULL, 'modifier_cd', 'modifier_dimension', 'modifier_path', 'T', 'LIKE', '\HSD\Fact Date\Modifier Code\Day of the week\', 'Healthare System Dynamics \ Fact Date \ Modifier Code \ Day of the week' UNION ALL SELECT '4', '\HSD\Fact Date\Modifier Code\Day of the week\'+w+'\', w, 'N', 'DA ', NULL, NULL, 'modifier_cd', 'modifier_dimension', 'modifier_path', 'T', 'LIKE', '\HSD\Fact Date\Modifier Code\Day of the week\'+w+'\', 'Healthare System Dynamics \ Fact Date \ Modifier Code \ Day of the week \ '+w FROM #n WHERE n<2 UNION ALL SELECT '5', '\HSD\Fact Date\Modifier Code\Day of the week\'+e+'\'+d+'\', ds, 'N', 'RA ', NULL, NULL, 'modifier_cd', 'modifier_dimension', 'modifier_path', 'T', '=', '\HSD\Fact Date\Modifier Code\Day of the week\'+e+'\'+d+'\', 'Healthare System Dynamics \ Fact Date \ Modifier Code \ Day of the week \ '+e+' \ '+d FROM #n WHERE n<7 -- *** Hour of the day *** UNION ALL SELECT '3', '\HSD\Fact Date\Modifier Code\Hour of the day\', ' Hour of the day', 'N', 'DA ', NULL, NULL, 'modifier_cd', 'modifier_dimension', 'modifier_path', 'T', 'LIKE', '\HSD\Fact Date\Modifier Code\Hour of the day\', 'Healthare System Dynamics \ Fact Date \ Modifier Code \ Hour of the day' UNION ALL SELECT '4', '\HSD\Fact Date\Modifier Code\Hour of the day\'+h+'\', hs, 'N', 'RA ', NULL, NULL, 'modifier_cd', 'modifier_dimension', 'modifier_path', 'T', '=', '\HSD\Fact Date\Modifier Code\Hour of the day\'+h+'\', 'Healthare System Dynamics \ Fact Date \ Modifier Code \ Hour of the day \ '+h+' ('+hp+')' FROM #n WHERE n<24 ) t ---------------------------------------------------------------------------- -- Modifier (Medications, Modifier Value) ---------------------------------------------------------------------------- INSERT INTO ONT.I2B2 SELECT C_HLEVEL, C_FULLNAME, C_NAME, C_SYNONYM_CD, C_VISUALATTRIBUTES, null C_TOTALNUM, C_BASECODE, C_METADATAXML, C_FACTTABLECOLUMN, C_TABLENAME, C_COLUMNNAME, C_COLUMNDATATYPE, C_OPERATOR, C_DIMCODE, null C_COMMENT, C_TOOLTIP, '\i2b2\Medications\%' M_APPLIED_PATH, '8/1/2016' UPDATE_DATE, '8/1/2016' DOWNLOAD_DATE, '8/1/2016' IMPORT_DATE, 'HSD' SOURCESYSTEM_CD, null VALUETYPE_CD, null M_EXCLUSION_CD, null C_PATH, null C_SYMBOL FROM ( SELECT null C_HLEVEL, null C_FULLNAME, null C_NAME, null C_SYNONYM_CD, null C_VISUALATTRIBUTES, null C_BASECODE, null C_METADATAXML, null C_FACTTABLECOLUMN, null C_TABLENAME, null C_COLUMNNAME, null C_COLUMNDATATYPE, null C_OPERATOR, null C_DIMCODE, null C_TOOLTIP WHERE 1=0 -------------------------------------------------------------------- UNION ALL SELECT '2', '\HSD\Fact Date\Modifier Value\', 'Modifier Value', 'N', 'DA ', NULL, NULL, 'modifier_cd', 'modifier_dimension', 'modifier_path', 'T', 'LIKE', '\HSD\Fact Date\Modifier Value\', 'Healthare System Dynamics \ Fact Date \ Modifier Code' -- *** Month of the year *** UNION ALL SELECT '3', '\HSD\Fact Date\Modifier Value\Month of the year\', 'zz Month of the year', 'N', 'RA ', NULL, '3.028/1/2016' +'HSD|DATEPART:MONTHMonth of the Year' +'EnumN' +CAST((SELECT m "Val/@description", n1 "Val" FROM #n WHERE n<12 ORDER BY n FOR XML PATH(''), TYPE) AS VARCHAR(MAX)) +'', 'modifier_cd', 'modifier_dimension', 'modifier_path', 'T', '=', '\HSD\Fact Date\Modifier Value\Month of the year\', 'Healthare System Dynamics \ Fact Date \ Modifier Value \ Month of the year' UNION ALL SELECT '3', '\HSD\Fact Date\Modifier Value\Day of the week\', 'zz Day of the week', 'N', 'RA ', NULL, '3.028/1/2016' +'HSD|DATEPART:WEEKDAYDay of the week' +'EnumN' +CAST((SELECT d "Val/@description", n1 "Val" FROM #n WHERE n<7 ORDER BY n FOR XML PATH(''), TYPE) AS VARCHAR(MAX)) +'', 'modifier_cd', 'modifier_dimension', 'modifier_path', 'T', '=', '\HSD\Fact Date\Modifier Value\Day of the week\', 'Healthare System Dynamics \ Fact Date \ Modifier Value \ Day of the week' UNION ALL SELECT '3', '\HSD\Fact Date\Modifier Value\Hour of the day\', 'Hour of the day', 'N', 'RA ', NULL, '3.028/1/2016' +'HSD|DATEPART:HOURHour of the day' +'EnumN' +CAST((SELECT h+' ('+hp+')' "Val/@description", s "Val" FROM #n WHERE n<24 ORDER BY n FOR XML PATH(''), TYPE) AS VARCHAR(MAX)) +'', 'modifier_cd', 'modifier_dimension', 'modifier_path', 'T', '=', '\HSD\Fact Date\Modifier Value\Hour of the day\', 'Healthare System Dynamics \ Fact Date \ Modifier Value \ Hour of the day' ) t