Cohort Name: Patients diagnosed with an eating disorder
Cohort Size: 342 patients
Cohort Description
Patients that have at least one diagnosis of an eating disorder
Cohort Criteria
Diagnosis from DAD with Rubric Code of "F50"
Cohort Selection SQL
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
WHERE
icd10.rubric_code= 'F50'
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))
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'.
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 1
ELSE DATEDIFF(D,ISNULL(Results.Event_Start_Date,ad.[Date]),
ISNULL(Results.Event_End_Date,
ISNULL(dd.[Date],
CONVERT(date,getdate())))) END),1)
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 "Patients diagnosed with an eating disorder" Cohort
File Name: Patient_Events-342_Eating_Disorder_2017-06-13.csv
Product Name: Patient Events (Version 3)
Cohort Name: Patients diagnosed with an eating disorder
Cohort Size: 342 patients
Cohort Description
Patients that have at least one diagnosis of an eating disorder
Cohort Criteria
Diagnosis from DAD with Rubric Code of "F50"
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 List
Select Statement