Solved

Appointment Scheduling

Posted on 2011-09-06
18
422 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
Comment Utility
you could do a if statment within 15min of projected finish time to still schedule
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
>> 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
Comment Utility

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
 

Author Comment

by:cpwilson
Comment Utility
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
Comment Utility
> 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_
Comment Utility
>> 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
> 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
Comment Utility

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_
Comment Utility
>> 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
Comment Utility

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_
Comment Utility
>> 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
Comment Utility
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
Comment Utility
>> 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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now