Definition

The Diary data model describes diary entries recorded in EMIS Web and made available for OpenSafely analysis. It captures each diary entry’s identifiers, clinical coding, scheduling information, and linkage to consultations.

Constraints and Notes

• Primary key usage: diary_id is part of a composite key with organisation and is not globally unique.
• SNOMED enrichment: snomed_concept_id is resolved from code_id and may be NULL if no matching concept exists.

Examples

Filter active diary entries

Simple query to return diary entries that are currently active and not deleted:

SELECT
  diary_id,
  patient_id,
  consultation_id,
  snomed_concept_id,
  effective_datetime,
  is_active,
  is_complete
FROM explorer_open_safely.diary
WHERE is_active
  AND NOT is_deleted;

Find diary entries with a specific SNOMED code

Query to return diary entries for a given SNOMED concept:

SELECT
  diary_id,
  patient_id,
  snomed_concept_id,
  availability_datetime,
  effective_datetime,
  duration_term
FROM explorer_open_safely.diary
WHERE snomed_concept_id = 1234567890;