11

MySQL Version

The code will run in MySQL 5.5

Background

I have a table like the following one

CREATE TABLE t
( id INT NOT NULL AUTO_INCREMENT
, patient_id INT NOT NULL
, bed_id INT NOT NULL
, ward_id INT NOT NULL
, admitted DATETIME NOT NULL
, discharged DATETIME
, PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

This table is about patients in a hospital and it stores the beds where each patient spent some time while being hospitalized.

Each ward may have multiple beds and each patient may move to a different bed within the same ward.

Objective

What I want to do is to find how much time each patient spent in a specific ward without having moved to a different ward. I.e I want to find the total duration of the consecutive time he spent within the same ward.

Test case

-- Let's assume that ward_id = 1 corresponds to ICU (Intensive Care Unit)
INSERT INTO t
  (patient_id, bed_id, ward_id, admitted, discharged)
VALUES

-- Patient 1 is in ICU, changes some beds, then he is moved 
-- out of ICU, back in and finally he is out.
(1, 1, 1, '2015-01-06 06:05:00', '2015-01-07 06:04:00'),
(1, 2, 1, '2015-01-07 06:04:00', '2015-01-07 07:08:00'),
(1, 1, 1, '2015-01-07 07:08:00', '2015-01-08 08:11:00'),
(1, 4, 2, '2015-01-08 08:11:00', '2015-01-08 09:11:00'),
(1, 1, 1, '2015-01-08 09:11:00', '2015-01-08 10:11:00'),
(1, 3, 1, '2015-01-08 10:11:00', '2015-01-08 11:11:00'),
(1, 1, 2, '2015-01-08 11:11:00', '2015-01-08 12:11:00'),

-- Patient 2 is out of ICU, he gets inserted in ICU, 
-- changes some beds and he is back out
(2, 1, 2, '2015-01-06 06:00:00', '2015-01-07 06:04:00'),
(2, 1, 1, '2015-01-07 06:04:00', '2015-01-07 07:08:00'),
(2, 3, 1, '2015-01-07 07:08:00', '2015-01-08 08:11:00'),
(2, 1, 2, '2015-01-08 08:11:00', '2015-01-08 09:11:00'),

-- Patient 3 is not inserted in ICU
(3, 1, 2, '2015-01-08 08:10:00', '2015-01-09 09:00:00'),
(3, 2, 2, '2015-01-09 09:00:00', '2015-01-10 10:01:00'),
(3, 3, 2, '2015-01-10 10:01:00', '2015-01-11 12:34:00'),
(3, 4, 2, '2015-01-11 12:34:00', NULL),

-- Patient 4 is out of ICU, he gets inserted in ICU without changing any beds
-- and goes back out.
(4, 1, 2, '2015-01-06 06:00:00', '2015-01-07 06:04:00'),
(4, 2, 1, '2015-01-07 06:04:00', '2015-01-07 07:08:00'),
(4, 1, 2, '2015-01-07 07:08:00', '2015-01-08 09:11:00'),

-- Patient 5 is out of ICU, he gets inserted in ICU without changing any beds
-- and he gets dismissed.
(5, 1, 2, '2015-01-06 06:00:00', '2015-01-07 06:04:00'),
(5, 3, 2, '2015-01-07 06:04:00', '2015-01-07 07:08:00'),
(5, 1, 1, '2015-01-07 07:08:00', '2015-01-08 09:11:00'),

-- Patient 6 is inserted in ICU and he is still there
(6, 1, 1, '2015-01-11 12:34:00', NULL);

In the real table the rows are not consecutive but for each patient the discharge timestamp from one row == the admission timestamp of the next row.

SQLFiddle

http://sqlfiddle.com/#!2/b5fe5

Expected Result

I would like to write something like the following:

SELECT pid, ward_id, admitted, discharged
FROM  (....)
WHERE ward_id = 1;

(1, 1, '2015-01-06 06:05:00', '2015-01-08 08:11:00'),
(1, 1, '2015-01-08 09:11:00', '2015-01-09 11:11:00'),
(2, 1, '2015-01-07 06:04:00', '2015-01-08 08:11:00'),
(4, 1, '2015-01-07 06:04:00', '2015-01-07 07:08:00'),
(5, 1, '2015-01-07 07:08:00', '2015-01-08 09:11:00'),
(6, 1, '2015-01-11 12:34:00', NULL);

Please, do take note that we can't group by patient_id. We must retrieve a separate record for each ICU visit.

To put it more plainly, if a patient spends time in ICU, then moves out of it and then returns back there, I need to retrieve the total time he spent in each ICU visit (i.e. two records)

4
  • 1
    +1 for an eloquent question, clearly explaining a complex (and interesting) problem. If I could vote it up twice for the added bonus of an SQLFiddle, I would. However, my instinct is that without either CTEs (common table expressions) or windowing functions, this will not be possible in MySQL. What dev environment are you using, i.e. you might be obliged to do this through code.
    – Vérace
    Jan 16 '15 at 14:55
  • @Vérace I 've stated to write code that retrieves all the rows that correspond to ICU beds and I am grouping them in Python.
    – pmav99
    Jan 16 '15 at 15:12
  • Of course if this can be done in a relatively clean way in SQL I will prefer it.
    – pmav99
    Jan 16 '15 at 15:20
  • As languages go, Python is pretty clean! :-) If you're not stuck to MySQL and you require a F/LOSS database, may I recommend PostgreSQL (in many ways vastly superior to MySQL IMHO) which does have CTEs and Windowing functions.
    – Vérace
    Jan 16 '15 at 15:31
4

Query 1, tested in SQLFiddle-1

SET @ward_id_to_check = 1 ;

SELECT
    st.patient_id,
    st.bed_id AS starting_bed_id,          -- the first bed a patient uses
                                           -- can be omitted
    st.admitted,
    MIN(en.discharged) AS discharged
FROM
  ( SELECT patient_id, bed_id, admitted, discharged
    FROM t 
    WHERE t.ward_id = @ward_id_to_check
      AND NOT EXISTS
          ( SELECT * 
            FROM t AS prev 
            WHERE prev.ward_id = @ward_id_to_check
              AND prev.patient_id = t.patient_id
              AND prev.discharged = t.admitted
          )
  ) AS st
JOIN
  ( SELECT patient_id, admitted, discharged
    FROM t 
    WHERE t.ward_id = @ward_id_to_check
      AND NOT EXISTS
          ( SELECT * 
            FROM t AS next 
            WHERE next.ward_id = @ward_id_to_check
              AND next.patient_id = t.patient_id
              AND next.admitted = t.discharged
          )
  ) AS en
    ON  st.patient_id = en.patient_id
    AND st.admitted <= en.admitted
GROUP BY
    st.patient_id,
    st.admitted ;

Query 2, which is the same as 1 but without the derived tables. This will probably have better execution plan, with proper indexes. Test in SQLFiddle-2:

SET @ward_id_to_check = 1 ;

SELECT
    st.patient_id,
    st.bed_id AS starting_bed_id,
    st.admitted,
    MIN(en.discharged) AS discharged
FROM
    t AS st    -- starting period
  JOIN
    t AS en    -- ending period
      ON  en.ward_id = @ward_id_to_check
      AND st.patient_id = en.patient_id
      AND NOT EXISTS
          ( SELECT * 
            FROM t AS next 
            WHERE next.ward_id = @ward_id_to_check
              AND next.patient_id = en.patient_id
              AND next.admitted = en.discharged
          )
      AND st.admitted <= en.admitted
WHERE 
      st.ward_id = @ward_id_to_check
  AND NOT EXISTS
      ( SELECT * 
        FROM t AS prev 
        WHERE prev.ward_id = @ward_id_to_check
          AND prev.patient_id = st.patient_id
          AND prev.discharged = st.admitted
      )
GROUP BY
    st.patient_id,
    st.admitted ;

Both queries assume that there is a unique constraint on (patient_id, admitted). If the server runs with strict ANSI settings, the bed_id should be added in the GROUP BY list.

4
  • Note that I modified the insert values in the fiddle, because your discharged/admitted dates did not match for patient ids 1 and 2. Jan 16 '15 at 18:17
  • 2
    In awe - I really thought that it was impossible given the lack of CTEs. Strangely, the first query wouldn't run for me in SQLFiddle - a glitch? The second one did though, but may I suggest that the st.bed_id be removed, as it is misleading. Patient 1 didn't spend all of his first stay in ward 1 in the same bed.
    – Vérace
    Jan 17 '15 at 0:01
  • @Vérace, thnx. At first, I thought, too, that we needed a recursive CTE. I've corrected a missing join on patient_id (that no one noticed ;) and added your point about the bed. Jan 17 '15 at 12:29
  • @ypercube Thank you so much for your answer! This is really helpful. I am going to study this in detail :)
    – pmav99
    Jan 21 '15 at 19:42
0

PROPOSED QUERY

SELECT patient_id,SEC_TO_TIME(SUM(elapsed_time)) elapsed
FROM (SELECT * FROM (SELECT patient_id,
UNIX_TIMESTAMP(IFNULL(discharged,NOW())) -
UNIX_TIMESTAMP(admitted) elapsed_time
FROM t WHERE ward_id = 1) AA) A
GROUP BY patient_id;

I loaded you sample data into a local database on my laptop. Then, I ran the query

PROPOSED QUERY EXECUTED

mysql> SELECT patient_id,SEC_TO_TIME(SUM(elapsed_time)) elapsed
    -> FROM (SELECT * FROM (SELECT patient_id,
    -> UNIX_TIMESTAMP(IFNULL(discharged,NOW())) -
    -> UNIX_TIMESTAMP(admitted) elapsed_time
    -> FROM t WHERE ward_id = 1) AA) A
    -> GROUP BY patient_id;
+------------+-----------+
| patient_id | elapsed   |
+------------+-----------+
|          1 | 76:06:00  |
|          2 | 26:07:00  |
|          4 | 01:04:00  |
|          5 | 26:03:00  |
|          6 | 118:55:48 |
+------------+-----------+
5 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXPLAINED

In the subquery AA, I compute the number of seconds elapsed using UNIX_TIMESTAMP() by subtracting UNIX_TIMESTAMP(discharged) FROM UNIX_TIMESTAMP(admitted). If the patient is still in the bed (as indicated by discharged being NULL), I assign the current time NOW(). Then, I do the subtract. This will give you an up-to-the-minute duration for any patient still in the ward.

Then, I aggregate the sum of the seconds by patient_id. Finally, I take the seconds for each patient and use SEC_TO_TIME() to display hours, minutes, and seconds of the patient stay.

GIVE IT A TRY !!!

3
  • For the record, I ran this in MySQL 5.6.22 on my Windows 7 laptop. It gives an error in SQL Fiddle. Jan 16 '15 at 16:26
  • 1
    thank you very much for your answer. I am afraid though that this does not answer my question; probably I wasn't clear enough in my description. What I want to retrieve is the total time spent on each stay in ICU. I don't want to group by patient. If a patient spends time in ICU, then moves out of it and then returns back there, I need to retrieve the total time he spent in each visit (i.e. two records).
    – pmav99
    Jan 16 '15 at 16:38
  • on a different topic, wrt to your (original) answer I think that using two subqueries is not really necessary (i.e. table A and AA). I think that one of them is enough.
    – pmav99
    Jan 16 '15 at 16:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.