Volumes Data Analysis Using SQL

Nov. 23, 18009 174 words One minute

Contents

Knowing our population is important for further development and the improvement of care. Looking at volumes helps us understand the flow and activity of patients in our healthcare institutions.

Number of encounters occurred in 2019.

1
2
3
4


SELECT COUNT(*)
FROM ENCOUNTERS
WHERE START >= '2019-01-01'
AND START < '2020-01-01'

Identify more specific patients.

Let’s use the DISTINCT function to extract from the encounters column.

1
2
3
4


SELECT DISTINCT PATIENT
FROM encounters
WHERE START >= '2019-01-01'
AND START < '2020-01-01'

Shows a list of distinct patients, 910 rows returned.

If we wanted the total number returned, use the COUNT function:

1
2
3
4


SELECT COUNT(DISTINCT patient)
FROM ENCOUNTERS
WHERE START >= '2019-01-01'
AND START < '2020-01-01'

Identify the different encounters.

1
2
3
4


SELECT DISTINCT encounterclass
FROM ENCOUNTERS
WHERE START >= '2019-01-01'
AND START < '2020-01-01'

Return the total number of encounters, use the COUNT funtion:

1
2
3
4


SELECT COUNT(DISTINCT encounterclass)
FROM ENCOUNTERS
WHERE START >= '2019-01-01'
AND START < '2020-01-01'

Let's specify an ecounter that we want to extract:

1
2
3
4


SELECT * FROM ENCOUNTERS
WHERE START >= '2019-01-01'
AND START < '2020-01-01'
and encounterclass = 'ambulatory'

Return the total number of encounters, use the COUNT funtion:

1
2
3
4
5


SELECT COUNT(*)
FROM ENCOUNTERS
WHERE START >= '2019-01-01'
AND START < '2020-01-01'
and encounterclass = 'ambulatory'

Extract the inpatient class only:

1
2
3
4


SELECT * FROM ENCOUNTERS
WHERE START >= '2019-01-01'
AND START < '2020-01-01'
and encounterclass = 'inpatient'

If you want to get the total number of encounters in sepcific departments:

1
2
3
4
5


SELECT COUNT(*)
FROM ENCOUNTERS
WHERE START >= '2019-01-01'
AND START < '2020-01-01'
and encounterclass IN ('ambulatory', 'wellness', 'outpatient', 'urgentcare')

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

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