Definition

The Diary data model represents recall/ reminder/ diary record content in EMIS source systems. It captures each diary entry’s identifiers, clinical coding, scheduling information, and linkage to consultations.

Constraints and Notes

• Soft deletion : When is_deleted is true, data fields will be nullified in upstream transformations and should be excluded from most analytical queries. Primary keys and foreign keys to patient data are preserved so deleted records and their relationships can still be identified. Foreign keys to coding tables are nullified as the coded information is no longer valid.
• 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;