Notes from years away from admissions

[sarahfpoole]

Prerequisites

• Put an X between the brackets on this line if you have done all of the following:
  • Checked the online documentation: https://mimic.mit.edu/
  • Checked that your issue isn't already addressed: https://github.com/MIT-LCP/mimic-code/issues?utf8=%E2%9C%93&q=

Description

Description of the issue, including:

• what you have tried
• references to similar issues
• queries demonstrating your question (if applicable)

I have been working to associate notes with specific admissions. I expect to see some notes available days / weeks outside of the admissions recorded in the 'admissions' table, but I actually see 1000s of notes that are at least 100 days from any recorded admission. I have done some spot checking on about 10 of the rows identified using the below query, and have confirmed that the admissions recorded are far from the CHARTTIME recorded in the note. Could you help me to understand these notes? I see a mix of nursing notes, physician notes, radiology notes, and a handful of others.

WITH
  notes AS (
  SELECT
    ROW_ID,
    SUBJECT_ID,
    CHARTTIME,
    TEXT,
    CATEGORY,
    DESCRIPTION
  FROM
    `physionet-data.mimiciii_notes.noteevents`),
  admissions AS (
  SELECT
    SUBJECT_ID,
    ADMITTIME,
    DISCHTIME,
    EDREGTIME,
    EDOUTTIME,
    ADMISSION_TYPE,
    ADMISSION_LOCATION,
    DISCHARGE_LOCATION
  FROM
    `physionet-data.mimiciii_clinical.admissions`),
  joined AS (
  SELECT
    *
  FROM
    notes
  LEFT JOIN (
    SELECT
      *
    FROM
      admissions)
  USING
    (SUBJECT_ID)),
  inpatient_label AS (
  SELECT
    *,
    CHARTTIME >= ADMITTIME
    AND CHARTTIME <= DISCHTIME AS inpatient_note,
    ABS(DATETIME_DIFF(CHARTTIME, DISCHTIME, DAY)) AS days_from_discharge,
    ABS(DATETIME_DIFF(CHARTTIME, ADMITTIME, DAY)) AS days_from_admit
  FROM
    joined),
  summary AS (
  SELECT
    SUBJECT_ID,
    ROW_ID,
    CHARTTIME,
    CATEGORY,
    DESCRIPTION,
    MAX(inpatient_note) AS any_inpatient_encounter,
    MIN(days_from_discharge) AS min_time_to_discharge,
    MIN(days_from_admit) AS min_time_to_admit
  FROM
    inpatient_label
  WHERE
    CHARTTIME IS NOT NULL
  GROUP BY
    1,
    2,
    3,
    4,
    5)
SELECT
  *
FROM
  summary
WHERE
  NOT any_inpatient_encounter
  AND min_time_to_discharge > 100
  AND min_time_to_admit > 100
ORDER BY
  min_time_to_discharge DESC