Page History
...
File parameter specification entries
Variables for File name entries
Entry into QT_BREAKDOWN_PATH
Entry into QT_RESULT_TTPE
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
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
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
Procedures
Patient_Mapping
...
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
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
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>
Sample Letter generated for Data Manager
