Wiki Spaces


Get Help from Others

Q&A: Ask OpenMRS
Discussion: OpenMRS Talk
Real-Time: IRC Chat | Slack


Page tree

Versions Compared


  • This line was added.
  • This line was removed.
  • Formatting was changed.


-- create-visits-for-encounters.sql
-- This is a SQL script that will create visits for already-existing encounters
-- in the OpenMRS database. It creates one visit per patient-day; thus all
-- encounters that take place on the same day are assumed to be part of the
-- same visit.
-- Note that this means that if a real-world patient visit involves multiple
-- encounters that happen on different sides of midnight UTC, they will appear
-- as different visits.
-- Parameters:
--   @visit_type_id: the visit_type_id to use for the created visits. If you
--     need to use different visit_type_ids for different types of encounters,
--     do separate runs of this script varying this parameter and
--     encounter_type_exclusions as necessary.
--   @encounter_type_exclusions: a comma-separated string enumerating the 
--     encounter_type_ids which should be excluded from these visits.
-- `location_id` and `creator` are assumed to be the same across each
-- encounter group (i.e., per patient-day). If they are not, it is undefined
-- which of the different values will be used.

SET @visit_type_id = 1;
SET @encounter_type_exclusions = '2';  /* a comma-separated string like '1,2,3' */

-- Create the visits
       (patient_id,   visit_type_id,  date_started,   date_stopped,   location_id,   creator,   date_created, voided, uuid)
SELECT  e.patient_id, @visit_type_id, e.date_started, e.date_stopped, e.location_id, e.creator, now(),        0,      uuid()
    SELECT patient_id, 
           Subtime(Min(encounter_datetime), '00:05:00') AS date_started,  /* visit must start before first enc */
           Addtime(Max(encounter_datetime), '0100:0005:00') AS date_stopped,  /* visit tomust ensureend starttimeafter !=last endtimeenc */
    FROM   encounter 
    WHERE  FIND_IN_SET(encounter_type, @encounter_type_exclusions) = 0
    GROUP  BY patient_id, 
) AS e;

-- Add the visit_ids to their encounters
UPDATE encounter e 
       INNER JOIN visit v 
               ON e.patient_id = v.patient_id 
                  AND Date(e.encounter_datetime) = Date(v.date_started) 
SET    e.visit_id = v.visit_id
WHERE FIND_IN_SET(e.encounter_type, @encounter_type_exclusions) = 0;