Appointment Scheduling

you could do a if statment within 15min of projected finish time to still schedule

>> Am able to check for conflicts in appointment times

From what you've described, it sounds like you just need to add a filter on service type. So you're not just searching for conflicting appointment times but ones that CAN'T be booked at the same time.  

For example if you're trying to book service X, which cannot be booked at the same time as services:  A, B and C

       WHERE  AppointmentDateTime BETWEEN ... your date filters here ...
       AND       ApointmentType IN ('A', 'B', 'C')

Does your schema have a list of services table?   I could see a services table holding the name of the service and the approximate time the service tables (a man's cut is 20 minutes, a woman's cut is 40, a man's cut and color is 30, etc).   In this scenario, the services that could overlap may have a flag indicator on the record to say that there is time to set which could be used for a cut.  

There has to be flexibility though - the person making the appointments will understand the rules and the system has to be able to allow her to make adjustments.   For example, she knows that James has really long hair and his cut would take longer... etc..

Have tried both methods with only partial results. Am able to filter for same time appointments with same services. This gives a conflicted schedule, which is okay. But if a different type of service requested for same time period, same stylist, it also thinks there is a conflict, even if this is one that could be taken during that period. Example - stylist already scheduled to do cut and wash from 10-11:30 but can also take a nail service. Nail service will be considered as conflict because it is requested during same time period.

> if a different type of service requested for same time period, same stylist, it also thinks there is a conflict,

If it shouldn't do this, then there is simply a bug in your code.. it's hard to help you without seeing the code


Is it really a rule that you can't take the same service during the same period?   I would think that they could do two colors during the same period because there is down-time for both; and I would think that you could not overlap a cut with a nail because the stylist is active full time with both.  


You could create a smarter matrix of overlapping... for each service you could setup a list of services that could overlap with THAT service....

>> If it shouldn't do this, then there is simply a bug in your code.. it's hard to help you
>> without seeing the code

Agreed. We need to see the code. Can you post it?

Using this currently, but not written in stone to keeping it

<cfquery name="get_my_stylist" datasource="#datasource#" username="#dbuser_name#" password="#db_password#">
select * from stylists
</cfquery>
<cfquery name="get_current_schedule" datasource="#datasource#" username="#dbuser_name#" password="#db_password#">
select * from schedule
</cfquery>
<cfquery name="get_services_offered" datasource="#datasource#" username="#dbuser_name#" password="#db_password#">
select * from services
</cfquery>
Currently scheduled: <cfoutput>
<cfloop query = 'get_current_schedule'>
#dateformat(startdate, 'dddd, mmmm dd, yyyy')# - #timeformat(starttime, 'hh:mmtt')# - #timeformat(endtime, 'hh:mmtt')# - #stylistID#<br>
</cfloop>
</cfoutput>
<br><br>
<cfform method='post' action="apptest2.cfm?action=new" enctype="multipart/form-data" name="thisform" onSubmit="return checkApplication();">
                     
     Date To Check                       <cf_easydate 
      formname="thisform" 
      fieldname="Conflict_Date" 
      format="mm/dd/yyyy"
      default="#Now()#" 
      dblspeed="3"
      ShowCalanderButton="Yes"
      writefile="yes"
    >
<br>
start time <input type='text' name='time_start' size='7'><br>
end time <input type='text' name='time_end' size='7'><br>
Stylist:<select name='stylistID' size='1'>
<option value = "" selected>Choose A Stylist</option>
<cfoutput query = 'get_my_stylist'>
<option value='#stylistID#'>#stylist#</option>
</cfoutput>
<option value='2'>Someone Else</option>
</select><br><br>
Services Desired: <br><cfoutput query = 'get_services_offered'>
<input type='checkbox' name='service' value='#service#'>#service#<br>
</cfoutput><br>
<input type='submit' value='Check Date'>
</cfform>
<cfif action is 'new'>
<cfset end = #TimeFormat(DateAdd('h', 2.5, time_start),'hh:mm:ss tt')#>

<cfquery name="check_conflict" datasource="#datasource#">

SELECT stylistid, startdate, starttime, endtime, service_type, no_service from schedule

WHERE stylistid = <cfqueryparam value="#form.stylistid#">   
AND startdate = <cfqueryparam value="#form.conflict_date#">
AND <cfqueryparam value="#form.time_start#"> BETWEEN starttime AND endtime
</cfquery> 


<cfif check_conflict.recordcount>

<br>
There is a conflict
<br>
<!-- following output for testing purposes only -->
<cfoutput>Requested date #dateformat(form.conflict_date, 'ddd, mmmm dd, yyyy')#<br>
Requested Stylist #form.stylistid#<br>
requested service: #form.service#<br>

Found date #dateformat(check_conflict.startdate, 'ddd, mmmm dd, yyyy')#<br>
found stylist #check_conflict.stylistid#<br>requested time: #timeformat(time_start, 'hh:mmt')#<br>
Proposed end time #timeformat(end, 'hh:mmt')#
</cfoutput>
<cfoutput>
<cfloop query = 'check_conflict'>
how many found #check_conflict.recordcount#<br>
Found date #dateformat(check_conflict.startdate, 'ddd, mmmm dd, yyyy')#<br>
found stylist #check_conflict.stylistid#</cfloop></cfoutput>
<cfelse>
There is no conflict<br>
<!-- following output for testing purposes only -->
<cfoutput>Requested date #dateformat(form.conflict_date, 'ddd, mmmm dd, yyyy')#<br>
Requested Stylist #form.stylistid#<br>
requested time #timeformat(form.time_start, 'hh:mmt')#<br>
requested service: #form.service#<br>
Found date #dateformat(check_conflict.startdate, 'ddd, mmmm dd, yyyy')#<br>
found stylist #check_conflict.stylistid#<br>
requested time: #timeformat(time_start, 'hh:mmt')#<br>
Proposed end time #timeformat(end, 'hh:mmt')#</cfoutput><cfoutput>
<cfloop query = 'check_conflict'>
how many found #check_conflict.recordcount#<br>
Found date #dateformat(check_conflict.startdate, 'ddd, mmmm dd, yyyy')#<br>
found stylist #check_conflict.stylistid#</cfloop></cfoutput>
</cfif>
</cfif>

Select allOpen in new window

Thinking now actually of setting length of time into table for services (ie - wash takes 40 minutes, etc.) and sum the number of minutes to produce endtime

> Thinking now actually of setting length of time into table for services (ie - wash takes 40 minutes, etc.) and sum the number of minutes to produce endtime

agreed, this may be helpful.  Keep in mind men, women, children may have different durations associated with a service and the stylist may want to tweek the appointment when entering it, so I would not lock it in, but default it to the service length.

I also suggest making the app more visual, I can see a chart with times down the left side with stylists across the top.  Clicking on a cell would allow the person to enter their client name there..

>> Thinking now actually of setting length of time into table for services (ie -
>> wash takes 40 minutes, etc.) and sum the number of minutes to produce endtime

Those are good suggestions.  But getting back to the original question, you'd still need to add the filters described to allow for overlapping appointments based on appointment type.

Also, if you'll be searching based on the total time needed for the appointment

               wash + service x  + service y == total time

you'd need a different query than what you've currently got to accurately search for conflicts based on the total time you're attempting to book.  What does your services table look like?

I think the search function will be attempting to locate a time slot for a given service.  Given that the client is on the phone asking for a color and cut which requires 40 minutes - where can you fit that in?  The search has to be smart enough to know that it can overlap with some other services (maybe) and perhaps show a list of possible times...   ?

>> The search has to be smart enough to know that it can overlap with some other services

If it's strictly based on time and service types, it's doable.  You just need to know what type of service is being requested, the start and total time required. Then a list of service types conflict with the requested type.   Whether that list can be hard coded like I showed earlier or needs to be table driven depends on how many service types there are. Though ultimately my preference is table driven too, because it's easier to main.  

The only thing I'm not sure about now is this service table. The search query might involve a join or 1 or 2 look up queries. Depends on the structure of the service table.

All quite good suggestions, however you are correct in saying that the issue is still filtering for the overlaps. The services table has an id nbr for each service, name, cost and length of time required. Using a basic calendar function to show days/time periods that stylists are already booked.

Sounds interesting and might work. Doing training at the police department until middle of next week (implementing new records management system) so can't get to try it until then.

Ended up going a completely different direction with this, but wanted to split the points because each of you were extremely helpful in trying to make this work. My thanks to you both.