NSG/543 Week 4: Queries, sample paper

How Many Hours Was Someone Alone? Five Queries That Turn the Vigil Program's Tables Into Answers, With the Logic of Each

[Student Name]

University of Phoenix

NSG/543: Database Management

Week 4 Assignment

[Instructor Name]

[Date]

The hospital, the program and all data are a composite written for a model paper.

In Weeks 2 and 3, I designed the tables and forms for our hospital's No One Dies Alone program database. It has now been in use for one quarter: 41 vigil requests, 486 scheduled shifts and 59 active volunteers. This paper writes five queries that answer the steering committee's questions. Each query is shown in structured query language with its logic explained in plain words.

Query 1: Requested and Covered Hours

Question: in the quarter, how many vigil hours were scheduled and how many were covered by a volunteer?

Query: SELECT SUM(scheduled hours) AS requested, SUM(CASE WHEN FilledStatus = 'filled' THEN scheduled hours ELSE 0 END) AS covered FROM Shift JOIN Vigil ON Shift.VigilID = Vigil.VigilID WHERE Vigil.RequestDateTime falls in the quarter AND FilledStatus is not 'canceled'.

Logic in plain words: add up the length of every shift that was scheduled for vigils requested in the quarter, excluding shifts canceled because the patient died or family arrived, and separately add up the length of shifts that were filled.

Result: 944 hours requested and 781 covered, 82.7%. One hundred sixty-three hours, nearly seven days in total, were scheduled at a dying patient's bedside with no one there.

Query 2: When Coverage Fails

Question: at what times of day are shifts most often unfilled?

Query: SELECT the hour block of ScheduledStart, COUNT of shifts, COUNT of unfilled shifts, and the unfilled percentage FROM Shift WHERE not canceled GROUP BY hour block ORDER BY hour block.

Logic: group every scheduled shift by its starting time and count how many in each group were unfilled.

Result: shifts starting at 0200 and 0400 were unfilled 39% and 44% of the time, compared with 6% for shifts between 1000 and 1800. Most uncovered hours are overnight.

Query 3: Time to First Volunteer

Question: how long after a request does the first volunteer arrive?

Query: SELECT Vigil.VigilID, Vigil.RequestDateTime, MIN(Shift.ActualStart) AS first_arrival, the difference in minutes FROM Vigil JOIN Shift ON Vigil.VigilID = Shift.VigilID WHERE FilledStatus = 'filled' GROUP BY Vigil.VigilID, then the median of the differences.

Logic: for each vigil, find the earliest actual start of any filled shift and subtract the request time.

Result: the median time to first volunteer was 2 hours and 40 minutes. For requests made after 2200, it was 6 hours and 15 minutes.

Query 4: Training Due

Question: which active volunteers need refresher training in the next 60 days, or are already overdue?

Query: SELECT Volunteer.FirstName, Volunteer.LastName, MAX(Training.ExpirationDate) AS current_expiration FROM Volunteer JOIN Training ON Volunteer.VolunteerID = Training.VolunteerID WHERE Volunteer.Status = 'active' AND CourseName in orientation or annual refresher GROUP BY the volunteer HAVING current_expiration is within 60 days or already past.

Logic: for each active volunteer, find the latest training expiration date and list those expiring soon or already expired.

Result: 11 volunteers, including 3 already overdue who had signed in to shifts; the Week 3 form now alerts them and the coordinator, but these shifts predated that rule.

Query 5: Requests by Unit

Question: which units request vigils most often, and are their requests covered equally?

Query: SELECT Unit.UnitName, COUNT of vigils, SUM of requested hours, SUM of covered hours, covered percentage FROM Vigil JOIN Unit JOIN Shift, grouped by unit.

Logic: combine vigils with their units and shifts and total requested and covered hours for each unit.

Result: the medical intensive care unit and the oncology unit made 26 of 41 requests. Coverage was similar across units, which suggests that time of day, not location, drives gaps.

Query Writing Choices

Several choices in these queries shape their results and are documented with each saved query. Canceled shifts are excluded from requested hours, because a shift canceled after the patient died was never needed; including them would understate coverage. Time blocks are based on scheduled start rather than actual start, because unfilled shifts have no actual start. The median rather than the mean is used for time to first volunteer, because a few very long waits would pull the mean upward.

A Query That Was Not Written

The committee also asked how volunteers felt about their vigils. That question cannot be answered by the database, which holds times and counts, not experiences. Programs that have implemented vigils describe volunteer support and debriefing as essential (Bradas et al., 2014), and the committee will gather that information through volunteer meetings rather than forcing it into a table.

Performance

The queries run in under two seconds on two years of data. As the program grows, indexes on VigilID and VolunteerID in the Shift table will keep them fast.

Checking the Queries

Each query was checked by calculating its result a second way. For Query 1, I counted a random sample of 30 shifts by hand; the covered percentage in the sample, 83%, matched. For Query 3, I reviewed five vigils in the log. Codd (1970) argued that users should be able to ask questions of data without knowing how the data are stored; checking results against hand counts is how users confirm that the questions were asked correctly.

Reading the Numbers Carefully

One quarter is a short period, and 41 vigils are few. The overnight gap is large enough to be unlikely to reflect chance, but the unit comparison rests on small numbers for most units and should be read as a first look. The queries will be rerun each quarter, and the trends will matter more than any single result. Nursing informatics texts emphasize that stored queries make this kind of consistent repeated measurement possible (McGonigle & Mastrian, 2022).

What the Queries Tell the Program

Taken together, the queries show that the program covers most requested hours, but its gaps are concentrated overnight, and requests made late in the evening wait more than six hours for a first volunteer. A few volunteers served with lapsed training before the form's alert existed. And gaps do not depend on the unit. The findings point to one priority: overnight coverage.

Next Queries

Two further queries are planned: the share of vigils in which the patient died before any volunteer arrived, and the number of shifts each volunteer worked, to spot volunteers carrying too much and at risk of burning out.

Saved Queries

All five queries are saved in the database with descriptions, so the coordinator can run them each quarter without rewriting them. McGonigle and Mastrian (2022) describe stored queries as a way to make routine reporting consistent, which Week 5 will build on.

Conclusion

Five queries turned the vigil database into answers: 82.7% of requested hours were covered, gaps cluster between 0200 and 0600, late-evening requests wait longest for a first volunteer, some volunteers' training lapsed and coverage does not differ by unit. Each query is tied to a question, explained in plain words and checked. Week 5 will present these answers as reports for each audience.

References

Bradas, C. M., Bowden, V., Moldaver, B., & Mion, L. C. (2014). Implementing the "No One Dies Alone" program: Process and lessons learned. Geriatric Nursing, 35(6), 471-473. https://doi.org/10.1016/j.gerinurse.2014.10.005

Codd, E. F. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377-387. https://doi.org/10.1145/362384.362685

McGonigle, D., & Mastrian, K. G. (2022). Nursing informatics and the foundation of knowledge (5th ed.). Jones & Bartlett Learning.