README.md

Link to CommonDataModel

• SPECIMEN

Remark

The proper etl.sql file is actually in MEASUREMENT/etl.sql

Principle

A specimen is linked to a measurement by the fact_relationship table and vice-versa. (relationship_id "has specimen"). Since MIMIC does not provide that level of information, they are populated with most appropriate information in order to keep OMOP philosophy.

• for chartevents provide two timestamp : charttime and storetime
  • charttime is the prelevement timestamp and populates specimen_datetime
  • storetime populates measurement_datetime
• for labevents and microbiologyevents, there is only one timestamp with populates specimen and measurement timestamp

Source Tables (mimic)

charteevents

• laboratory chartevents data generates a specimen for each result

labevents

• laboratory data generates a specimen for each result

microbiologyevents

• a specimen is generated for each single specimen id

Mapping used

labs_to_concept

• it maps d_labitems.fluid to specimen_concept_id

microbiology_to_concept

• it maps microbiologyevents.spec_type_desc to specimen_concept_id

Examples

Distinct specimen_type_concept_id

SELECT distinct specimen_type_concept_id
FROM specimen;

Distinct specimen_concept_id

SELECT distinct specimen_concept_id
FROM specimen;