...

File parameter specification entries

Image Modified

Image Modified

Variables for File name entries 

 Image Modified

Image Modified

Entry into QT_BREAKDOWN_PATH

Image Added

Entry into QT_RESULT_TTPE

Image Added

Example SQL statement entries

Demographics

SELECT to_char(a.PATIENT_NUM) as "I2B2_PATIENT_NUMBER"

,a.BIRTH_DATE as "BIRTH_DATE"

, a.DEATH_DATE as "DEATH_DATE"

,a.SEX_CD as "GENDER"

, a.AGE_IN_YEARS_NUM as "AGE_IN_YEARS"

,a.LANGUAGE_CD as "PRIMARY_SPOKEN_LANGUAGE"

,a.RACE_CD as "RACE"

,a.MARITAL_STATUS_CD as "MARTIAL_STATUS"

,a.RELIGION_CD as "RELIGION"

,a.ZIP_CD as "ZIP_CODE"

,a.STATECITYZIP_PATH as "STATE_CITY_ZIP"

,a.INCOME_CD as "INCOME"

,a.VITAL_STATUS_CD as "VITAL_STATUS"

FROM patient_dimension a, {{{DX}}} c

where a.patient_num = c.patient_num

Image Added

Diagnosis

SELECT DISTINCT to_char(a.PATIENT_NUM) as "I2B2_PATIENT_NUMBER"

,m.patient_ide as ”PATIENT_ID"

,a.start_date as "START_DATE"

,a.start_date as "END_DATE"

,b.name_char as "DIAGNOSIS_NAME"

,b.concept_cd as "DIAGNOSIS_CODE"

,a.modifier_cd as "MODIFIER"

,a.location_cd as "FACILITY"

,case v.inout_cd when ''O'' then ''Outpatient'' when ''I'' then ''Inpatient'' else ''Unknown'' end as "ENCOUNTER_TYPE"

,p.name_char as "PROVIDER"

,a.encounter_num as "ENCOUNTER_NUMBER"

FROM observation_fact a

INNER JOIN concept_dimension b on a.concept_cd = b.concept_cd and b.concept_path like ''\i2b2\Diagnoses\%''

JOIN {{{DX}}} c on a.patient_num = c.patient_num

LEFT OUTER JOIN provider_dimension p on a.provider_id = p.provider_id

JOIN visit_dimension v on a.encounter_num = v.encounter_num and a.patient_num = v.patient_num

JOIN patient_mapping m on a.patient_num = m.patient_num

Image Added

Labs

SELECT DISTINCT to_char(a.PATIENT_NUM) as "I2B2_PATIENT_NUMBER"

,m.patient_ide as ”PATIENT_ID"

,a.start_date as "START_DATE"

,a.start_date as "END_DATE"

,b.name_char as "LAB_NAME"

,b.concept_cd as "LAB_CODE"

,case when a.valtype_cd = ''T'' then tval_char else to_char(nval_num) end as "LAB_RESULTS"

,a.modifier_cd as "MODIFIER"

,a.location_cd as "FACILITY"

,case v.inout_cd when ''O'' then ''Outpatient'' when ''I'' then ''Inpatient'' else ''Unknown'' end as "ENCOUNTER_TYPE"

,p.name_char as "PROVIDER"

,a.encounter_num as "ENCOUNTER_NUMBER"

FROM observation_fact a

INNER JOIN concept_dimension b on a.concept_cd = b.concept_cd and b.concept_path like ''\i2b2\Labtests\%''

JOIN {{{DX}}} c on a.patient_num = c.patient_num

LEFT OUTER JOIN provider_dimension p on a.provider_id = p.provider_id

JOIN visit_dimension v on a.encounter_num = v.encounter_num and a.patient_num = v.patient_num

JOIN patient_mapping m on a.patient_num = m.patient_num

Image Added

Procedures

SELECT DISTINCT to_char(a.PATIENT_NUM) as "I2B2_PATIENT_NUMBER"

,m.patient_ide as ”PATIENT_ID"

,a.start_date as "START_DATE"

,a.start_date as "END_DATE"

,b.name_char as "PROCEDURE_NAME"

,b.concept_cd as "PROCEDURE_CODE"

,a.quantity_num as "QUANTITY"

,a.modifier_cd as "MODIFIER"

,a.location_cd as "FACILITY"

,case v.inout_cd when ''O'' then ''Outpatient'' when ''I'' then ''Inpatient'' else ''Unknown'' end as "ENCOUNTER_TYPE"

,p.name_char as "PROVIDER"

,a.encounter_num as "ENCOUNTER_NUMBER"

FROM observation_fact a

INNER JOIN concept_dimension b on a.concept_cd = b.concept_cd and b.concept_path like ''\i2b2\Procedures\%''

JOIN {{{DX}}} c on a.patient_num = c.patient_num

LEFT OUTER JOIN provider_dimension p on a.provider_id = p.provider_id

JOIN visit_dimension v on a.encounter_num = v.encounter_num and a.patient_num = v.patient_num

JOIN patient_mapping m on a.patient_num = m.patient_num

Image Added

Medications

SELECT DISTINCT to_char(a.PATIENT_NUM) as "I2B2_PATIENT_NUMBER"

,m.patient_ide as ”PATIENT_ID"

,a.start_date as "START_DATE"

,a.start_date as "END_DATE"

,b.name_char as "MEDICATION_NAME"

,b.concept_cd as "NDC_CODE"

,a.units_cd as "UNIT"

,a.quantity_num as "DOSE_QUANTITY"

,a.modifier_cd as "MODIFIER"

,a.location_cd as "FACILITY"

,case v.inout_cd when ''O'' then ''Outpatient'' when ''I'' then ''Inpatient'' else ''Unknown'' end as "ENCOUNTER_TYPE"

,p.name_char as "PROVIDER"

,a.encounter_num as "ENCOUNTER_NUMBER"

FROM observation_fact a

INNER JOIN concept_dimension b on a.concept_cd = b.concept_cd and b.concept_path like ''\i2b2\Medications\%''

JOIN {{{DX}}} c on a.patient_num = c.patient_num

LEFT OUTER JOIN provider_dimension p on a.provider_id = p.provider_id

JOIN visit_dimension v on a.encounter_num = v.encounter_num and a.patient_num = v.patient_num

JOIN patient_mapping m on a.patient_num = m.patient_num

Image Added

Patient_Mapping

SELECT DISTINCT to_char(a.PATIENT_NUM) as "I2B2_PATIENT_NUMBER"

,a.PATIENT_IDE_SOURCE as "PATIENT_SOURCE"

,a.PATIENT_IDE as "PATIENT_IDE"

FROM patient_mapping a, {{{DX}}} c

where a.patient_num = c.patient_num</Query>

Image Added 

Sample  Sample Letter generated for Data Manager

Image Added