Definition

The issue record data model contains information about medication issues prescribed to patients within the EMIS Web system and sent to be dispensed. It captures details such as dosage, quantity, prescription type, costs, and the clinical codes associated with each issued medication. The master prescription sits in medication_drug_record. There can be multiple issuances of a drug record i.e. repeat prescriptions.

Constraints and Notes

• Soft deletion: When is_deleted is true, data fields will be nullified in upstream transformations and should be excluded from most analytical queries. Primary keys and foreign keys to patient data are preserved so deleted records and their relationships can still be identified. Foreign keys to coding tables are nullified as the coded information is no longer valid.
• dmd_product_code_id is resolved from code_id and may be NULL if no matching concept exists.

Examples

Active (non-cancelled) issues for a specific patient

SELECT
  issue_record_id,
  patient_id,
  consultation_id,
  effective_datetime,
  dosage,
  quantity,
  quantity_unit,
  prescription_type_description,
  issue_method_description,
  estimated_nhs_cost,
  organisation
FROM explorer_open_safely.medication_issue_record
WHERE
  patient_id = <patient_id>
  AND organisation = <organisation>
  AND NOT is_cancelled
  AND NOT is_deleted

Issues in the last 30 days

SELECT
  issue_record_id,
  patient_id,
  effective_datetime,
  dosage,
  quantity,
  quantity_unit,
  prescription_type_description,
  estimated_nhs_cost,
  organisation
FROM explorer_open_safely.medication_issue_record
WHERE
  effective_datetime >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
  AND NOT is_cancelled
  AND NOT is_deleted