Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
1.3.00
-
None
-
Query Tool
-
Queries
Description
We want to find the overlap for a particular concept. For example, the patients that have answered both "no" and "yes" to a particular question, obviously on different dates.
For a particular client they have a concept_cd of "HDID:5474" with description "alcohol abuse".
There are 2 text values "no" and "yes" for this concept.
There are 11446 patients total with this concept.
There are 10257 patients who answered "no".
There are 1228 patients who answered "yes".
There must be 39 to 1228 patients that have both answers, but they cannot be identified in I2B2.
If you choose all the patients that answered "no" in group 1 and all the patients that answered "yes" in group 2 you always get the number of patients in group 2, not the patients in both. In this case you get 1228 patients (those who answered yes).
If you reverse the query, you get 10257 patients (those who answered no) which is impossible.
Here is the generated SQL, which is incorrect:
INSERT INTO dbo.#global_temp_table (patient_num, panel_count) SELECT patient_num, 1
FROM ( SELECT /*+ index(observation_fact fact_cnpt_pat_enct_idx) */patient_num FROM dbo.observation_fact
WITH(NOLOCK) WHERE (((concept_cd IN (select concept_cd from dbo.concept_dimension c WITH(NOLOCK)
where concept_path LIKE '\HDID\GE-A\alcohol abuse (HDID:5474)%')) AND valtype_cd = 'T' and tval_char IN ('yes')))
group by patient_num having count >=1) t <*>
UPDATE dbo.#global_temp_table SET panel_count = 2 WHERE EXISTS ( SELECT 1
FROM ( SELECT /*+ index(observation_fact fact_cnpt_pat_enct_idx) */patient_num FROM dbo.observation_fact
WITH(NOLOCK) WHERE (((concept_cd IN (select concept_cd from dbo.concept_dimension c WITH(NOLOCK)
where concept_path LIKE '\HDID\GE-A\alcohol abuse (HDID:5474)%')) AND valtype_cd = 'T' and tval_char IN ('yes')))
group by patient_num having count >=1) v WHERE dbo.#global_temp_table.patient_num = v.patient_num ) <*>
INSERT INTO dbo.#dx (patient_num) SELECT * FROM ( SELECT DISTINCT t.patient_num
FROM dbo.#global_temp_table t WHERE panel_count = 2 ) q
For a particular client they have a concept_cd of "HDID:5474" with description "alcohol abuse".
There are 2 text values "no" and "yes" for this concept.
There are 11446 patients total with this concept.
There are 10257 patients who answered "no".
There are 1228 patients who answered "yes".
There must be 39 to 1228 patients that have both answers, but they cannot be identified in I2B2.
If you choose all the patients that answered "no" in group 1 and all the patients that answered "yes" in group 2 you always get the number of patients in group 2, not the patients in both. In this case you get 1228 patients (those who answered yes).
If you reverse the query, you get 10257 patients (those who answered no) which is impossible.
Here is the generated SQL, which is incorrect:
INSERT INTO dbo.#global_temp_table (patient_num, panel_count) SELECT patient_num, 1
FROM ( SELECT /*+ index(observation_fact fact_cnpt_pat_enct_idx) */patient_num FROM dbo.observation_fact
WITH(NOLOCK) WHERE (((concept_cd IN (select concept_cd from dbo.concept_dimension c WITH(NOLOCK)
where concept_path LIKE '\HDID\GE-A\alcohol abuse (HDID:5474)%')) AND valtype_cd = 'T' and tval_char IN ('yes')))
group by patient_num having count >=1) t <*>
UPDATE dbo.#global_temp_table SET panel_count = 2 WHERE EXISTS ( SELECT 1
FROM ( SELECT /*+ index(observation_fact fact_cnpt_pat_enct_idx) */patient_num FROM dbo.observation_fact
WITH(NOLOCK) WHERE (((concept_cd IN (select concept_cd from dbo.concept_dimension c WITH(NOLOCK)
where concept_path LIKE '\HDID\GE-A\alcohol abuse (HDID:5474)%')) AND valtype_cd = 'T' and tval_char IN ('yes')))
group by patient_num having count >=1) v WHERE dbo.#global_temp_table.patient_num = v.patient_num ) <*>
INSERT INTO dbo.#dx (patient_num) SELECT * FROM ( SELECT DISTINCT t.patient_num
FROM dbo.#global_temp_table t WHERE panel_count = 2 ) q