EasyHealthcare7 min

Encounters still open

Challenge

Find visits that have started and do not have an end date.

Business context

Bed management treats a missing end date as a visit that is still open. A completed visit always has ended_on filled in.

Instructions

Return id, patient_id, department, and started_on for encounters where ended_on is NULL. Sort by started_on, then id.

Schema

patients

id INTEGER · primary key
name TEXT · may include extra spaces
birth_date TEXT
sex TEXT · F, M, Female, or mixed case
city TEXT

encounters

id INTEGER · primary key
patient_id INTEGER
department TEXT
started_on TEXT
ended_on TEXT · nullable while the visit is open
visit_type TEXT · outpatient or inpatient

diagnoses

id INTEGER · primary key
encounter_id INTEGER
code TEXT
description TEXT

claims

id INTEGER · primary key
encounter_id INTEGER
billed_cents INTEGER
paid_cents INTEGER · nullable until adjudicated
status TEXT · paid, denied, submitted
submitted_on TEXT

Sample rows

patients
id	name	birth_date	sex	city
1	Amina Yusuf	1984-04-12	F	Lagos
2	Hugo Berger	1972-11-03	M	Berlin
3	Mei Lin	1990-06-21	f	Shanghai

encounters
id	patient_id	department	started_on	ended_on	visit_type
1	1	Cardiology	2025-01-06	2025-01-06	outpatient
2	1	Cardiology	2025-03-02	2025-03-04	inpatient
3	2	Orthopedics	2025-01-11	2025-01-11	outpatient

diagnoses
id	encounter_id	code	description
1	1	I10	Essential hypertension
2	2	I21.9	Acute myocardial infarction
3	3	M17.1	Knee osteoarthritis

claims
id	encounter_id	billed_cents	paid_cents	status	submitted_on
1	1	18000	15000	paid	2025-01-07
2	2	240000	198000	paid	2025-03-06
3	3	22000	0	denied	2025-01-12

Draft saves automatically

Ctrl or Cmd + Enter runs the query. The database resets on every run.

Run the query to see rows from the sample database.

Next exercise: Customers without a completed order