Details
-
Bug
-
Status: New
-
Minor
-
Resolution: Unresolved
-
None
-
None
-
None
-
None
Description
I have found that constructing a certain type of temporal query with the advanced query builder with more than one temporal constraint fails to apply the constraints to the underlying SQL query. This was the test query that I built at my institution.
Population in which events occur: Patients with colorectal cancer (ICD10 Diagnosis of C18, C19, C20, C21)
Event 1: fluorouracil
Event 2: Platelet count (777-3) < 75
Temporal Constraint 1: The start of any event 2 occurs before the last ever event 1 by at least one day
Temporal Constraint 2: The start of any event 2 occurs after the first ever event 1 by at least one day
This is what was stored in QT_QUERY_MASTER for a query with one temporal constraint:
update #master_global_temp_table set level_no = 1 from #master_global_temp_table t where exists (select 1 from #master_global_temp_table m where m.master_id = '18moxqdnmx2k6d1fkudxw57pi_S0' and m.patient_num = t.patient_num and m.level_no = 1 ) and exists (select 1 from (select m.patient_num , max(m.temporal_start_date) temporal_start_date from #master_global_temp_table m where m.master_id = '18moxqdnmx2k6d1fkudxw57pi_S0' group by m.patient_num ) m where m.patient_num = t.patient_num and t.temporal_start_date < m.temporal_start_date and m.temporal_start_date <= DATEADD(DAY, (1), t.temporal_start_date)) and t.level_no = 0 and t.master_id = '18moxqdnmx2k6d1fkudxw57pi_S1'
What I got for two temporal constraints:
update #master_global_temp_table set level_no = 1 from #master_global_temp_table t where exists (select 1 from #master_global_temp_table m where m.master_id = 'bj983lrmfp7vkkeiqro34gchj_S0' and m.patient_num = t.patient_num and m.level_no = 1 ) and t.level_no = 0 and t.master_id = 'bj983lrmfp7vkkeiqro34gchj_S1'
Population in which events occur: Patients with colorectal cancer (ICD10 Diagnosis of C18, C19, C20, C21)
Event 1: fluorouracil
Event 2: Platelet count (777-3) < 75
Temporal Constraint 1: The start of any event 2 occurs before the last ever event 1 by at least one day
Temporal Constraint 2: The start of any event 2 occurs after the first ever event 1 by at least one day
This is what was stored in QT_QUERY_MASTER for a query with one temporal constraint:
update #master_global_temp_table set level_no = 1 from #master_global_temp_table t where exists (select 1 from #master_global_temp_table m where m.master_id = '18moxqdnmx2k6d1fkudxw57pi_S0' and m.patient_num = t.patient_num and m.level_no = 1 ) and exists (select 1 from (select m.patient_num , max(m.temporal_start_date) temporal_start_date from #master_global_temp_table m where m.master_id = '18moxqdnmx2k6d1fkudxw57pi_S0' group by m.patient_num ) m where m.patient_num = t.patient_num and t.temporal_start_date < m.temporal_start_date and m.temporal_start_date <= DATEADD(DAY, (1), t.temporal_start_date)) and t.level_no = 0 and t.master_id = '18moxqdnmx2k6d1fkudxw57pi_S1'
What I got for two temporal constraints:
update #master_global_temp_table set level_no = 1 from #master_global_temp_table t where exists (select 1 from #master_global_temp_table m where m.master_id = 'bj983lrmfp7vkkeiqro34gchj_S0' and m.patient_num = t.patient_num and m.level_no = 1 ) and t.level_no = 0 and t.master_id = 'bj983lrmfp7vkkeiqro34gchj_S1'