This project is one of the most exciting for me, and I would like to briefly share how I was entrusted with this important task.
To begin with, it is essential to know that the hospital in question is not just any hospital; it is what is known as a «teaching hospital.» Each year, medical graduates come to this hospital to pursue their specialization or subspecialization in pediatrics. Specifically, the National Autonomous University of Mexico (UNAM) sends students who wish to specialize in Surgery to this hospital annually. UNAM sets certain evaluation parameters that the hospital must meet to assess its specialty students.
However, due to various issues, the hospital failed to meet the evaluation standards required by UNAM, which led the university to decide to stop sending Surgery students. This decision was a significant loss for the hospital, as Surgery students play a crucial role in the operation of the surgical services. In response to this situation, the hospital’s surgeons reached out to me for assistance in resolving the issue.
Summary of problems to solve:
- Compliance with Evaluation Parameters: The need to meet the evaluation standards set by UNAM for the hospital to continue receiving Surgery specialty students.
- Retention of Surgery Students: The problem caused by UNAM’s suspension of sending Surgery students, which affected the operation of the surgical service at the hospital.
- Optimization of the Surgical Service: The need to improve the management and quality of the surgical service to meet UNAM’s requirements and recover the sending of students.
In this context, the hospital’s surgeons informed me that UNAM had provided them with a detailed document outlining all the parameters required to evaluate the Surgery specialty students. Below are some excerpts from this document:
My main task was to develop a tool from scratch that would systematize the parameters established by UNAM and automate the evaluation of surgery specialty students. This effort would not only allow the hospital to meet UNAM’s requirements but also transform how medical education evaluations are conducted.
What did I do to create this new tool?
To accomplish this project, I used two main technologies: SQL Server (with Visual Studio Code as the IDE) and Power BI.
- In SQL Server:
- I performed table normalization to ensure that all variables and fields required by UNAM were included.
- I established a relationship between the hospital’s surgical procedure catalog, used by residents to record post-surgery information, and UNAM’s procedure catalog.
- I implemented a trigger to automatically update the student’s academic year. For example, a student in their second academic year in 2024 will be automatically promoted to the third year in 2025.
Here are some examples of what I have done in SQL Server:
Table Creation and Data Insertion:
- Creation of tables and insertion of test data into the previously created tables.
Database Backup:
- Creation of a database backup that is executed daily at 12 PM via a simulated Scheduled Job (using the Windows Task Scheduler). The image also shows a stored procedure for registering surgeons’ skill categories.
VIEW Table Programming:
- Programming of VIEW tables to be used within Power BI and to facilitate data queries within the database itself.
Automation of Scripts and Triggers:
- On the right, there is a script that increments the school year of surgery students by one unit each time it runs, with a Scheduled Job set to execute this script every 365 days. On the left, there is a trigger that automatically updates the modification date to the current date when the residency year is updated.
- In Power BI:
- I designed a dashboard that automatically filters data based on the selected specialty student.
- The dashboard displays the number of surgical procedures approved according to the student’s skill level and academic year.
- It identifies which surgical procedures the student finds easier or more challenging and shows the skill level required by UNAM for each procedure based on the student’s academic year.
- It shows, through visual indicators, how many surgical procedures of each type the student should have completed throughout the year, both as a surgeon and as an assistant.
The dashboard has additional, more specific functions, but its core functionality revolves around these capabilities. I’ve included a link to the embedded dashboard on this page so you can explore it and see its functionality in detail.
What was achieved?
Although it has not yet been presented to UNAM authorities, who will visit later in the year, I have already conducted an official presentation to the hospital’s surgeons and some executives. I am pleased to say that I have never received so many compliments for a completed project.
The following achievements are anticipated once the tool is used and presented to UNAM:
- Compliance with Requirements: The hospital will meet UNAM’s evaluation standards for the surgery specialty.
- Optimization of Evaluations: Enhanced evaluation of students, enabling a more precise and automated review of their performance.
- Improved Service Operation: Increased efficiency in the surgical service by identifying areas for improvement and adjusting required skill levels for procedures.
- Recovery of Students: Potential for UNAM to resume sending surgery students to the hospital, which is crucial for the service’s operation.
