Emergency Room Data Using SQL

Nov. 22, 11009 181 words One minute

Contents

The ER is always a hot topic because of how busy it gets and not enough staff to serve incoming patients in a timely manner. With the data we have, we will look at emergency room throughout or length of stay. This will help us understand the status of patient access, patient volume and health system support.

How many enocunters occurred in 2019?

1
2
3
4


SELECT * FROM encounters
WHERE START >= '2019-01-01'
    AND START < '2020-01-01'
    AND ENCOUNTERCLASS = 'emergency'

Looking at the total number:

1
2
3
4


SELECT count(*) AS 'ER Volumes' FROM encounters
WHERE START >= '2019-01-01'
    AND START < '2020-01-01'
    AND ENCOUNTERCLASS = 'emergency'

Identify conditions using the JOIN function

We will look at the conditions table and the encounters table:

1
2
3
4
5
6
7


SELECT CON.DESCRIPTION, count(*)
FROM encounters ENC
JOIN conditions CON ON ENC.Id=CON.encounter
WHERE ENC.START >= '2019-01-01'
    AND ENC.START < '2020-01-01'
    AND ENC.ENCOUNTERCLASS = 'emergency'
GROUP BY CON.DESCRIPTION;

We could add LEFT JOIN to pull all information from encounters column that meet the criteria identified in this WHERE query, if there’s no match, stil return data (could be null data).

1
2
3
4
5
6
7


SELECT CON.DESCRIPTION, count(*)
FROM encounters ENC
LEFT JOIN conditions CON ON ENC.Id=CON.encounter
WHERE ENC.START >= '2019-01-01'
    AND ENC.START < '2020-01-01'
    AND ENC.ENCOUNTERCLASS = 'emergency'
GROUP BY CON.DESCRIPTION;

What is the LOS in the ED?

1
2
3
4
5


SELECT * , DATEDIFF(MINUTE,START,STOP) 'LOS in Minute'
FROM encounters
WHERE START >= '2019-01-01'
    AND START < '2020-01-01'
    AND ENCOUNTERCLASS = 'emergency';

Note: DATEDIFF is specific to SMSS and it is the same as TIMESTAMPDIFF function for MySQL.

Dataset source: https://synthetichealth.github.io/synthea/

Github repository: https://github.com/ib99