Runner-Up
Matt Mitchell, Network Analyst
Alpena Regional Medical
Center, Alpena, Michigan
mjmitchell@agh.org
Taming the Scheduling Monster
Matt Mitchell describes himself as “network
analyst by trade, and a DB developer by
hobby.” But Matt’s enthusiasm for database
programming has trickled down into his IT
job with Alpena Regional Medical Center,
where, he says, “for me every project uses
a database because I love creating solutions
with databases.” Matt, who has previous
experience developing Web-based database
solutions, jumped in to tackle the challenge of computerizing a paper-based system
of scheduling logs that nurses and staffing
employees at the medical center used for
bidding on overtime shifts.
Until fairly recently, multiple copies of
paper vacancy logs (lists of shifts that needed
to be filled) were circulated around the
nursing units. Nurses signed up for vacancies, and the completed logs went to the
staffing office where staffing employees had
to reconcile the various logs, then award
overtime based on a complex set of labor-union rules. Matt sought to develop a system
that made the bidding process fairer to the
nurses by letting them access vacancy information from a central Web site and made
it easier for staffing employees to fairly and
accurately award the vacancies.
Matt’s solution consists of an ASP.NET
Web application front end containing separate areas for the nurses and staffing personnel,
a SQL Server 2000 database that contains
vacancy information and the status of bids
(awarded or not), and a simple reporting
capability. “The ASP.NET Web application
accesses SQL Server with ADO.NET using
the System.Data.SqlClient namespace,” Matt
explains. A short subprocedure in the Web
application establishes a connection to the
SQL Server database that contains the vacancy information and stores the connection as a SqlConnection object. Then a
procedure creates a SqlDataAdapter object
that uses one of the stored procedures or
views included in the database as its SELECT
statement. Finally, code in the application uses
the SQlDataAdapter object to fill a DataSet
object. “Since I’m not a full-time programmer, the nice thing about ADO.NET
is that I don’t need to know a lot about the
intricate details of accessing data from a SQL
Server database. All I need to do is establish
the connection, create a data adapter, and fill
my dataset by using the data adapter. ADO
.NET handles the rest,” says Matt.
The main Web page, which the nurses
view, has a drop-down list for selecting the
unit(s) to check for vacancies. Nurses log in to
the Nursing Vacancy Log by using their Active
Directory (AD) account information, then
simply click a button to bid for a vacancy, and
the system records the date and time of their
bid. When staffing employees log in, they see
a screen for setting up the schedules that will
have vacancies available to the nurses, entering
the vacancies, and approving the bids.
Interestingly, Matt first used Microsoft
Access for the database, then switched to
SQL Server. “When I begin implementing a
SQL Server database design, I always start by
creating an Access Project file and selecting
the Project (New Data) option. Access is a
nice tool for creating tables, establishing relationships, and creating the database diagram,
and the familiarity of this program makes it
easy for me to get started,” he says.
The third part of the solution, a reporting
capability, was already available. “On our
intranet server, we run SSW Access Reporter.NET for IIS, software that lets us serve
up Access reports in PDF format to our
intranet Web applications.” The Nursing
Vacancy Log has only one report—a bid
approval report. Matt designed the report in
Access, then added an ASP.NET page that
invokes the Access report.
Although Matt’s system hasn’t gone live
yet (its release was delayed because of a job
change in the staffing office), Matt received
positive feedback from the staffing coordinator about his solution, and nursing staff
have all trained and completed a practice
exercise using the system. Even so, the benefits are evident. “The time spent reconciling
vacancy logs and awarding bids becomes
negligible, and nurses no longer have to
hunt down a vacancy log or leave the unit
to visit the staffing office,” says Matt.
Prev. page
1
2
[3]
4
5
next page 
