i2b2 Developer's Forum
Space shortcuts
Space Tools

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migrated to Confluence 4.0

...


For example, this query gives all patients that had a visit before they were three years old:


(SQL Server)


Declare @age_of_patient_that_visit_should_proceed integer
set @age_of_patient_that_visit_should_proceed = 3
select encounter_num from visit_dimension
where start_date <
((select birth_date from patient_dimension
where patient_num = visit_dimension.patient_num)
+ (365.25*@age_of_patient_that_visit_should_proceed))


For example, this query gives those patients that had a visit when they were 40-45 years old:


(SQL Server)


Declare @age_of_patient_that_visit_should_start_on integer
Declare @age_of_patient_that_visit_should_end_on integer
set @age_of_patient_that_visit_should_start_on = 40
set @age_of_patient_that_visit_should_end_on = 45
select encounter_num from visit_dimension
where start_date BETWEEN
((select birth_date from patient_dimension
where patient_num = visit_dimension.patient_num)
+ (365.25 * (@age_of_patient_that_visit_should_start_on)))
AND
((select birth_date from patient_dimension
where patient_num = visit_dimension.patient_num)
+ (365.25 * (@age_of_patient_that_visit_should_end_on+1)))

Both of these queries can result from routine metadata definitions. The items in the ontology tables would look like:
The ontology cell provides instructions on how the data repository cell should perform its query for any given term or set of terms. Each row in an ontology metadata table(s) represents a term or item, such as a diagnosis like "diabetes", or a laboratory test like "white blood cell count". In order to find these items in the data repository, the data repository cell needs to know which table to find them in and how to identify them. The information contained in the metadata tables is utilized to build a metadata SELECT SQL statement as follows:


Wiki Markup\\ select \ [c_facttablecolumnname\] from \ [c_tablename\] where \ [c_columnname\] \ [c_operator\] \ [c_dimcode\] \\
In the ontology table, the items that define encounters as shown in the SQL queries above would look like: \\
Encounters for all patients that had a visit before they were three years old:


(SQL Server)


select encounter_num from visit_dimension
where start_date <
((select birth_date from patient_dimension
where patient_num = visit_dimension.patient_num)
+ (365.25 * 3))


(Ontology table entries)


Wiki Markup\\ \ [c_name\] = patients that had a visit before they were three years old \
[c_fullpath\] = \\
encounter detail\ age at visit\ < 3 y/o \
[c_facttablecolumnname\] = encounter_num \
[c_tablename\] = visit_dimension \
[c_columnname\] = start_date \
[c_operator\] = < \
[c_dimcode\] =
((select birth_date from patient_dimension
where patient_num = visit_dimension.patient_num)
+ (365.25 * 3)) \\ \\ Encounters for all patients that had a visit when they were

Encounters for all patients that had a visit when they were 40-45 years old:


(SQL Server)


select encounter_num from visit_dimension
where start_date BETWEEN
((select birth_date from patient_dimension
where patient_num = visit_dimension.patient_num)
+ (365.25 * 40))
AND
((select birth_date from patient_dimension
where patient_num = visit_dimension.patient_num)
+ (365.25 * 45))


(Ontology table entries)


Wiki Markup\\ \ [c_name\] = patients that had a visit when they were 40-45 years old \
[c_fullpath\] = \\ &nbsp;encounter =
 encounter detail\ age at visit\ 40 -- 45 y/o \
[c_facttablecolumnname\] = encounter_num \
[c_tablename\] = visit_dimension \
[c_columnname\] = start_date \
[c_operator\] = BETWEEN \
[c_dimcode\] =
((select birth_date from patient_dimension
where patient_num = visit_dimension.patient_num)
+ (365.25 * 40))
AND
((select birth_date from patient_dimension
where patient_num = visit_dimension.patient_num)
+ (365.25 * 45))

© Shawn Murphy 2010