Finding Available Time Slot C#

Question

We are building a slot booking system but the slots are dynamic that get updated live from the handhelds. I have got to the stage where we have the booking screen i know i have an avalible day and time slot between e.g. 8am and 10am. When i book the slot i need to put 2 records in a table 1 for job time and 1 for travel time.

e.g.

Slot 8am-10am

Travel Time = 20 mins (Start 8:00am Finish 8:20am)

Job Time = 30 mins (Start 8:20am Finish 8:50am)

This would then show on a schedule gantt view visually we will be able to see his travel time and job time as 2 blocks.

So if there where no other appointments booked the first appointment would start at 8am and finish at 8:50am but how can i see if there are any appointments booked in this slot and if they are find the finish time so i no what to set the start time for the next job.

second problem - it may be the case where i have an appointment for 8am - 8:30am and another for 9:30am - 10am (the gap in the middle is due to a customer canceling) so i need to be able to say i have a job that totals 40mins can i fit it in the gap, YES get the finish time (8:30) and insert the records that fills the gap.

Hope that makes sense, i would like to do the with c#. Any Ideas??

Answer 1

This is a tricky question to answer a) how are you storing your data? Also what does your data schema look like?

I would use something like this:

TaskId (int)
TaskName (string)
TaskStart (DateTime)
TaskEnd (DateTime)

In regards to your first question, you ask "How can I see if there are any appointments booked in this slot?", the answer is, check to see if there are any other appointments and then determine their start time and the duration (including travel). Then set the start time for travel to the end of the first appointment. (NOTE: in code I use NewTaskTravel for the travel task and NewTaskEndTime for the actual task's end time)

SELECT TaskId, TaskEnd FROM Tasks WHERE @NewTaskTravelStartTime < TaskEnd;

if TaskId is not null then 
    NewTaskTravelStartTime = TaskEnd
    NewTaskTravelEndTime = ...
end if

SELECT TaskId, TaskEnd FROM Tasks WHERE @NewTaskEndTime > TaskStart;

if TaskId is not null then
   a) warn user about conflict or 
   b) automatically re-schedule following task or 
   c) place task in different place
end if

In regards to your second question, "how do I insert records into the gap between appointment 1 and appointment 2", the answer is you just create a new record with a TaskStart time of 8:30 (or whatever the time is) and make sure the end time doesn't go over your next task start time.

Answer 2

You may want to take a look at the Specification design pattern, and how it can be used as part of a Builder (in this case to find and fill available slots). Read more in the excellent Domain-Driven Design book.