SQL select most recent timestamp before order

I have two tables: one with the order time of a specific procedure for a group of patients, with some patients having the same type of order (with a different order ID) more than once per stay:PATIENT_ORDERS:

and the other with the arrival time in hospital departments for the same group of patients: PATIENT_TRANSFERS:

I want to join the tables to show the time of the most recent transfer into a department after the OR, but before the time of the procedure order.
For patients that have more than one procedure order per stay, I want to show the most recent transfer into a department before the second procedure order time.

EXPECTED OUTPUT:

I am not sure how to approach this problem. I know that I need max(adt_time)<order_time, but I am not sure what the syntax would be, particularly for those patients with more than one order_time per visit.

Using standard sql.

Thank you!

>Solution :

You can use a CORRELATED SUBQUERY to accomplish this. In this example, I return the MAX(ADT_TIME) where the ADT_TIME < ORDER_TIME.

Important Notes:

• In the example below, I had to fix the VISIT_ID in the PATIENT_TRANSFERS table since the value was 1 instead of 10.
• I did this in MySQL since your DBMS was not specified, but you should be able to alter the syntax to your corresponding DBMS if it doesn’t work already. Ultimately, the idea and gist of how you can accomplish this is there.

Schema (MySQL v8.0)

CREATE TABLE PATIENT_ORDERS (
  `INDEX_ID` INTEGER,
  `VISIT_ID` INTEGER,
  `PATIENT_ID` INTEGER,
  `ORDER_ID` INTEGER,
  `ORDER_TIME` DATETIME
);

INSERT INTO PATIENT_ORDERS
  (`INDEX_ID`, `VISIT_ID`, `PATIENT_ID`, `ORDER_ID`, `ORDER_TIME`)
VALUES
  ('1', '10', '1', '100', '2022-06-01 08:19:00'),
  ('2', '10', '1', '101', '2022-06-05 16:30:00'),
  ('1', '22', '2', '109', '2022-08-07 10:30:00'),
  ('1', '50', '3', '111', '2022-09-02 11:00:00');

CREATE TABLE PATIENT_TRANSFERS (
  `VISIT_ID` INTEGER,
  `DEP_NAME` VARCHAR(5),
  `ADT` VARCHAR(11),
  `ADT_TIME` DATETIME
);

INSERT INTO PATIENT_TRANSFERS
  (`VISIT_ID`, `DEP_NAME`, `ADT`, `ADT_TIME`)
VALUES
  ('10', 'UnitA', 'Admission', '2022-05-25 06:00:00'),
  ('10', 'OR', 'Transfer In', '2022-05-30 09:00:00'),
  ('10', 'UnitA', 'Transfer In', '2022-05-30 21:00:00'),
  ('10', 'OR', 'Transfer In', '2022-06-05 12:00:00'),
  ('10', 'UnitA', 'Transfer In', '2022-06-05 16:00:00'),
  ('22', 'UnitB', 'Admission', '2022-08-06 21:00:00'),
  ('22', 'OR', 'Transfer In', '2022-08-07 01:00:00'),
  ('22', 'UNITB', 'Transfer In', '2022-08-07 09:00:00'),
  ('50', 'UnitA', 'Admission', '2022-09-01 12:00:00'),
  ('50', 'OR', 'Transfer In', '2022-09-02 08:00:00'),
  ('50', 'Unit', 'Transfer In', '2022-09-02 10:00:00');

Query #1

SELECT a.INDEX_ID,
       a.VISIT_ID,
       a.PATIENT_ID,
       a.ORDER_ID,
       a.ORDER_TIME,
       b.ADT_TIME,
       b.DEP_NAME
FROM PATIENT_ORDERS a
INNER JOIN PATIENT_TRANSFERS b ON a.VISIT_ID = b.VISIT_ID
WHERE b.ADT_TIME =
  (SELECT MAX(ADT_TIME)
   FROM PATIENT_TRANSFERS
   WHERE visit_id = b.visit_id
     AND ADT_TIME < a.ORDER_TIME);

View on DB Fiddle