Definition

Purpose

The issue_record model contains medication issue records for patients included in recruit studies (anon extract). It includes identifiers, dosing and cost metadata and scoping fields.

Information

The Issue Record model contains medication issue records for patients included in recruit studies. It captures the key identifiers and foreign key relationships linking issues to patients, medications, organisations, and user roles. All patient identifiers (patient_id, nhs_number, registration_id) are pseudonymised through the recruit included patients view.

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

• emis_issue_guid (PK): The unique identifier for the issue record
• pseudo_registration_guid (FK): Hashed registration identifier - links to patient
• pseudo_patient_id (FK): Hashed patient identifier - links to patient
• emis_medication_organisation_guid (FK): Links to organisation
• emis_registration_organisation_guid (FK): Links to organisation
• emis_drug_guid (FK): Links to drug_record
• emis_authorising_userinrole_guid (FK): Links to user_in_role
• emis_enteredby_userinrole_guid (FK): Links to user_in_role
• study_id (FK): Links to user_studies

Examples

Get all issue records for a study

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

Find issue records for an organisation

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

Get issue records with dosage and cost information

SELECT
  emis_issue_guid,
  patient_id,
  emis_code_id,
  dose,
  quantity,
  uom,
  estimated_nhs_cost
FROM hive.explorer_recruit_anon.medication_issuerecord
WHERE organisation = 'CDB-0001'
LIMIT 100;