Definition

Purpose

The drug_record model (anon extract) contains medication records for patients included in recruit studies. It provides identifiers, dosing details, flags and scoping for study participants.

Information

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

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

• emis_drug_guid (PK): The unique identifier for the medication 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_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 drug records for a study

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

Find active drug records for an organisation

SELECT *
FROM hive.explorer_recruit_anon.medication_drugrecord
WHERE organisation = 'CDB-0001'
  AND drug_active_flag = TRUE
LIMIT 100;

Get drug records with dosage information

SELECT
  emis_drug_guid,
  patient_id,
  emis_code_id,
  dose,
  quantity,
  uom,
  emis_prescription_type
FROM hive.explorer_recruit_anon.medication_drugrecord
WHERE organisation = 'CDB-0001'
LIMIT 100;