For Rebecca: needs a few cohort subsets based on type of counselling services.
Cohort Criteria
Have accessed Palette 84 services, and have been diagnosed with F32 or F33
Cohort Selection SQL
SELECT DISTINCT
fe.Patient_Dim_Key
from
star.dbo.F_ENCOUNTER fe
inner join Research.Shared.D_Location_Map 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)
where
lm.Palette_Code = 84
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
Corrected bug where diagnoses were not matching properly.
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,
fd.Patient_Dim_Key AS Patient_Dim_Key
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,
fp.Patient_Dim_Key AS Patient_Dim_Key
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,
fco.Patient_Dim_Key AS Patient_Dim_Key
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,
fo.Patient_Dim_Key AS Patient_Dim_Key
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 and results.Patient_Dim_Key = fe.Patient_Dim_Key
WHERE
pc.Cohort_Dim_Key = @Cohort_Dim_Key
AND 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 Accessing MHSU Consultation" Cohort
File Name: Patient_Events-22634_MHSU_Counsel_2017-06-14.csv
Product Name: Patient Events (Version 4)
Cohort Name: Patients Accessing MHSU Consultation
Cohort Size: 22,634 patients
Cohort Description
For Rebecca: needs a few cohort subsets based on type of counselling services.
Cohort Criteria
Have accessed Palette 84 services, and have been diagnosed with F32 or F33
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
Corrected bug where diagnoses were not matching properly.
Column List
Select Statement