Definition

Purpose

The location model extracts organisational location metadata. It establishes the site/clinic hierarchy and provides location codes for appointment scheduling and service delivery tracking.

Information

This master table contains location hierarchy data for healthcare facilities served by participating organisations. Locations can be grouped under parent locations to represent multi-site practices or departments within larger facilities.

• emis_location_guid (PK): The unique identifier for the location
• emis_parent_location_guid (FK): Links to the parent location in the hierarchy

Examples

Get all locations for an organisation

SELECT *
FROM hive.explorer_recruit.location
WHERE organisation = 'CDB-0001'
LIMIT 100;

Find active locations (no close date)

SELECT
  emis_location_guid,
  emis_location_id,
  location_type_description,
  open_date
FROM hive.explorer_recruit.location
WHERE close_date IS NULL
LIMIT 100;

Get locations with their parent location

SELECT
  l.emis_location_guid,
  l.location_type_description,
  l.emis_parent_location_guid,
  parent.location_type_description AS parent_type
FROM hive.explorer_recruit.location l
LEFT JOIN hive.explorer_recruit.location parent
  ON l.emis_parent_location_guid = parent.emis_location_guid
  AND l.organisation = parent.organisation
LIMIT 100;