Friday Puzzler: Finding a meeting time

13

Posted in ColdFusion | Posted on 06-16-2006 | 3,141 views

Here is an interesting problem, and one I admit I'm not sure what the best answer is. You have two people who want to meet. However, both are quite busy and have a day full of meetings. Can you use ColdFusion to find a time where both are available? I've created a simple data set that contains a query of 4 rows. Person ID is the FK of the user. Name is the name. Start and End are the times of a meeting. Each row then is one meeting, or a busy time. Your solution needs to find a time where users 1 and 2 can meet up (on 6/16 obviously). Your solution should only search from 8AM to 6PM, and, potentially, handle a case where a meeting is impossible.

Here is some code that will generate the data for you. By the way - notice how I commit one of the cardinal sins of UDFs - accessing outside data from a UDF. Also notice how I don't care - it was a handy way to manipulate the core query quickly and easily. Anyway, here is the code:

view plain print about

1<cfscript>
2function addEvent(personid, name, start, end) {
3    queryAddRow(data);
4    querySetCell(data, "personid", arguments.personid);
5    querySetCell(data, "name", arguments.name);
6    querySetCell(data, "start", arguments.start);
7    querySetCell(data, "end", arguments.end);
8}
9</cfscript>
10
11<cfset data = queryNew("personid,name,start,end")>
12<cfset addEvent(1, "Ray", "6/16/06 8:00 AM", "6/16/06 10:00 AM")>
13<cfset addEvent(1, "Ray", "6/16/06 2:00 PM", "6/16/06 4:00 PM")>
14<cfset addEvent(2, "Lynn", "6/16/06 8:00 AM", "6/16/06 9:00 AM")>
15<cfset addEvent(2, "Lynn", "6/16/06 10:00 AM", "6/16/06 12:00 PM")>

Comments

[Add Comment] [Subscribe to Comments]

bethb said on 06-16-2006 at 8:15 AM #
	how long do they need to meet?

Raymond Camden said on 06-16-2006 at 8:17 AM #
	1 hour.

Michael said on 06-16-2006 at 8:45 AM #

I am currently downloading cold fusion because i haven't done any CF development for over a year and a half now. But here is my solution before debugging and actually making sure it works:

<cffunction name="locateMeetingTime">
<cfargument name="data">
<cfargument name="span">
   <cfset var sorted = QueryNew()>
   <cfset var beginTime="8:00 AM">
   <cfquery name="sorted" dbtype="query">
      SELECT * FROM arguments.data ORDER BY start
   </cfquery>

   <cfloop query="sorted">
      <cfif beginTime lte sorted.start and beginTime + arguments.span lte sorted.start>
         <cfreturn beginTime>
      <cfelse>
         <cfif sorted.end gt beginTime>
            <cfset beginTime = sorted.end>
         </cfif>
      </cfif>
      <cfif beginTime gte "6:00 PM">
         <cfreturn false>
      </cfif>
   </cfloop>
   <cfreturn false>
</cffunction>

Martin said on 06-16-2006 at 8:50 AM #

I had to reparse the dates in the original query which puzzles me a little but here's my solution:

<cffunction name="findFreeSlots">
   <cfargument name="data" type="query" required="true" />
   <cfargument name="attendees" type="string" required="true" hint="List of personIDs of attendees" />

   <cfset var l = structNew() />

   
   <cfloop query="arguments.data">
      <cfset querySetCell( data, "start", parseDateTime( start ), currentRow ) />
      <cfset querySetCell( data, "end", parseDateTime( end ), currentRow ) />
   </cfloop>

   <cfset l.today = "6/16/06" />
   <cfset l.freeSlots = "" />

   <cfloop from="8" to="17" index="l.hour">
      <cfset l.currentSlot = dateAdd( "n", 1, dateAdd( "h", l.hour, l.today ) ) />

      <cfquery name="l.whoIsBusy" dbtype="query">
         SELECT   *
         FROM   arguments.data
         WHERE   personID IN (#arguments.attendees#)
               AND ( '#dateFormat( l.currentSlot, "M/D/YY" )# #timeFormat( l.currentSlot, "h:mm tt" )#' BETWEEN start AND [end] )
      </cfquery>

      <cfif l.whoIsBusy.recordCount eq 0>
         <cfset l.freeSlots = listAppend( l.freeSlots, l.hour ) />
      </cfif>
   </cfloop>

   <h3>Possible meeting times:</h3>
   <cfif listLen( l.freeSlots ) gt 0>
      <cfloop list="#l.freeSlots#" index="l.slot">
         <cfoutput>#l.slot#:00 to #l.slot + 1#:00<br /></cfoutput>
      </cfloop>
   <cfelse>
   No available slots
   </cfif>
</cffunction>

<cfset findFreeSlots( data, "1,2" ) />

Raymond Camden said on 06-16-2006 at 8:54 AM #
	Martin, my strings for datetimes may not have been valid. To be fair, I should have created real dt objects.

Martin said on 06-16-2006 at 9:05 AM #
	Ray, I don't think your date strings are invalid. When using the dates as is, the between statement half works. For example when checking who's busy 8am to 9am, my query returned Lynn but not Ray. I tend to avoid comparing dates as strings because I'm never sure how CF is interpreting them behind the scenes (I'm sure you do to).

Michael said on 06-16-2006 at 9:16 AM #

I fixed the problems and now this works. Some things I would do differently is not hardcoding the date, but that is simple.

<cffunction name="locateMeetingTime">
<cfargument name="data">
<cfargument name="span">
   <cfset var sorted = "">
   <cfset var beginTime="06/16/2006 8:00 AM">
   <cfquery name="sorted" dbtype="query">
      SELECT * FROM arguments.data ORDER BY start
   </cfquery>
   <cfloop query="sorted">
      <cfif beginTime lte sorted.start and beginTime + arguments.span lte sorted.start>
         <cfreturn beginTime>
      <cfelse>
         <cfif sorted.end gt beginTime>
            <cfset beginTime = sorted.end>
         </cfif>
      </cfif>
      <cfif beginTime gte "06/16/2006 6:00 PM">
         <cfreturn false>
      </cfif>
   </cfloop>
   <cfreturn false>
</cffunction>

<cfscript>
function addEvent(personid, name, start, end) {
queryAddRow(data);
querySetCell(data, "personid", arguments.personid);
querySetCell(data, "name", arguments.name);
querySetCell(data, "start", arguments.start);
querySetCell(data, "end", arguments.end);
}
</cfscript>

<cfset data = queryNew("personid,name,start,end")>
<cfset addEvent(1, "Ray", "6/16/06 8:00 AM", "6/16/06 10:00 AM")>
<cfset addEvent(1, "Ray", "6/16/06 2:00 PM", "6/16/06 4:00 PM")>
<cfset addEvent(2, "Lynn", "6/16/06 8:00 AM", "6/16/06 9:00 AM")>
<cfset addEvent(2, "Lynn", "6/16/06 10:00 AM", "6/16/06 12:00 PM")>

<cfset meet = locateMeetingTime(data, "1:00")>
<cfdump var="#meet#">

Gus said on 06-16-2006 at 10:11 AM #

In a real app, I would likely do this with SQL to take advantage of DB functions that deal with dates/times, but this works for this quiz:

<cfoutput>
<cfloop from='8' to='17' index='i'>
<cfset hourStart = '6/16/06 #i#:00'>
<cfset hourEnd = '6/16/06 #i + 1#:00'>
<cfset busy = ''>
<cfloop query='data'>
<cfif i GTE REReplaceNoCase(timeFormat(start,'H:mm'),':.*','','all') AND I LT REReplaceNoCase(timeFormat(END,'H:mm'),':.*','','all')>
<cfset busy = 'Not '>
<cfbreak>
</cfif>
</cfloop>
#timeFormat(HourStart,'hh:mm tt')# - #timeFormat(HourEnd,'hh:mm tt')# #busy#Available<br>
</cfloop>
</cfoutput>

Michael said on 06-16-2006 at 10:27 AM #

Sorry for all the reposts, but I found a bug in my code and wanted to address it, it didn't handle the last meeting of the day case.

<cffunction name="locateMeetingTime">
   <cfargument name="data">
   <cfargument name="span">
   <cfargument name="date">
   <cfset var sorted = "">
   <cfset var beginTime="#arguments.date# 8:00 AM">
   <cfset var EOD = "#arguments.date# 6:00 PM">
   <cfquery name="sorted" dbtype="query">
      SELECT * FROM arguments.data ORDER BY start
   </cfquery>
   <cfloop query="sorted">

      <cfif beginTime lte sorted.start and (beginTime + arguments.span lte sorted.start ) and beginTime lt EOD>
         <cfreturn beginTime>
      <cfelse>
         <cfif sorted.end gt beginTime>
            <cfset beginTime = sorted.end>
         </cfif>
      </cfif>
      <cfif beginTime gte EOD>
         <cfreturn false>
      </cfif>
   </cfloop>
   <cfif beginTime + arguments.span lte eod>
      <cfreturn beginTime>
   </cfif>
   <cfreturn false>
</cffunction>

One thing that differentiates this solution from the others is a meeting doesn't have to start at the hour it rather starts when free time is available.

Raymond Camden said on 06-16-2006 at 10:31 AM #
	Michael, don't worry about all the posts. The point of this is to share code.

Edward T said on 06-16-2006 at 11:15 AM #

Here's a late entry.... I know this is solved, but I have such a TERRIBLE little hack, I couldn't help myself. This solution will handle up to 9 people, and takes advantage of the fact that meetings start on the hour. A very slight modification makes it possible to schedule meetings for n hours. Can't really go into half or quarter hours, though, since we're limited on the size of integers:

<cfset combinedSched = 0>
<cfoutput query="data" group="personid">
   <cfset attendee[data.personid] = '0,0,0,0,0,0,0,0,0,0,0'>
   <cfoutput>
      <cfset mtgDur = timeformat(data.end, 'H') - timeformat(data.start, 'H')>
      <cfloop from="1" to="#mtgDur#" index="i">
         <cfset attendee[data.personid] = listSetAt(attendee[data.personid], timeformat(data.start, 'H')-8+i, "1")>
      </cfloop>
   </cfoutput>
   <cfset combinedSched = numberFormat(combinedSched + replace(attendee[data.personid], ',','','all'), '00000000000')>
</cfoutput>

<cfset mtgTimes = find('0', combinedSched)>
<cfif mtgTimes>
<h3>Possible meeting times:</h3>
   <ul>
<cfloop condition="mtgTimes GT 0">
   <li><cfoutput>#timeFormat(mtgTimes + 7 & ':00', 'h:mm tt')#</cfoutput></li>
   <cfset mtgTimes = Find('0', combinedSched, mtgTimes+1)>
</cfloop>
</ul>
<cfelse>
   <h3>Sorry, can't meet today!</h3>
</cfif>

Ed Bartram said on 06-16-2006 at 11:42 AM #

I originally tried doing a query of queries approach only to find all DATEPART and CAST functions that I wanted to use didn't seem to be available. So I ended up storing the meetings in an Array to determine availability.

<cfset aAvail=ArrayNew(1)>
<cfloop index="ptr" from="8" to="17">
   <cfset aAvail[ptr]="yes">
</cfloop>
<cfloop query="data">
   <cfloop index="ptr" from="#DatePart('h',start)#" to="#DatePart('h',end)-1#">
      <cfset aAvail[ptr]="no">
   </cfloop>
</cfloop>
<p>Ray and Lynn are available the following times:<br />
<cfloop index="ptr" from="8" to="17">
   <cfif aAvail[ptr] is "yes">
      <cfoutput>#ptr#</cfoutput>:00<br>
   </cfif>
</cfloop>
</p>

Michael said on 06-16-2006 at 12:02 PM #

Also decided to create a similar function that looks for all possible meetings times, because people like choices.

<cffunction name="locateAllPossibleMeetingTimes">
   <cfargument name="data">
   <cfargument name="span">
   <cfargument name="date">
   <cfset var tmp = "">
   <cfset var meetings = ArrayNew(1)>
   <cfset var sorted = "">
   <cfset var beginTime="#arguments.date# 8:00 AM">
   <cfset var EOD = "#arguments.date# 6:00 PM">
   <cfquery name="sorted" dbtype="query">
      SELECT * FROM arguments.data ORDER BY start
   </cfquery>
   <cfloop query="sorted">

      <cfif beginTime lte sorted.start and (beginTime + arguments.span lte sorted.start ) and beginTime lt EOD>
         <cfset tmp = StructNew()>
         <cfset tmp["start"] = beginTime>
         <cfset tmp["finish"] = sorted.start>
         <cfset ArrayAppend(meetings, tmp)>
         <cfset beginTime = sorted.end>
      <cfelse>
         <cfif sorted.end gt beginTime>
            <cfset beginTime = sorted.end>
         </cfif>
      </cfif>
      <cfif beginTime gte EOD>
         <cfbreak>
      </cfif>
   </cfloop>
   <cfif beginTime + arguments.span lte eod>
         <cfset tmp = StructNew()>
         <cfset tmp["start"] = beginTime>
         <cfset tmp["finish"] = eod>
         <cfset ArrayAppend(meetings, tmp)>
   </cfif>

   <cfreturn meetings>
</cffunction>

usage:
<cfset meet = locateMeetingTime(data, "1:00", "06/16/06")>
<cfset arrMeetings = locateAllPossibleMeetingTimes(data, "1:00", "06/16/06")>
<cfdump var="#arrMeetings#">
<cfdump var="#meet#">

[Add Comment] [Subscribe to Comments]