Solved

Appointment Scheduling

Posted on 2011-09-06
18
433 Views
Last Modified: 2012-05-12
Attempting to create an appointment schedule for hair salon. Am able to check for conflicts in appointment times (no appointment taken between start and likely finish time of hair session), but it seems that a stylist may take an added appointment during the same time for different services. Not sure how to code for that part. In other words, can code for services desired during unconflicted time periods, but having problems checking for other services that cannot be provided during same time periods.
0
Comment
Question by:cpwilson
  • 6
  • 6
  • 5
  • +1
18 Comments
 
LVL 22

Expert Comment

by:plusone3055
ID: 36492855
you could do a if statment within 15min of projected finish time to still schedule
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36492912
>> 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')



0
 
LVL 39

Expert Comment

by:gdemaria
ID: 36492924

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..
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:cpwilson
ID: 36492961
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.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 36493061
> 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....

0
 
LVL 52

Expert Comment

by:_agx_
ID: 36496059
>> 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?
0
 

Author Comment

by:cpwilson
ID: 36496604
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>

Open in new window

0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 250 total points
ID: 36496732
well, the check you perform doesn't have any logic other than checking the time span.   So you would have to add any exceptions to this logic..

<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>


Just a suggestion - but I would guess the user would have an easier time entering start time and length of appointment rather than trying to figure out the end time and typing the extra characters...

Start Time  [  11:30]    Length (minutes):   [  20]

0
 

Author Comment

by:cpwilson
ID: 36496982
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
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 36497459
> 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.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 36497479

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..

0
 
LVL 52

Expert Comment

by:_agx_
ID: 36497974
>> 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?
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 36498069

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...   ?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36498145
>> 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.
0
 

Author Comment

by:cpwilson
ID: 36500371
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.
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 250 total points
ID: 36512324
>> The services table has an id nbr for each service, ... length of time required.

You can use that info to calculate the requested start/end time. Then query on those values to ensure there are no conflicting appointments during that time.  Conceptually it'll look like this.

SELECT  Cstylistid, startdate, starttime, endtime, service_type, no_service
FROM     schedule
WHERE  stylelistID = @requestedStylistID
AND       startDate = @requestedDate          <!--- ie a valid 09/15/2011 --->
AND       startTime < @requestedEndTime     <!--- ie a valid time 14:00:00 --->
AND      @requestedStartTime < endTime       <!--- ie a valid time 14:45:00  --->

Open in new window


Then adding in a service filter is pretty simple.  Just search only for services that *can't* overlap with the one you're requesting.  So if you're searching for an open appointment for service X, and service X cannot be booked at the same time as services Y and Z, filter on those types only:

   WHERE ... other filters ....
   <!--- list of services that CAN'T overlap with the one you're requesting --->
   AND     ServiceType IN ('Service Y', 'Service Z')

Open in new window


You just need to decide how you want to store the list of service conflicts.  You could use a table based matrix as gd suggested or hard code them if you only have a few service types.  IMO table based is better.  

Make sense?
0
 

Author Comment

by:cpwilson
ID: 36514297
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.
0
 

Author Closing Comment

by:cpwilson
ID: 36593976
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.
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question