Active Orders (Design Page)

Background

One of the most important aspects for an order entry system is to keep track of and be able to return the list of active orders.

Design

Given the OpenMRS model for orders, an order is active on a given TARGET DATETIME if:

orders.date_created ≤ TARGET DATETIME
orders.date_stopped ≥ TARGET DATETIME
orders.auto_expire_date ≤ TARGET DATETIME

While we may (in the future) consider creating a separate table to track active orders per patient, we can determine active orders at this time using this definition on the data within the orders table.

SELECT * FROM orders WHERE
  orders.date_created <= $targetDateTime AND
  orders.date_stopped >= $targetDateTime AND
  (order.auto_expire_date IS NOT NULL AND orders.auto_expire_date >= $targetDateTime) AND
  NOT voided;

In the common use case where we are looking for currently active orders (i.e., TARGET DATETIME is now), we can simplify this to:

SELECT * FROM orders WHERE
  NOT orders.stopped AND
  (orders.auto_expire_date IS NOT NULL AND orders.auto_expire_date >= NOW()) AND
  NOT voided;