Definition

The Consultation Section data model describes the individual sections within a consultation recorded in EMIS Web and made available for OpenSafely analysis. It captures each section’s identifiers, original heading text, categorisation, linkage back to the parent consultation and to clinical events, as well as core timing and completion information.

Constraints and Notes

• Primary key usage: consultation_section_id is generated via the generate_pseudo_id macro from the section identifier and organisation to form a single primary key column.
• Event linkage: event_id is a pseudo-identifier that links a section back to the underlying event in the observation, recall, or issue_record models.
• Consultation linkage: consultation_id is a pseudo-identifier that links a section back to the parent consultation in the consultation model.
• Patient identifier hashing: emis_patient_id is a hashed identifier derived from patient ID + organisation.

Examples

List completed consultation sections

Simple query to return completed consultation sections with their headings and parent consultation identifiers:

SELECT
  consultation_section_id,
  consultation_id,
  emis_patient_id,
  consultation_section_original_heading,
  consultation_section_category_code,
  recorded_date,
  effective_date,
  complete
FROM explorer_open_safely.consultation_section
WHERE complete;