asked on

Help needed with C# / SQL appointment scheduler

I'm trying to figure out a simple and efficient way to build part of an appointment scheduler. Basically, I have a SQL table with start and end times by date (the start and end times when a hairdresser is on the clock), and another table with existing scheduled appointments (includes time and date). What I need to do is create a list of available times in half hour increments to display in a datagrid, but I'm not sure where to start. Any ideas? This is a modification of an existing application, so I have limited ability to make changes to the existing tables, but I can add new tables and stored procedures.

Thanks-
Ethan

nmcdermaid

There are a few ways to do it. This is how I would do it;

1. Create a table which has a record for each 'timeslot'.

For example if you are open from 9 to 5 with 15 minute timeslots, that means 32 records.
The table has three fields:

StartDatetime (SMALLDATETIME)
EndDatetime (SMALLDATETIME)
Label (VARCHAR(20))

You populate this with all the timeslots.

Then you can create a view which joins this table (a 'timeslot' table) and your other two tables to return the data you want.

Post the existing table structure and I can help further.

esw074

ASKER

Cool. Ok, so let's say that the timeslot table you've proposed contains 32 timeslots beginning at 9 and ending at 5 using the fields above.

The appointments table contains existing appointment start and end times. Let's say that for today, March 19, it contains two appointments - one from 10:00am-11:00am, and one from 1:00pm to 2:00pm. We'll call the fields in the appointment table apptStartDate, apptStartTime and apptEndTime.

What I want to display is a list of available appointment times for March 19, meaning all of the appointment times in the timeslot table that are not already booked (every timeslot except 10am-11am and 1pm-2pm). What do you think?

ASKER CERTIFIED SOLUTION

nmcdermaid

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

esw074

ASKER

Sorry it took so long to confirm. This is great, thanks!