Definition

Purpose

The immunisation model exposes immunisation (vaccination) observations for patients included in recruit studies (anon extract). It captures identifiers, administration metadata and coding mappings.

Information

The Immunisation model exposes immunisation (vaccination) observations for patients included in recruit studies. It captures the key identifiers and foreign key relationships linking vaccinations to patients, consultations, codes, organisations, and other entities. All patient identifiers (patient_id, nhs_number, registration_id) are pseudonymised through the recruit included patients view.

Each immunisation record is uniquely identified within a specific healthcare organisation.

• emis_observation_guid (PK): The unique identifier for the immunisation 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_consultation_guid (FK): Links to consultation
• emis_code_id (FK): Links to clinical_code
• study_id (FK): Links to user_studies

Examples

Get all immunisations for a study

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

Find immunisations for an organisation

SELECT *
FROM hive.explorer_recruit_anon.immunisation
WHERE organisation = 'CDB-0001'
LIMIT 100;

Get immunisations with SNOMED mapping and batch details

SELECT
  emis_observation_guid,
  patient_id,
  emis_code_id,
  snomed_concept_id,
  batch_number,
  expiry_date
FROM hive.explorer_recruit_anon.immunisation
WHERE snomed_concept_id IS NOT NULL
  AND organisation = 'CDB-0001'
LIMIT 100;