MIMIC II V2.6 - MView-MIMIC2V26.DEMOGRAPHIC

Main Tables Materialized Views Indexes Constraints DDL scrips

Description Columns Query Constraints Triggers

MIMIC2V26.DEMOGRAPHIC_DETAIL

Container	Updatable
DEMOGRAPHIC_DETAIL	N

DDL script

Columns

Name	Type	Nullable	Insertable	Updatable	Deletable	Comment
SUBJECT_ID	NUMBER(7)	N	YES	YES	YES	The subject identifier.
HADM_ID	NUMBER(7)	N	YES	YES	YES	The hospital admission.
MARITAL_STATUS_ITEMID	NUMBER(7)	Y	YES	YES	YES	The itemid for the marital-status component of the demographic.
MARITAL_STATUS_DESCR	VARCHAR2(50)	Y	YES	YES	YES	The description of the marital-status component of the demographic.
ETHNICITY_ITEMID	NUMBER(7)	Y	YES	YES	YES	The itemid for the ethnicity component of the demographic.
ETHNICITY_DESCR	VARCHAR2(50)	Y	YES	YES	YES	The description of the ethnicity component of the demographic.
OVERALL_PAYOR_GROUP_ITEMID	NUMBER(7)	Y	YES	YES	YES	The itemid for the overall-payor-group component of the demographic.
OVERALL_PAYOR_GROUP_DESCR	VARCHAR2(50)	Y	YES	YES	YES	The description of the overall-payor-group component of the demographic.
RELIGION_ITEMID	NUMBER(7)	Y	YES	YES	YES	The itemid for the religion component of the demographic.
RELIGION_DESCR	VARCHAR2(50)	Y	YES	YES	YES	The description of the religion component of the demographic.
ADMISSION_TYPE_ITEMID	NUMBER(7)	Y	YES	YES	YES	The itemid for the admission type of the demographic.
ADMISSION_TYPE_DESCR	VARCHAR2(50)	Y	YES	YES	YES	The description of the admission type of the demographic.
ADMISSION_SOURCE_ITEMID	NUMBER(7)	Y	YES	YES	YES	The itemid for the admission source of the demographic.
ADMISSION_SOURCE_DESCR	VARCHAR2(50)	Y	YES	YES	YES	The description of the admission source of the demographic.

Query:

Note: Syntax highlighting off

select distinct
  subject_id,
  hadm_id,
  cast(max(marital_status_itemid) over (partition by subject_id, hadm_id) as number(7.0)) as marital_status_itemid,
  max(marital_status_descr) over (partition by subject_id, hadm_id) as marital_status_descr,
  cast(max(ethnicity_itemid) over (partition by subject_id, hadm_id) as number(7.0)) as ethnicity_itemid,
  max(ethnicity_descr) over (partition by subject_id, hadm_id) as ethnicity_descr,
  cast(max(overall_payor_group_itemid) over (partition by subject_id, hadm_id) as number(7.0)) as overall_payor_group_itemid,
  max(overall_payor_group_descr) over (partition by subject_id, hadm_id) as overall_payor_group_descr,
  cast(max(religion_itemid) over (partition by subject_id, hadm_id) as number(7.0)) as religion_itemid,
  max(religion_descr) over (partition by subject_id, hadm_id) as religion_descr,
  cast(max(admission_type_itemid) over (partition by subject_id, hadm_id) as number(7.0)) as admission_type_itemid,
  max(admission_type_descr) over (partition by subject_id, hadm_id) as admission_type_descr,
  cast(max(admission_source_itemid) over (partition by subject_id, hadm_id) as number(7.0)) as admission_source_itemid,
  max(admission_source_descr) over (partition by subject_id, hadm_id) as admission_source_descr
from
  (select
    de.subject_id,
    de.hadm_id,
    (case when di.category = 'MARITAL STATUS' then di.itemid else null end) as marital_status_itemid,
    (case when di.category = 'MARITAL STATUS' then di.label else null end) as marital_status_descr,
    (case when di.category = 'ETHNICITY' then di.itemid else null end) as ethnicity_itemid,
    (case when di.category = 'ETHNICITY' then di.label else null end) as ethnicity_descr,
    (case when di.category = 'OVERALL PAYOR GROUP' then di.itemid else null end) as overall_payor_group_itemid,
    (case when di.category = 'OVERALL PAYOR GROUP' then di.label else null end) as overall_payor_group_descr,
    (case when di.category = 'RELIGION' then di.itemid else null end) as religion_itemid,
    (case when di.category = 'RELIGION' then di.label else null end) as religion_descr,
    (case when di.category = 'ADMISSION TYPE' then di.itemid else null end) as admission_type_itemid,
    (case when di.category = 'ADMISSION TYPE' then di.label else null end) as admission_type_descr,
    (case when di.category = 'ADMISSION SOURCE' then di.itemid else null end) as admission_source_itemid,
    (case when di.category = 'ADMISSION SOURCE' then di.label else null end) as admission_source_descr
  from
    mimic2v26.demographicevents de,
    mimic2v26.d_demographicitems di
  where
    de.itemid = di.itemid)