Table: appointment
| Column name
|
Comments
|
Providing publisher
|
| id
|
Unique Id of the appointment (specific to the database)
|
|
| organization_id
|
Owning organisation (i.e. publisher)
|
EMIS
|
| patient_id
|
The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times
|
|
| person_id
|
Unique individual across all organisations
|
|
| practitioner_id
|
The clinician the activity is recorded against
|
EMIS
|
| schedule_id
|
The schedule the patient was put on to book multiple appointments. ID unique to the applied schedule
|
|
| start_date
|
The start date of the appointment
|
EMIS
|
| planned_duration
|
The time allocated for the appointment, not necessarily the actual duration always in minutes
|
EMIS
|
| actual_duration
|
Time between sent in and left always in minutes
|
EMIS
|
| appointment_status_concept_id
|
The status of the appointment e.g. arrived/sent in/left/DNA
|
EMIS
|
| patient_wait
|
How long the patient waited from being marked as arrived to being sent in
|
|
| patient_delay
|
How long the patient was delayed for
|
|
| date_time_sent_in
|
Date and time the patient was sent into the practitioner
|
EMIS
|
| date_time_left
|
Date and time the patient left the practitioner
|
EMIS
|
| source_id
|
Unique reference to the source of the appointment
|
EMIS
|
| cancelled_date
|
The date the appointment was cancelled
|
EMIS
|
Table: episode_of_care
| Column name
|
Comments
|
Providing publisher
|
| id
|
Unique Id of the episode of care
|
|
| organization_id
|
Owning organisation (i.e. publisher)
|
|
| patient_id
|
The organisations record for this person’s registration. Patients may have multiple records across clinical systems and may have registered at an organisation multiple times
|
|
| person_id
|
Unique individual across all organisations
|
|
| registration_type_concept_id
|
Reference to the registration type of the patient
|
|
| registration_status_concept_id
|
Reference to the registration status of the patient
|
|
| date_registered
|
The date the registration was started for this episode of care
|
|
| date_registered_end
|
The date the registration was ended for this episode of care
|
|
| usual_gp_practitioner_id
|
Reference to the usual GP for this episode of care
|
|
Table: patient
| Column name
|
Comments
|
Providing publisher
|
| id
|
Unique Id of the patient
|
|
| organization_id
|
Owning organisation (i.e. publisher)
|
EMIS, ADT, BHRUT
|
| person_id
|
Unique individual across all organisations
|
|
| title
|
The title of the patient
|
EMIS
|
| first_names
|
The first names of the patient
|
EMIS
|
| last_name
|
The last name of the patient
|
EMIS
|
| gender_concept_id
|
Reference to the gender of the patient
|
EMIS
|
| nhs_number
|
The NHS number of the patient
|
EMIS
|
| date_of_birth
|
The date of birth of the patient
|
EMIS
|
| date_of_death
|
The date of death of the patient
|
EMIS
|
| current_address_id
|
Reference to the current address of the patient
|
EMIS
|
| ethnic_code_concept_id
|
Reference to the ethnicity of the patient
|
EMIS
|
| registered_practice_organization_id
|
Reference to the organisation the patient is registered at
|
EMIS
|
| birth_year
|
|
EMIS
|
| birth_month
|
|
EMIS
|
| birth_week
|
|
EMIS
|
Table: patient_additional
| Column name
|
Comments
|
Providing publisher
|
| id
|
Unique Id of the patient
|
|
| property_id
|
IM reference (e.g. cause of death)
|
|
| value_id
|
IM reference (e.g. COVID)
|
|
| json_value
|
Where there is no mapped value_id, just raw JSON
|
|
| text_value
|
Where there is no mapped value_id or raw JSON, just a basic text value
|
|
