Closed mpbismar closed 9 years ago
Preferred:
SELECT patient_num, concept_cd, count(*) AS count
FROM (
SELECT patient_num, substring(concept_cd from '.*:.{3}') AS concept_cd
FROM i2b2demodata.observation_fact
WHERE concept_cd IN (
SELECT concept_cd
FROM i2b2demodata.concept_dimension
WHERE concept_cd SIMILAR TO '(ATC|ICD):%')
AND (start_date >= '2007-01-01T00:00:00' AND start_date <= '2008-01-01T00:00:00')
AND (FALSE
OR patient_num IN (
SELECT patient_num
FROM i2b2demodata.qt_patient_set_collection
WHERE result_instance_id = 7)
)) observations
GROUP BY patient_num, concept_cd
Compromise:
SELECT patient_num, concept_cd, count(*) AS count
FROM (
SELECT patient_num, substring(concept_cd from 1 for 7) AS concept_cd
FROM i2b2demodata.observation_fact
WHERE concept_cd IN (
SELECT concept_cd
FROM i2b2demodata.concept_dimension
WHERE (concept_cd LIKE 'ATC:%'
OR concept_cd LIKE 'ICD:%'))
AND (start_date >= '2007-01-01T00:00:00' AND start_date <= '2008-01-01T00:00:00')
AND (FALSE
OR patient_num IN (
SELECT patient_num
FROM i2b2demodata.qt_patient_set_collection
WHERE result_instance_id = 7)
)) observations
GROUP BY patient_num, concept_cd