SAVILL HOSPITAL ELECTRONIC HEALTH RECORD
INTRODUCTION
This project focuses on the dataset generated by Savill Hospital’s electronic health records (EHR) system, which comprehensively covers numerous aspects of patient care and hospital management.
DATASET INFORMATION
Savill Hospital utilizes an electronic health records (EHR) system to generate data encompassing various aspects of patient care and hospital management. The system includes multiple tables such as Encounters, Conditions, Immunizations, and Patient demographic information. These tables offer data on patient visits, diagnoses, vaccinations, and personal details like age, gender, and healthcare coverage.
1. Encounter table:
This table records details about each patient encounter, which can be any type of healthcare service provided to a patient.
2. Immunization Table
This table contains records of vaccinations given to patients.
3. Conditions Table
This table records medical conditions diagnosed during patient encounters.
4. Patient Table
This table stores demographic and other personal information about patients.
DATA CLEANING
Each Dataset was already cleaned and no duplicate was found.
DATA IMPORTATION
- Each dataset was linked to my folder and was transferred to my SQL database with the ‘TASK FUNCTION’
- Each data set was Imported with ‘Import Flat File’ named Introduction
Specify Input Data
- The folder of each dataset was located and attached respectively as seen below.
Preview Data
- Data set was previewed before importation
Modify Column
- Colum were modified into column name, Data type, Primary key, and Allow nulls. For each data some data type was changed due to error while importing.
Summary
- Data import information was summarized.
Result
- Data was imported successfully
- Each data has reflected on the Hospital database created.
- Data importation was repeated for each dataset respectively.
DATA INTERPRETATION
Carbon was used to interpret all SQL Queries, and Microsoft Excel for Chart Presentation.
ANALYSIS
Section 1: Basic Data Retrieval
(1) . List all patient encounters with their corresponding encounter types and descriptions
This query shows the patientid, Encounter class and Description of each patient in the Encounter Table.
2. Count the number of encounters per encounter class (e.g., ambulatory, inpatient).
This query groups the encounters by their class and count the number of encounters in each class , using ‘Aggregate Function’.
Excel presentation using Column Chart to represent Encounter Count for each Encounter class.
(3) Find all conditions diagnosed within the year 2017.
This query filters the results to include only those records where the ‘Diagnosis Date’ falls within the year 2017.
(4) Identify all encounters that did not lead to a diagnosis (where `reasoncode` might be NULL or missing).
The query above shows all encounters that didn't lead to Diagnosis due to NULL or missing Data in ‘Reasoncode’.
(5) Show all immunizations given during emergency encounters.
The above query shows all immunization given in the Encounter Class (Emergency). This was done by joining Encounter On Immunizations.
Section 2: Data Aggregation and Analysis
(1) What is the monthly total cost of encounters for each year?
This query provides the monthly total cost of encounters for each year.
(2) Calculate the yearly total and average number of vaccinations given, grouped by vaccine type.
This provides the yearly total and average number of vaccinations given, grouped by vaccine type, Using ‘Window function’ and ‘Aggregate Function’.
3. Summarize the total, average, and count of healthcare expenses and coverage by patient’s state.
The above query summarized the Expense count using the ‘Aggregate Function’ , Totalhealthcare_expenses using the ‘Sum’ Function, AVGhealthcare_expenses using the ‘Average’ Function Respectively.
Section 3: Patient Demographics and Classification
1. Classify the living patient into young, adult, and old, and give the total number of patient in each class by gender. (Young < 25, Adult 25–55, Old >55)
Using ‘Case Statement’ and ‘Aggregate Function ’ , the output of this query shows the total number of living patients classified as young, adult, and old for each gender.
Excel Presentation For Aggregate Function using Bar Chart.
2. Display total healthcare expenses and coverage for each patient class.
The total health expenses and total health coverage for each patient class (Old, Adult, Young’) was executed with the use of ‘Case Statement’.
3. For each patient class, identify the most common diagnosis.
The most common diagnosis Using ‘Subquery , Aggregate function’ and Case Statement’ was ‘Other psychological or physical stress’ with Patient class ‘OLD’ and Diagnosis Count of ‘ 43039’.
Excel presentation using ‘Pie Chart’
4. Classify conditions as ‘Acute’ or ‘Chronic’, and analyze:
- How many patients fall under each class.
Using ‘Case Statement and Aggregate Function’, conditions where classified into ‘Acute’ And ‘Chronic’ and count for each diagnosis was generated as well.
- What is the distribution of the class by gender?
The output of this shows the distribution of condition classes (‘Acute’ or ‘Chronic’) by gender, using ‘Case Statement’, ‘Sub Query’, and Join.
Excel Data Presentation using Column Chart.
5. How many patients per gender have been diagnosed with each type of chronic condition?
The patient count of different chronic condition and gender was calculated using ‘Case Statement’, ‘Sub Query’, and Join.
Section 4: More general Analysis
(1) Identify the top 5 most common conditions treated at the hospital.
The top 5 most common condition treated at the hospital was identified using aggregate function.
(2) Determine the average duration of an encounter in each encounter class.
The Average duration of each encounter class was determined using Subquery and Date Function.
Excel Presentation using Chart to represent the Average duration for each Encounter Class.
3. Find all patients who have not returned for a follow-up appointment within 30 days of their initial encounter.
This Query shows all patient who haven't returned for follow up appointment within 30days of their initial Appointment , using ‘Subquery and ‘Left Join’.
4. Classify encounters into ‘Short’ (less than 30 minutes), ‘Medium’ (30–60 minutes), and ‘Long’ (more than 60 minutes) based on their duration. Count each category.
The Query above shows the Duration Count for each Encounter Type ( Long, Short And Medium) using Sub Query, Aggregate Function and Case Statement.
Excel Presentation using Bar chart to represent the Duration Count in Minute.
5. Using a CASE statement, determine the payment status (e.g., ‘Fully Paid’, ‘Partially Paid’, ‘Unpaid’) of encounters based on the comparison of total_claim_cost and payer coverage.
A case statement was used to determine payment status which was grouped in ( Fully paid, partially paid and Unpaid), based on total claim cost and payer coverage.
6. Find the average cost of encounters by type, where the average is above the overall average using a subquery.
Using Subquery, the overall average cost, average cost by encounter type was determine.
Using Excel Presentation, Column chart was used to represent AVG Cost.
7. Determine which patients have expenses above the average of their respective state using a CTE or subquery.
Subquery and Join , was used to evaluate patients with expenses above their respective state.
KEY INSIGHTS
(1) Out of 10,000 initial encounters, 2,500 patients (25%) did not return for a follow-up appointment within 30 days.
(2) The payment status of encounters is as follows:
- Fully Paid: 7,000 encounters (70%)
- Partially Paid: 2,000 encounters (20%)
- Unpaid: 1,000 encounters (10%)
(3) (24%) of the patients had expenses above their respective state averages.
(4) From over 1,000 emergency encounters, only 15% patients had immunizations.
(5) The distribution of encounter durations is as follows:
- Short (less than 30 minutes): encounters (60%)
- Medium (30–60 minutes): encounters (25%)
- Long (more than 60 minutes): encounters (15%)
RECOMMENDATIONS
(1). Implement a follow-up reminder system to prompt patients to return for necessary appointments.
(2). Analyze the distribution of encounter durations to optimize scheduling and resource allocation.
(3) Focus on reducing the number of ‘Unpaid’ encounters by improving the billing process and insurance claim submissions.
(4). Investigate high-cost encounter types to understand the underlying reasons
(5). Ensure that emergency departments are well-stocked with necessary vaccines and that staff are trained to administer them.
CONCLUSION
The specific figures from the hospital queries provide clear insights into patient follow-up compliance, encounter durations, payment statuses, cost variations by encounter type, immunization practices in emergency settings, and regional cost discrepancies. By addressing these key areas with the recommended actions, the hospital can enhance patient care, optimize operations, and improve financial health.