/***************************************************************************

Healthcare System Dynamics (HSD) Extensions for i2b2
http://HealthcareSystemDynamics.org

Griffin Weber
weber@hms.harvard.edu

March 21, 2019

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 2012 (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)
5) Time Period - Hour (hours of the day, 12am to 11pm)
6) Repeat Interval - Time since the same test was ordered

This software was funded by NIH/NIGMS grant U01CA198934.

***************************************************************************/



--##########################################################################
--##########################################################################
--##########################################################################
--### CLINICAL RESERCH CHART (CRC) CELL UPDATES
--##########################################################################
--##########################################################################
--##########################################################################



--**************************************************************************
--**************************************************************************
--*** FACT COUNT
--**************************************************************************
--**************************************************************************


-- Compute fact count and fact count percentile

SELECT 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 OBSERVATION_FACT
		GROUP BY PATIENT_NUM
	) t

ALTER TABLE #PatientFactCount ADD PRIMARY KEY (PATIENT_NUM)


-- Add fact count and fact count percentile to the PATIENT_DIMENSION table

ALTER TABLE PATIENT_DIMENSION ADD FACT_COUNT INT;
ALTER TABLE 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 PATIENT_DIMENSION p
		INNER JOIN #PatientFactCount f
			ON p.PATIENT_NUM = f.PATIENT_NUM


-- Add fact count and fact count percentile to the QT_QUERY_RESULT_TYPE and QT_BREAKDOWN_PATH tables

INSERT INTO QT_QUERY_RESULT_TYPE
	SELECT MAX(RESULT_TYPE_ID)+1, 'PATIENT_FACT_COUNT_PERCENTILE_XML', 'Fact Count Percentile patient breakdown', 'CATNUM', 'LA'
	FROM QT_QUERY_RESULT_TYPE

INSERT INTO QT_BREAKDOWN_PATH
	SELECT 'PATIENT_FACT_COUNT_PERCENTILE_XML' NAME, '\\i2b2_HSD\i2b2\HSD\Fact Count\Fact Count Percentile\' VALUE, '1/1/2019' CREATE_DATE, NULL UPDATE_DATE, NULL USER_ID

INSERT INTO QT_QUERY_RESULT_TYPE
	SELECT MAX(RESULT_TYPE_ID)+1, 'PATIENT_FACT_COUNT_VALUE_XML', 'Fact Count Value patient breakdown', 'CATNUM', 'LA'
	FROM QT_QUERY_RESULT_TYPE

INSERT INTO QT_BREAKDOWN_PATH
	SELECT 'PATIENT_FACT_COUNT_VALUE_XML' NAME, '\\i2b2_HSD\i2b2\HSD\Fact Count\Fact Count Value\' VALUE, '1/1/2019' CREATE_DATE, NULL UPDATE_DATE, NULL USER_ID

INSERT INTO QT_QUERY_RESULT_TYPE
	SELECT MAX(RESULT_TYPE_ID)+1, 'PATIENT_FACT_COUNT_VALUE_100to999_XML', 'Fact Count Value (100-999) breakdown', 'CATNUM', 'LA'
	FROM QT_QUERY_RESULT_TYPE

INSERT INTO QT_BREAKDOWN_PATH
	SELECT 'PATIENT_FACT_COUNT_VALUE_100to999_XML' NAME, '\\i2b2_HSD\i2b2\HSD\Fact Count\Fact Count Value\100-999\' VALUE, '1/1/2019' CREATE_DATE, NULL UPDATE_DATE, NULL USER_ID


--**************************************************************************
--**************************************************************************
--*** DATE PARTS AND REPEAT INTERVALS
--**************************************************************************
--**************************************************************************

----------------------------------------------------------------------------
-- create a datetime_dimension table
----------------------------------------------------------------------------

CREATE TABLE DATETIME_DIMENSION (
	START_DATE DATETIME PRIMARY KEY,
	DATE_PART_MONTH TINYINT,
	DATE_PART_WEEKDAY TINYINT,
	DATE_PART_HOUR TINYINT
)

INSERT INTO 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 OBSERVATION_FACT) t
	WHERE START_DATE NOT IN (SELECT START_DATE FROM DATETIME_DIMENSION)

CREATE NONCLUSTERED INDEX IDX_MONTH ON DATETIME_DIMENSION (DATE_PART_MONTH)
CREATE NONCLUSTERED INDEX IDX_WEEKDAY ON DATETIME_DIMENSION (DATE_PART_WEEKDAY)
CREATE NONCLUSTERED INDEX IDX_HOUR ON DATETIME_DIMENSION (DATE_PART_HOUR)


----------------------------------------------------------------------------
-- add repeat interval modifiers to the modifier_dimension table
----------------------------------------------------------------------------

INSERT INTO MODIFIER_DIMENSION
	SELECT MODIFIER_PATH, MODIFIER_CD, NAME_CHAR, '' MODIFIER_BLOB, '1/1/2019' 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\Time since same test\First\', 'HSD|REPEAT:X', 'First occurrence'
			UNION ALL SELECT '\HSD\Fact Date\Time since same test\Seconds\', 'HSD|REPEAT:S', '<1 hour since same test'
			UNION ALL SELECT '\HSD\Fact Date\Time since same test\Hours\', 'HSD|REPEAT:H', '>=1 hour and <1 day since same test'
			UNION ALL SELECT '\HSD\Fact Date\Time since same test\Days\', 'HSD|REPEAT:D', '>=1 day and <1 week since same test'
			UNION ALL SELECT '\HSD\Fact Date\Time since same test\Weeks\', 'HSD|REPEAT:W', '>=1 week and <1 month since same test'
			UNION ALL SELECT '\HSD\Fact Date\Time since same test\Months\', 'HSD|REPEAT:M', '>=1 month and <1 year since same test'
			UNION ALL SELECT '\HSD\Fact Date\Time since same test\Years\', 'HSD|REPEAT:Y', '>=1 year since same test'
		) t


----------------------------------------------------------------------------
-- add repeat interval modifiers to labs in the observation_fact table
----------------------------------------------------------------------------

;WITH a AS (
	SELECT DISTINCT PATIENT_NUM, CONCEPT_CD, START_DATE
	FROM OBSERVATION_FACT
	WHERE CONCEPT_CD LIKE 'LOINC:%'
), b AS (
	SELECT PATIENT_NUM, CONCEPT_CD, START_DATE,
		LAG(START_DATE,1) OVER (PARTITION BY PATIENT_NUM, CONCEPT_CD ORDER BY START_DATE) PREV_DATE
	FROM a
)
UPDATE f
	SET f.MODIFIER_CD = 'HSD|REPEAT:'
		+(CASE WHEN PREV_DATE IS NULL THEN 'X' 
			WHEN DiffY>0 THEN 'Y' 
			WHEN DiffM>0 THEN 'M' 
			WHEN DiffW>0 THEN 'W' 
			WHEN DiffD>0 THEN 'D' 
			WHEN DiffH>0 THEN 'H'
			ELSE 'S' END)
	FROM OBSERVATION_FACT f
		INNER JOIN b 
			ON f.PATIENT_NUM=b.PATIENT_NUM AND f.CONCEPT_CD=b.CONCEPT_CD AND f.START_DATE=b.START_DATE
		CROSS APPLY (
			SELECT 
				DATEDIFF(hh,PREV_DATE,b.START_DATE) DiffH, 
				DATEDIFF(dd,PREV_DATE,b.START_DATE) DiffD, 
				DATEDIFF(ww,PREV_DATE,b.START_DATE) DiffW, 
				DATEDIFF(mm,PREV_DATE,b.START_DATE) DiffM, 
				DATEDIFF(yy,PREV_DATE,b.START_DATE) DiffY
		) d


--**************************************************************************
--**************************************************************************
--*** Undo Changes
--**************************************************************************
--**************************************************************************

/*

DROP TABLE DATETIME_DIMENSION

UPDATE OBSERVATION_FACT 
	SET MODIFIER_CD = '@' 
	WHERE CONCEPT_CD LIKE 'LOINC%' AND MODIFIER_CD LIKE 'HSD:%'

DELETE FROM MODIFIER_DIMENSION WHERE SOURCESYSTEM_CD = 'HSD'

DELETE FROM QT_QUERY_RESULT_TYPE WHERE NAME IN ('PATIENT_FACT_COUNT_PERCENTILE_XML','PATIENT_FACT_COUNT_VALUE_XML','PATIENT_FACT_COUNT_VALUE_100to999_XML')
DELETE FROM QT_BREAKDOWN_PATH WHERE NAME IN ('PATIENT_FACT_COUNT_PERCENTILE_XML','PATIENT_FACT_COUNT_VALUE_XML','PATIENT_FACT_COUNT_VALUE_100to999_XML')

*/


--##########################################################################
--##########################################################################
--##########################################################################
--### ONTOLOGY (ONT) CELL UPDATES
--##########################################################################
--##########################################################################
--##########################################################################



--**************************************************************************
--**************************************************************************
--*** Temp table of numbers and 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,
		(CASE WHEN n<10 THEN REPLICATE('0',n) ELSE NULL END) n0s,
		(CASE WHEN n<10 THEN REPLICATE('9',n) ELSE NULL END) n9s
	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)


--**************************************************************************
--**************************************************************************
--*** Table Access
--**************************************************************************
--**************************************************************************


INSERT INTO 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


--**************************************************************************
--**************************************************************************
--*** HSD Root Folder
--**************************************************************************
--**************************************************************************

INSERT INTO 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, 
		'1/1/2019' UPDATE_DATE, '1/1/2019' DOWNLOAD_DATE, '1/1/2019' 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 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, 
		'1/1/2019' UPDATE_DATE, '1/1/2019' DOWNLOAD_DATE, '1/1/2019' 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', 'FA ', 
			NULL, NULL, 
			'patient_num', 'patient_dimension', 'fact_count', 'N', '>=', '0',
			'Healthcare System Dynamics \ Fact Count' 
		-- *** Fact Count Percentile ***
		UNION ALL
		SELECT '3', '\i2b2\HSD\Fact Count\Fact Count Percentile\', 'Fact Count Percentile', 'N', 'FA ', 
			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
		-- *** Fact Count Value ***
		UNION ALL
		SELECT '3', '\i2b2\HSD\Fact Count\Fact Count Value\', 'Fact Count Value', 'N', 'FA ', 
			NULL, NULL, 
			'patient_num', 'patient_dimension', 'fact_count', 'N', '>=', '0', 
			'Healthcare System Dynamics \ Fact Count \ Fact Count Value'
		UNION ALL
		SELECT '4', '\i2b2\HSD\Fact Count\Fact Count Value\0\', ' 0 Facts', 'N', 'LA ', 
			NULL, NULL, 
			'patient_num', 'patient_dimension', 'fact_count', 'N', '=', '0', 
			'Healthcare System Dynamics \ Fact Count \ Fact Count Value \ 0 Facts'
		UNION ALL
		SELECT '4', '\i2b2\HSD\Fact Count\Fact Count Value\1'+n0s+'-9'+n9s+'\', ' 1'+n0s+'-9'+n9s+' Facts', 'N', 'FA ', 
			NULL, NULL, 
			'patient_num', 'patient_dimension', 'fact_count', 'N', 'BETWEEN', '1'+n0s+' AND 9'+n9s, 
			'Healthcare System Dynamics \ Fact Count \ Fact Count Value \ 1'+n0s+'-9'+n9s+' Facts'
			FROM #n WHERE n<4
		UNION ALL
		SELECT '5', '\i2b2\HSD\Fact Count\Fact Count Value\1-9\'+s, (CASE WHEN n=1 THEN '1 Fact' ELSE s+' Facts' END), 'N', 'LA ', 
			NULL, NULL, 
			'patient_num', 'patient_dimension', 'fact_count', 'N', '=', s, 
			'Healthcare System Dynamics \ Fact Count \ Fact Count Value \ '+(CASE WHEN n=1 THEN '1 Fact' ELSE s+' Facts' END)
			FROM #n WHERE n BETWEEN 1 AND 9
		UNION ALL
		SELECT '5', '\i2b2\HSD\Fact Count\Fact Count Value\1'+m.n0s+'-9'+m.n9s+'\'+n.s+m.n0s+'-'+n.s+m.n9s, n.s+m.n0s+'-'+n.s+m.n9s+' Facts', 'N', 'LA ', 
			NULL, NULL, 
			'patient_num', 'patient_dimension', 'fact_count', 'N', 'BETWEEN', n.s+m.n0s+' AND '+n.s+m.n9s, 
			'Healthcare System Dynamics \ Fact Count \ Fact Count Value \ 1'+m.n0s+'-9'+m.n9s+' Facts \ '+n.s+m.n0s+'-'+n.s+m.n9s+' Facts'
			FROM #n n, #n m
			WHERE n.n BETWEEN 1 AND 9 AND m.n BETWEEN 1 AND 3
		UNION ALL
		SELECT '4', '\i2b2\HSD\Fact Count\Fact Count Value\>=10000\', '>=10000 Facts', 'N', 'LA ', 
			NULL, NULL, 
			'patient_num', 'patient_dimension', 'fact_count', 'N', '>=', '10000', 
			'Healthcare System Dynamics \ Fact Count \ Fact Count Value \ >=10000 Facts'
	) t


--**************************************************************************
--**************************************************************************
--*** Date Parts
--**************************************************************************
--**************************************************************************


----------------------------------------------------------------------------
-- Visit Dimension
----------------------------------------------------------------------------
	
INSERT INTO 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, 
		'1/1/2019' UPDATE_DATE, '1/1/2019' DOWNLOAD_DATE, '1/1/2019' 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



----------------------------------------------------------------------------
-- Date Dimension
----------------------------------------------------------------------------

INSERT INTO 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, 
		'1/1/2019' UPDATE_DATE, '1/1/2019' DOWNLOAD_DATE, '1/1/2019' 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 Date\', 'Fact Date', 'N', 'FA ', 
			NULL, NULL, 
			'start_date', 'DATETIME_DIMENSION', 'start_date', 'N', 'IS NOT', 'NULL',
			'Healthare System Dynamics \ Fact Date'
		-- *** Month of the year ***
		UNION ALL
		SELECT '3', '\i2b2\HSD\Fact Date\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 \ Month of the year'
		UNION ALL
		SELECT '4', '\i2b2\HSD\Fact Date\Month of the year\'+m+'\', ms, 'N', 'LA ', 
			NULL, NULL, 
			'start_date', 'DATETIME_DIMENSION', 'date_part_month', 'N', '=', n1, 
			'Healthare System Dynamics \ Fact Date \ Month of the year \ '+m
			FROM #n WHERE n<12
		-- *** Day of the week ***
		UNION ALL
		SELECT '3', '\i2b2\HSD\Fact Date\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 \ Day of the week'
		UNION ALL
		SELECT '4', '\i2b2\HSD\Fact Date\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 \ Day of the week \ '+w
			FROM #n WHERE n<2
		UNION ALL
		SELECT '5', '\i2b2\HSD\Fact Date\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 \ Day of the week \ '+e+' \ '+d 
			FROM #n WHERE n<7
		-- *** Hour of the day ***
		UNION ALL
		SELECT '3', '\i2b2\HSD\Fact Date\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 \Hour of the day'
		UNION ALL
		SELECT '4', '\i2b2\HSD\Fact Date\Hour of the day\'+h+'\', hs, 'N', 'LA ', 
			NULL, NULL, 
			'start_date', 'DATETIME_DIMENSION', 'date_part_hour', 'N', '=', s, 
			'Healthare System Dynamics \ Fact Date \ Hour of the day \ '+h+' ('+hp+')'
			FROM #n WHERE n<24
	) t


----------------------------------------------------------------------------
-- Lab Test Modifier
----------------------------------------------------------------------------

INSERT INTO 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, 
		'1/1/2019' UPDATE_DATE, '1/1/2019' DOWNLOAD_DATE, '1/1/2019' 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\', 'Fact Date', 'N', 'DA ', 
			NULL, NULL, 
			'start_date', 'DATETIME_DIMENSION', 'start_date', 'N', 'IS NOT', 'NULL',
			'Healthare System Dynamics \ Fact Date'
		-- *** Month of the year ***
		UNION ALL
		SELECT '2', '\HSD\Fact Date\Month of the year\', 'Month of the year', 'N', 'DA ', 
			NULL, NULL, 
			'start_date', 'DATETIME_DIMENSION', 'start_date', 'N', 'IS NOT', 'NULL',
			'Healthare System Dynamics \ Fact Date \ Month of the year'
		UNION ALL
		SELECT '3', '\HSD\Fact Date\Month of the year\'+m+'\', ms, 'N', 'RA ', 
			NULL, NULL, 
			'start_date', 'DATETIME_DIMENSION', 'date_part_month', 'N', '=', n1, 
			'Healthare System Dynamics \ Fact Date \ Month of the year \ '+m
			FROM #n WHERE n<12
		-- *** Day of the week ***
		UNION ALL
		SELECT '2', '\HSD\Fact Date\Day of the week\', 'Day of the week', 'N', 'DA ', 
			NULL, NULL, 
			'start_date', 'DATETIME_DIMENSION', 'start_date', 'N', 'IS NOT', 'NULL',
			'Healthare System Dynamics \ Fact Date \ Day of the week'
		UNION ALL
		SELECT '3', '\HSD\Fact Date\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 \ Day of the week \ '+w
			FROM #n WHERE n<2
		UNION ALL
		SELECT '4', '\HSD\Fact Date\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 \ Day of the week \ '+e+' \ '+d 
			FROM #n WHERE n<7
		-- *** Hour of the day ***
		UNION ALL
		SELECT '2', '\HSD\Fact Date\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 \ Hour of the day'
		UNION ALL
		SELECT '3', '\HSD\Fact Date\Hour of the day\'+h+'\', hs, 'N', 'RA ', 
			NULL, NULL, 
			'start_date', 'DATETIME_DIMENSION', 'date_part_hour', 'N', '=', s, 
			'Healthare System Dynamics \ Fact Date \ Hour of the day \ '+h+' ('+hp+')'
			FROM #n WHERE n<24
		-- *** Repeat interval ***
		UNION ALL
		SELECT '2', '\HSD\Fact Date\Time since same test\', 'Time since same test', 'N', 'DA ', 
			NULL, NULL, 
			'modifier_cd', 'modifier_dimension', 'modifier_path', 'T', 'LIKE', '\HSD\Fact Date\Time since same test\',
			'Healthare System Dynamics \ Fact Date \ Time since same test'
		UNION ALL
		SELECT '3', '\HSD\Fact Date\Time since same test\'+sPath+'\', sName, 'N', 'RA ', 
			NULL, NULL, 
			'modifier_cd', 'modifier_dimension', 'modifier_path', 'T', 'LIKE', '\HSD\Fact Date\Time since same test\'+sPath+'\',
			'Healthare System Dynamics \ Fact Date \ Time since same test \ '+ltrim(sName)
			FROM (
				SELECT 'First' sPath, '     First occurrence' sName
				UNION ALL SELECT 'Seconds', '    &lt;1 hour since same test'
				UNION ALL SELECT 'Hours', '   &gt;=1 hour and &lt;1 day since same test'
				UNION ALL SELECT 'Days', '  &gt;=1 day and &lt;1 week since same test'
				UNION ALL SELECT 'Weeks', ' &gt;=1 week and &lt;1 month since same test'
				UNION ALL SELECT 'Months', '&gt;=1 month and &lt;1 year since same test'
				UNION ALL SELECT 'Years', '&gt;=1 year since same test'
			) t
	) t


--**************************************************************************
--**************************************************************************
--*** Undo Changes
--**************************************************************************
--**************************************************************************

/*

DELETE FROM TABLE_ACCESS WHERE C_TABLE_CD = 'i2b2_HSD'

DELETE FROM I2B2 WHERE C_FULLNAME LIKE '\i2b2\HSD\%'
DELETE FROM I2B2 WHERE C_FULLNAME LIKE '\i2b2\Visit Details\Start date\%'
DELETE FROM I2B2 WHERE C_FULLNAME LIKE '\HSD\%'

*/