Data Extract Description: "Patient Events" Product for "3T Research Project Full Cohort" Cohort

[ssrobertson]

Data Extract Description: "Patient Events" Product for "3T Research Project Full Cohort" Cohort

File Name: Patient_Events-170565_3T_Research_Full_2017-06-09.csv

Product Name: Patient Events (Version 3)

Cohort Name: 3T Research Project Full Cohort

Cohort Size: 170,565 patients

Cohort Description

Patients that are in the scope of the research project: Typologies, Trajectories and Transitions in Island Health Mental Health & Substance Use High Risk/High Needs Populations

Cohort Criteria

The patient meets at least one of the following criteria:

• had at least one encounter with an MHSU or Psychogeriatric program during the time period, or
• had a Mental Health diagnosis (MCC=17) at a location where the Location_Inclusion_Code =2, or
• had a Mental Health procedure performed at a location where the Inclusion_Code = 2, or
• has reported using certain drugs

Cohort Selection SQL

SELECT DISTINCT 
	cohort.Patient_Dim_Key
FROM
(
	(
		-- The person has had at least one encounter with an MHSU or Psychogeriatric program during the time period

		SELECT DISTINCT
			enc.Patient_Dim_Key as Patient_Dim_Key
				
		FROM 
			Shared.D_Location_Map lm
			INNER JOIN Research.Shared.D_Location_Palette lp
				ON lp.Palette_Code = lm.Palette_Code
			INNER JOIN Star.dbo.F_ENCOUNTER enc
				ON enc.Discharge_Location_Dim_Key = lm.Location_Dim_Key
		WHERE 
			(lp.Location_Inclusion_Code = 1 
			OR enc.Patient_Service_Dim_Key in(104,105))
			AND 
			((enc.Discharge_Date_Dim_Key >= @Period_Start_Key AND enc.Discharge_Date_Dim_Key <= @Period_End_Key) OR 
			(enc.Admit_Date_Dim_Key >= @Period_Start_Key AND enc.Admit_Date_Dim_Key <= @Period_End_Key) OR 
			(enc.Admit_Date_Dim_Key >= @Period_Start_Key AND enc.Discharge_Date_Dim_Key < 1))
	)
	UNION
	(
		-- The person has had a Mental Health diagnosis (MCC=17) at a location where the Location_Inclusion_Code =2
		SELECT DISTINCT
			enc.Patient_Dim_Key as Patient_Dim_Key
		FROM DW_Star.dbo.F_CIHI_DAD_ABSTRACT ab
			inner join Star.dbo.D_ENCOUNTER de
				on de.Encounter_Number = ab.Encounter_Number
			inner join Star.dbo.F_ENCOUNTER enc
				ON enc.Encounter_Dim_Key = de.Encounter_Dim_Key
			INNER JOIN DW_Star.dbo.F_CIHI_DAD_DIAGNOSIS dx
				ON dx.CIHI_Abstract_Dim_Key = ab.CIHI_Abstract_Dim_Key
			INNER JOIN DW_Star.dbo.D_CIHI_ICD10 icd10
				ON icd10.CIHI_ICD10_Dim_Key = dx.CIHI_ICD10_Dim_Key
			INNER JOIN DW_Star.dbo.D_Date dd
				ON dd.Date_Dim_Key = ab.Discharge_Date_Dim_Key
			INNER JOIN ETL.ICD10_MCC_Mapping mcc
				ON mcc.ICD10_CIHI_Value = icd10.ICD10_Code 
			INNER JOIN Shared.D_Location_Map lm
				ON lm.Location_Dim_Key = 
					ISNULL((SELECT CASE WHEN ISNULL(enc.Latest_Location_Dim_Key,-1) > 0 THEN enc.Latest_Location_Dim_Key
					WHEN ISNULL(enc.Discharge_Location_Dim_Key,-1) > 0 THEN enc.Discharge_Location_Dim_Key
					ELSE enc.Admit_Location_Dim_Key END),-1)
			INNER JOIN Research.Shared.D_Location_Palette lp
				ON lp.Palette_Code = lm.Palette_Code
		WHERE
			lp.Location_Inclusion_Code = 2
			AND 
			((enc.Discharge_Date_Dim_Key >= @Period_Start_Key AND enc.Discharge_Date_Dim_Key <= @Period_End_Key) OR 
			(enc.Admit_Date_Dim_Key >= @Period_Start_Key AND enc.Admit_Date_Dim_Key <= @Period_End_Key) OR 
			(enc.Admit_Date_Dim_Key >= @Period_Start_Key AND enc.Discharge_Date_Dim_Key < 1))
			and mcc.MCC = '17' and mcc.Valid_From <= dd.date and dd.date <= mcc.Valid_To
	)
	UNION
	(
		-- The person has had a Mental Health procedure performed at a location where the Inclusion_Code = 2
		SELECT DISTINCT
			enc.patient_dim_key as Patient_Dim_Key
		FROM 
			DW_Star.dbo.F_CIHI_DAD_ABSTRACT ab
			inner join Star.dbo.D_ENCOUNTER de				
				on de.Encounter_Number = ab.Encounter_Number
			inner join Star.dbo.F_ENCOUNTER enc									
				ON enc.Encounter_Dim_Key = de.Encounter_Dim_Key
			INNER JOIN DW_Star.[dbo].[F_CIHI_DAD_INTERVENTION_PROCEDURE] prc 
				ON prc.CIHI_Abstract_Dim_Key = ab.CIHI_Abstract_Dim_Key
			INNER JOIN DW_Star.[dbo].[D_CIHI_CCI_INTERVENTION] interv	
				ON interv.[CCI_Intervention_Dim_Key] = prc.[CCI_Intervention_Dim_Key]
			INNER JOIN Shared.D_Location_Map lm 
				ON lm.Location_Dim_Key = 
					ISNULL((SELECT CASE WHEN ISNULL(enc.Latest_Location_Dim_Key,-1) > 0 THEN enc.Latest_Location_Dim_Key
					WHEN ISNULL(enc.Discharge_Location_Dim_Key,-1) > 0 THEN enc.Discharge_Location_Dim_Key	
					ELSE enc.Admit_Location_Dim_Key END),-1)
			INNER JOIN Research.Shared.D_Location_Palette lp
				ON lp.Palette_Code = lm.Palette_Code
		WHERE 
			interv.[CIHI_Procedure_Code]
				IN (
					'1AN09HAX7',
					'1AN09JADV',
					'1ZZ35CAP5',
					'1ZZ35CAP6',
					'1ZZ35CAP7',
					'1ZZ35CAP8',
					'6AA08PE',
					'6AA30MASA',
					'6KA08CP',
					'7SF15TZ'
				)
				or interv.[CIHI_Procedure_Code] like '6AA02%'
				or interv.[CIHI_Procedure_Code] like '6AA10%'
				or interv.[CIHI_Procedure_Code] like '6AA30BE%'
				or interv.[CIHI_Procedure_Code] like '6AA30CT%'
				or interv.[CIHI_Procedure_Code] like '6AA30ZZ%'
				or interv.[CIHI_Procedure_Code] like '6DA30DC%'
				or interv.[CIHI_Procedure_Code] like '6DA30DF%'
				or interv.[CIHI_Procedure_Code] like '6DA30DS%'
				or interv.[CIHI_Procedure_Code] like '6DA30DW%'
				or interv.[CIHI_Procedure_Code] like '6DA30DX%'
			and lp.Location_Inclusion_Code = 2
			AND 
			((enc.Discharge_Date_Dim_Key >= @Period_Start_Key AND enc.Discharge_Date_Dim_Key <= @Period_End_Key) OR 
			(enc.Admit_Date_Dim_Key >= @Period_Start_Key AND enc.Admit_Date_Dim_Key <= @Period_End_Key) OR 
			(enc.Admit_Date_Dim_Key >= @Period_Start_Key AND enc.Discharge_Date_Dim_Key < 1))
	)
	UNION
	(
		-- The person has reported using certain drugs (Note that this is using all encounters to determine, not using above date filters)
		SELECT DISTINCT
			enc.patient_dim_key as Patient_Dim_Key
		FROM 
			Staging.MHSU_MRR.SERVICE_EPISODE AS se 
			JOIN Staging.MHSU_MRR.SUBSTANCE_USE AS u 
				ON u.S1 = se.S1 
			JOIN Star.dbo.F_Encounter enc 
				ON enc.ENCNTR_ID = se.Encntr_id

		WHERE 
			(u.U3 LIKE 'Cocaine%' 
			OR u.U3 LIKE 'Crack-cocaine%' 
			OR u.U3 LIKE 'Heroin%' 
			OR u.U3 LIKE 'Methadone%' 
			OR u.U3 LIKE 'Opiates%' 
			OR u.U3 LIKE 'Methamph%')
			AND
			((enc.Discharge_Date_Dim_Key >= @Period_Start_Key AND enc.Discharge_Date_Dim_Key <= @Period_End_Key) OR 
			(enc.Admit_Date_Dim_Key >= @Period_Start_Key AND enc.Admit_Date_Dim_Key <= @Period_End_Key) OR 
			(enc.Admit_Date_Dim_Key >= @Period_Start_Key AND enc.Discharge_Date_Dim_Key < 1))

	)
) as cohort
WHERE Patient_Dim_Key > 0 

Product Description

List of "Encounter Events" for the patients included in the cohort. For those encounters that do not have a event associated with it, the Event_Type column will be 'Encounter Only'.

Version Notes

Added Organism Identified as another Event_Type.

Column Definitions

 Cohort_Patient_ID	varchar(20)
,Event_type		varchar(50)
,Event_Title		varchar(255)
,Event_Detail		varchar(2048)
,Palette_Code		int
,Palette_Colour_Name	varchar(255)
,Event_Start_Date	datetime2(2)
,Event_End_Date		datetime2(2)
,Encounter_Class	varchar(255)
,Encounter_Type		varchar(255)
,Encounter_Fact_Key	int
,Age_Group		varchar(50)
,Loc_Class_ID		int
,Gender			varchar(50)
,Addiction_Loc_Count	int
,Event_Count		int
,Event_Year		int
,Event_Month		int
,Start_Day		int
,End_Day		int
,Duration_Days		int

Select Statement

SELECT 
	CAST([DEID_App].[dbo].[udf_Encrypt_String](pc.Patient_Dim_Key,@Cohort_Seed+@Product_Seed ) as varchar(12)) as Cohort_Patient_ID
	,@delim+ISNULL(Results.Event_type,'Encounter Only')+@delim				AS Event_type
	,@delim+ISNULL(Results.Event_Title,dps.Service_Group)+@delim		AS Event_Title
	,@delim+ISNULL(Results.Event_Detail,dps.[Service])+@delim		AS Event_Detail
	,dlp.Palette_Code							AS Palette_Code
	,@delim+ISNULL(dlp.Palette_Colour_Name,'')+@delim			AS Palette_Colour_Name
	,ISNULL(Results.Event_Start_Date,ad.[Date])				AS Event_Start_Date
	,ISNULL(Results.Event_End_Date,
		ISNULL(dd.[Date],CONVERT(date,getdate())))			AS Event_End_Date
	,@delim+et.Encounter_Class+@delim							AS Encounter_Class
	,@delim+et.Encounter_Type+@delim							AS Encounter_Type
	,CAST([DEID_App].[dbo].[udf_Encrypt_String](fe.ENCNTR_ID,@Product_Seed ) as varchar(12)) as Encounter_ID
	,@delim+da.AgeGroupA2+@delim								AS Age_Group
	,lm.Location_Map_ID							AS Location_Map_ID
	,@delim+dg.Gender+@delim								AS Gender
	,(SELECT CASE WHEN lm.Location_Map_ID IN 
		(5,38,139,313,788,874,895,903,914,916,942,1052,1320,1370,1502,1511,1554,1582,1720,1748,1773,2116,2182,2238)
		THEN 1 ELSE 0 END )						AS Addiction_Location_Count
	,1									AS Event_Count
	,ISNULL(YEAR(ISNULL(Results.Event_Start_Date,ad.[Date])),0)		AS Event_Year
	,ISNULL(MONTH(ISNULL(Results.Event_Start_Date,ad.[Date])),0)		AS Event_Month
	,DATEDIFF(D,pc.First_Admit_Date,
		ISNULL(Results.Event_Start_Date,ad.[Date]))			AS Start_Day
	,DATEDIFF(D,pc.First_Admit_Date,
		ISNULL(Results.Event_End_Date,
			ISNULL(dd.[Date],
				CONVERT(date,getdate()))))			AS End_Day
	,ISNULL((SELECT CASE 
			WHEN Results.Event_End_Date < Results.Event_Start_Date 	
			THEN 0 
			ELSE DATEDIFF(D,ISNULL(Results.Event_Start_Date,ad.[Date]),
				ISNULL(Results.Event_End_Date,
					ISNULL(dd.[Date],
						CONVERT(date,getdate())))) END),0)
										AS Duration_Days
FROM
	Star.dbo.F_Encounter fe 
	INNER JOIN Shared.D_Location_Map	AS lm	ON lm.Location_Dim_Key	= ISNULL((SELECT CASE WHEN ISNULL(fe.Latest_Location_Dim_Key,-1) > 0 THEN fe.Latest_Location_Dim_Key
																							WHEN ISNULL(fe.Discharge_Location_Dim_Key,-1) > 0 THEN fe.Discharge_Location_Dim_Key
																							ELSE fe.Admit_Location_Dim_Key END),-1)
	INNER JOIN Shared.D_Location_Palette	AS dlp	ON dlp.Location_Palette_Dim_Key	= lm.Palette_Code
	INNER JOIN Shared.D_Cohort_Patient	AS pc	ON pc.Patient_Dim_Key		= fe.Patient_Dim_Key and pc.Cohort_Dim_Key = @Cohort_Dim_Key
	INNER JOIN Star.dbo.D_Encounter_Type	AS et	ON fe.Encounter_Type_Dim_Key	= et.Encounter_Type_Dim_Key
	INNER JOIN Star.dbo.D_Patient_Service	AS dps	ON dps.Patient_Service_Dim_Key	= fe.Patient_Service_Dim_Key
	INNER JOIN Star.dbo.D_Age		AS da	ON da.Age_Dim_Key		= (SELECT CASE WHEN fe.Admit_Age_Dim_Key > 0 THEN fe.Admit_Age_Dim_Key WHEN fe.Discharge_Age_Dim_Key > 0 THEN fe.Discharge_Age_Dim_Key ELSE -1 END)
	INNER JOIN Star.dbo.D_GENDER		AS dg	ON fe.Gender_Dim_Key		= dg.Gender_Dim_Key
	INNER JOIN Star.dbo.D_Date		AS ad	ON ad.Date_Dim_Key		= fe.Admit_Date_Dim_Key
	INNER JOIN Star.dbo.D_Date		AS dd	ON dd.Date_Dim_Key		= fe.Discharge_Date_Dim_Key
	LEFT OUTER JOIN 
(
	(SELECT 
		'Diagnosis'				AS Event_Type,
		dci.Chapter_Title		AS Event_Title,
		dci.ICD10_Display		AS Event_Detail,
		dd.[date]				AS Event_Start_Date, 
		dd.[date]				AS Event_End_Date,
		fd.Encounter_Dim_Key	AS Encounter_ID
	FROM 
		Shared.F_DIAGNOSIS						AS fd
		INNER JOIN Star.dbo.D_Date				AS dd	ON dd.Date_Dim_Key			= fd.Diagnosis_Date_Dim_Key
		INNER JOIN DW_Star.dbo.D_CIHI_ICD10		AS dci	ON dci.CIHI_ICD10_Dim_Key	= fd.CIHI_ICD10_Dim_Key
	WHERE
		(fd.Diagnosis_Date_Dim_Key >= @Period_Start_Key AND fd.Diagnosis_Date_Dim_Key <= @Period_End_Key) 

	) 
UNION
	(
	SELECT
		'Procedure'									AS Event_Type,
		dci.block_name								AS Event_Title,
		dci.Procedure_Display						AS Event_Detail,
		ad.[date]									AS Event_Start_Date, 
		ISNULL(dd.[date],CONVERT(date,getdate()))	AS Event_End_Date,
		fp.Encounter_Dim_Key						AS Encounter_ID
	FROM 
		Shared.F_PROCEDURE								AS fp
		INNER JOIN Star.dbo.D_Date						AS ad	ON ad.Date_Dim_Key				= fp.Admit_Date_Dim_Key
		INNER JOIN Star.dbo.D_Date						AS dd	ON dd.Date_Dim_Key				= fp.Discharge_Date_Dim_Key
		INNER JOIN DW_Star.dbo.D_CIHI_CCI_INTERVENTION	AS dci	ON dci.CCI_Intervention_Dim_Key	= fp.CCI_Intervention_Dim_Key
	WHERE
		(fp.Admit_Date_Dim_Key >= @Period_Start_Key AND fp.Admit_Date_Dim_Key <= @Period_End_Key) 

	) 
UNION
	(
	SELECT
		(CASE WHEN doc.Activity_Type = doc.Catalog_Type_Group 
				THEN doc.Activity_Type ELSE doc.Catalog_Type_Group+' - ' + doc.Activity_Type 
		END)										AS Event_Type,
		(CASE WHEN doc.Catalog_Type_Group = 'Laboratory' OR doc.Activity_Type = 'Surgery'
				THEN dog.Order_Group_Name ELSE ISNULL(doc.Clinical_Group,'Other') 
		END)										AS Event_Title,
		doc.Order_Name								AS Event_Detail,
		dds.[date]									AS Event_Start_Date, 
		ISNULL(dde.[date],CONVERT(date,getdate()))  AS Event_End_Date,
		fco.Encounter_Dim_Key						AS Encounter_ID
	FROM 
		[Research].[Shared].F_Clinical_Order fco
		inner join star.CLINICAL_ORDERS.D_ORDER_CATALOG	AS doc	ON doc.Order_Catalog_Dim_Key	= fco.Order_Catalog_Dim_Key
		inner join star.CLINICAL_ORDERS.D_ORDER_GROUP	AS dog	ON dog.Order_Group_Dim_Key		= fco.Order_Group_Dim_Key
		inner join Star.dbo.D_DATE						AS dds	ON dds.Date_Dim_Key				= fco.Ordered_At_Date_Dim_Key
		inner join Star.dbo.D_DATE						AS dde	ON dde.Date_Dim_Key				= fco.Completed_Date_Dim_Key
	WHERE 
		fco.Ordered_At_Date_Dim_Key > 0
		and (fco.Ordered_At_Date_Dim_Key >= @Period_Start_Key AND fco.Ordered_At_Date_Dim_Key <= @Period_End_Key) 

	) 
UNION
	(
	SELECT
		'Organism Identified'						AS Event_Type,
		daos.Order_Name								AS Event_Title,
		dao.Organism_Name							AS Event_Detail,
		dds.[date]									AS Event_Start_Date, 
		dds.[Date]									AS Event_End_Date,
		fo.Encounter_Dim_Key						AS Encounter_ID
	FROM 
		Research.Shared.F_Organism fo
		inner join star.dbo.D_ANTIBIOGRAM_ORDER_SPECIMEN daos on daos.Antibiogram_Order_Specimen_Dim_Key = fo.Antibiogram_Order_Specimen_Dim_Key
		inner join star.dbo.D_ANTIBIOGRAM_ORGANISM dao on dao.Antibiogram_Organism_Dim_Key = fo.Antibiogram_Organism_Dim_Key
		inner join Star.dbo.D_DATE						AS dds	ON dds.Date_Dim_Key				= fo.Collection_Date_Dim_Key
	WHERE 
		(fo.Collection_Date_Dim_Key >= @Period_Start_Key AND fo.Collection_Date_Dim_Key <= @Period_End_Key) 

	) 

) AS results ON results.Encounter_ID = fe.Encounter_Dim_Key
WHERE 
	et.Encounter_Class <> 'PreAdmit'
	AND 
	((fe.Discharge_Date_Dim_Key >= @Period_Start_Key AND fe.Discharge_Date_Dim_Key <= @Period_End_Key) OR 
	(fe.Admit_Date_Dim_Key >= @Period_Start_Key AND fe.Admit_Date_Dim_Key <= @Period_End_Key) OR 
	(fe.Admit_Date_Dim_Key >= @Period_Start_Key AND fe.Discharge_Date_Dim_Key < 1))