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))
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:
Cohort Selection SQL
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
Select Statement