Definition

Purpose

The diary_entry model (anon extract) contains recall/diary records scoped to included patients for recruit studies. It captures identifiers, codes, timestamps and status flags.

Information

The Diary Entry model contains recall/diary records for patients included in recruit studies. It captures the key identifiers and foreign key relationships linking diary entries to patients, consultations, codes, organisations, and user roles. All patient identifiers (patient_id, nhs_number, registration_id) are pseudonymised through the recruit included patients view.

Each diary entry is uniquely identified within a specific healthcare organisation.

• emis_diary_guid (PK): The unique identifier for the diary entry record
• pseudo_registration_guid (FK): Hashed registration identifier - links to patient
• pseudo_patient_id (FK): Hashed patient identifier - links to patient
• emis_registration_organisation_guid (FK): Links to organisation
• emis_authorising_userinrole_guid (FK): Links to user_in_role
• emis_enteredby_userinrole_guid (FK): Links to user_in_role
• emis_code_id (FK): Links to clinical_code
• emis_consultation_guid (FK): Links to consultation
• study_id (FK): Links to user_studies

Examples

Get all diary entries for a study

SELECT *
FROM hive.explorer_recruit_anon.recall
WHERE study_id = 'STUDY001'
LIMIT 100;

Find active diary entries

SELECT *
FROM hive.explorer_recruit_anon.recall
WHERE is_active_flag = TRUE
LIMIT 100;

Get diary entries for an organisation

SELECT
  emis_diary_guid,
  patient_id,
  emis_code_id,
  effective_date,
  is_complete_flag
FROM hive.explorer_recruit_anon.recall
WHERE organisation = 'CDB-0001'
LIMIT 100;