2017-05-16 - First Extract of "Patient Events" for "Patients with Mental Health Diagnosis"

[ssrobertson]

Data Extract Details

File Name: Patient_Events-30039_MH_Diagnosis_2017-05-16.csv

Product Name: Patient Events

Cohort Name: Patients with Mental Health Diagnosis

Cohort Size: 30,039 patients

Cohort Description

Patients with a diagnosis of ICD10 codes between F00 and F99

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'.

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
	 pc.Cohort_Patient_Encrypted						as Cohort_Patient_ID
	,ISNULL(Results.Event_type,'Encounter Only')				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
	,et.Encounter_Class							as Encounter_Class
	,et.Encounter_Type							as Encounter_Type
	,fe.ENCNTR_ID								as Encounter_ID
	,da.AgeGroupA2								as Age_Group
	,lm.Location_Map_ID							as Location_Map_ID
	,dg.Gender								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 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  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
		doc.Activity_Type as Event_Type,
		doc.Clinical_Group 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 doc on doc.Order_Catalog_Dim_Key = fco.Order_Catalog_Dim_Key
		inner join Star.dbo.D_DATE dds on dds.Date_Dim_Key = fco.Ordered_At_Date_Dim_Key
		inner join Star.dbo.D_DATE dde on dde.Date_Dim_Key = fco.Completed_Date_Dim_Key
	WHERE 
		fco.Ordered_At_Date_Dim_Key > 0
		and doc.Activity_Type in (
			'Evaluation and Management'
			,'General Lab'
			,'Micro'
			--,'Patient Care'
			--,'Pharmacy'
			,'Radiology'
			,'Surgery'
		)
		and fco.Order_Status_Dim_Key not in(2,4,12)
		and (fco.Ordered_At_Date_Dim_Key >= @Period_Start_Key AND fco.Ordered_At_Date_Dim_Key <= @Period_End_Key) 

	) 
UNION
	(
	SELECT
		'Pharmacy' as Event_Type,
		dd.ATC_Level_4_Name as Event_Title,
		dd.Cerner_Synonym_Name+(SELECT CASE WHEN fp.Avg_Dosed_DDD_Amount > 0 THEN '; Avg DDD: '+cast(fp.Avg_Dosed_DDD_Amount as varchar) ELSE '' END) as Event_Detail,
		dds.[date] as Event_Start_Date, 
		ISNULL(dde.[date],CONVERT(date,getdate()))  as Event_End_Date,
		fp.Encounter_Dim_Key as Encounter_ID
FROM 
		[Research].[Shared].F_Pharmacy fp
		inner join star.dbo.D_Drug dd on dd.Drug_Dim_Key = fp.Drug_Dim_Key
		inner join Star.dbo.D_DATE dds on dds.Date_Dim_Key = fp.Start_Date_Dim_Key
		inner join Star.dbo.D_DATE dde on dde.Date_Dim_Key = fp.End_Date_Dim_Key
	WHERE
		(fp.Start_Date_Dim_Key >= @Period_Start_Key AND fp.Start_Date_Dim_Key <= @Period_End_Key) 

	) 

) as results ON results.Encounter_ID = fe.ENCNTR_ID
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))