MediCore Hospital wants to measure the typical length of stay for completed patient visits. Write a SQL query to calculate the average time between admission and discharge using a single table.
discharge_time is not NULLadmissions
| Column | Type | Description |
|---|---|---|
| admission_id | INT | Unique admission record ID |
| patient_name | VARCHAR(100) | Patient name |
| department | VARCHAR(50) | Hospital department |
| admission_time | TIMESTAMP | Time the patient was admitted |
| discharge_time | TIMESTAMP | Time the patient was discharged; may be NULL for active stays |
| admission_id | patient_name | department | admission_time | discharge_time |
|---|---|---|---|---|
| 1 | Alice Chen | Cardiology | 2024-03-01 08:00:00 | 2024-03-03 10:00:00 |
| 2 | Brian Lee | Neurology | 2024-03-02 14:30:00 | 2024-03-02 20:30:00 |
| 3 | Carla Gomez | Orthopedics | 2024-03-04 09:15:00 | NULL |
| 4 | David Park | Cardiology | 2024-03-05 11:00:00 | 2024-03-06 11:00:00 |
| 5 | Emma Davis | Pediatrics | 2024-03-01 23:00:00 | 2024-03-02 05:00:00 |
| 6 | Farah Khan | Neurology | 2024-03-06 07:45:00 | 2024-03-08 07:45:00 |
| average_stay_hours |
|---|
| 27.00 |
| Column | Type | Description |
|---|---|---|
| admission_idPK | INT | Unique admission record ID |
| patient_name | VARCHAR(100) | Patient full name |
| department | VARCHAR(50) | Hospital department handling the admission |
| admission_time | TIMESTAMP | Timestamp when the patient was admitted |
| discharge_time | TIMESTAMP | Timestamp when the patient was discharged; NULL for active stays |
| admission_id | patient_name | department | admission_time | discharge_time |
|---|---|---|---|---|
| 1 | Alice Chen | Cardiology | 2024-03-01 08:00:00 | 2024-03-03 10:00:00 |
| 2 | Brian Lee | Neurology | 2024-03-02 14:30:00 | 2024-03-02 20:30:00 |
| 3 | Carla Gomez | Orthopedics | 2024-03-04 09:15:00 | |
| 4 | David Park | Cardiology | 2024-03-05 11:00:00 | 2024-03-06 11:00:00 |
| 5 | Emma Davis | Pediatrics | 2024-03-01 23:00:00 | 2024-03-02 05:00:00 |
| 6 | Farah Khan | Neurology | 2024-03-06 07:45:00 | 2024-03-08 07:45:00 |
| 7 | George Miller | 2024-03-07 12:00:00 | 2024-03-07 12:00:00 | |
| 8 | Hannah Scott | Oncology | 2024-03-08 18:20:00 | |
| 9 | Ivan Petrov | Emergency | 2024-03-03 01:00:00 | 2024-03-03 13:00:00 |
| 10 | Julia Wong | Pediatrics | 2024-03-09 06:00:00 | 2024-03-10 18:00:00 |
| 11 | Samuel Green | Emergency | 2024-02-29 00:45:35 | 2024-03-05 22:09:27 |
| 12 | Owen Brooks | Rehabilitation | 2024-03-05 14:42:56 | 2024-02-29 07:34:17 |
| 13 | Marcus Reed | Urology | 2024-02-29 08:09:52 | 2024-03-10 00:52:39 |
| 14 | Marcus Reed | Dermatology | 2024-02-28 17:17:18 | 2024-03-06 19:57:14 |
| 15 | Yara Hassan | Emergency | 2024-03-10 04:29:36 | null |
| 16 | Brian Lee | Orthopedics | 2024-03-05 15:52:56 | 2024-03-05 12:48:48 |
| 17 | Brian Lee | Urology | 2024-03-09 21:36:30 | 2024-03-05 20:32:38 |
| 18 | Quentin Hall | Pathology | 2024-03-10 09:55:29 | 2024-03-09 04:53:17 |
| 19 | Zane Cooper | Pediatrics | 2024-03-03 03:10:48 | 2024-03-09 22:36:35 |
| 20 | George Miller | Orthopedics | 2024-03-06 12:20:24 | 2024-03-06 18:01:42 |
| 21 | George Miller | General Surgery | 2024-03-03 08:37:54 | 2024-03-07 23:15:39 |
| 22 | Quentin Hall | Rheumatology | 2024-02-29 02:18:54 | 2024-02-28 07:26:46 |
| 23 | Ivan Petrov | Infectious Disease | 2024-03-10 18:37:49 | 2024-03-02 21:19:10 |
| 24 | Uma Kapoor | General Surgery | 2024-02-28 00:48:38 | 2024-03-04 16:08:57 |
| 25 | Yara Hassan | Hematology | 2024-03-03 22:39:23 | 2024-03-10 17:22:32 |
| 26 | Brian Lee | null | 2024-03-10 17:17:19 | 2024-03-09 16:22:24 |
| 27 | Marcus Reed | Rheumatology | 2024-03-05 20:18:01 | 2024-03-08 18:56:45 |
| 28 | Emma Davis | Emergency | 2024-03-03 09:17:47 | 2024-03-06 03:40:25 |
| average_stay_hours |
|---|
| 38.09 |
