Date Comparision Help Needed!

I am making a scheduling app to help book singers on holiday caroling jobs.  I have two tables (how they are set up can be seen here: http://www.wrightengineers.com/mytables.jpg).  The Jobs table has start and end times for each job.  The SingerSchedule table has start and end times of periods of time that singers are NOT available, in other words, where they have previous commitments.  I am having trouble writing code to tell me which singers are available to book which jobs.

So, if I have a singer who is NOT available 9a-5p, I want to be able to find out if they can work a job that is 6p-10p.  Or, if the singer is completely open all day long, there won't be an entry in the singerschedule table since they've only been entering conflicts & previous commitments.  To further complicate things, each job gets a quartet: soprano, tenor, alto, bass.  Only one of each type (S,A,T,B) can be assigned to one job, for a total of four singers.

Any ideas?
LVL 2
David WilliamsonIT DirectorAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
mrichmonConnect With a Mentor Commented:
Basically you need to write a conflict detection.

It is very easy.

Consider the possibilities

Booked from 2-4

You can have the following situations

12:00 - 1:00 - completely before
1:00 - 2:00 - completely before but touches start
1:00-3:00 overlaps start
1:00-5:00 - overlaps start and end
3:00-4:00 - overlaps end
4:00-5:00 completely after but touches end
5:00-6:00 - completely after
2:30-3:30 - completely within overlap range

And a few others where it touches a begin or end.

Basically you want to know if they are available - i.e. no overlap

This will tell you if there is an overlap:

(assume cloumsn are starttime and endtime and fields are reservationstart and reservationend)

SELECT * FROM yourtable WHERE
starttime <= reservationend AND endtime >= reservationstart

Then something like

<cfif query.RecordCount GT 0>
You have a conflict
<cfelse>
They are available
</cfif>
0
 
David WilliamsonIT DirectorAuthor Commented:
Thank you!  With your help and that of others, I've come up with the following which seems to work well:

<cfquery datasource="llc" name="getSingers">
      SELECT SingerID,FirstName,LastName,VoicePart FROM Singers
      ORDER BY SingerID
</cfquery>

<cfquery datasource="llc" name="getJobTimes">
      SELECT StartDate,EndDate FROM Jobs
      WHERE JobID = #URL.jid#
</cfquery>
<cfoutput>
<b>These are the singers that are available for #DateFormat(getJobTimes.StartDate,'DDD, MMM d, yyyy')# from #TimeFormat(getJobTimes.StartDate)# - #TimeFormat(getJobTimes.EndDate)#</b><br><br>
<table class="jobList">
      <tr>
            <td>
            <b>Name</b>
            </td>
            <td>
            <b>Part</b>
            </td>
            <td>
            <b>Conflict As Entered In Schedule </b>
            </td>
      </tr>
      <tr>
            <cfloop query="getSingers">
                  <cfset Full_Name = getSingers.FirstName&' '&getSingers.LastName>
                  <cfset Voice_Part = getSingers.VoicePart>
                  <cfquery datasource="llc" name="findOpen">
                        SELECT SingerSchID FROM SingerSchedule
                        WHERE SingerSchedule.SingerID = #getSingers.SingerID# AND DATE_FORMAT(noAvailStart,'%Y-%m-%d') = DATE_FORMAT('#getJobTimes.StartDate#','%Y-%m-%d')
                  </cfquery>
                  <cfif findOpen.RecordCount>
                        <cfloop query="findOpen">
                              <cfquery datasource="llc" name="checkConflict">
                                    SELECT SingerID,noAvailStart,noAvailEnd FROM SingerSchedule
                                    WHERE SingerSchID = #findOpen.SingerSchID# AND
                                    (
                                                (DATE_FORMAT(noAvailEnd,'%H:%i:%s') <= DATE_FORMAT('#getJobTimes.StartDate#','%H:%i:%s')) OR
                                                (DATE_FORMAT(noAvailStart,'%H:%i:%s') >= DATE_FORMAT('#getJobTimes.EndDate#','%H:%i:%s'))
                                    )
                              </cfquery>
                              <cfif checkConflict.RecordCount>
                              <td style="text-align:left ">#Full_Name#</td><td>#Voice_Part#</td><td>#TimeFormat(checkConflict.noAvailStart)# - #TimeFormat(checkConflict.noAvailEnd)#</td></tr><tr>
                              </cfif>
                        </cfloop>
                  <cfelse>
                        <td style="text-align:left ">#Full_Name#</td><td>#Voice_Part#</td><td>None</td></tr><tr>
                  </cfif>
            </cfloop>
            </cfoutput>
      </tr>
</table>

What do you think?
0
 
mrichmonCommented:
It really matters what you think :o)

Does it work how you need it to?
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
David WilliamsonIT DirectorAuthor Commented:
yes, it does work.  However, I am always looking for ways to write better, faster, and cleaner code.
0
 
mrichmonCommented:
The only improvements I would recommend is to use cfqueryparam to parametarize all inputs to the database.

FOr example

<cfquery datasource="llc" name="getJobTimes">
     SELECT StartDate,EndDate FROM Jobs
     WHERE JobID = #URL.jid#
</cfquery>

Becomes (assuming jid is an integer)

<cfquery datasource="llc" name="getJobTimes">
     SELECT StartDate,EndDate FROM Jobs
     WHERE JobID = <cfqueryparam cfsqltype="cf_sql_type" value="#URL.jid#">
</cfquery>
0
 
David WilliamsonIT DirectorAuthor Commented:
good idea.  I usually use that in all my UPDATE and INSERT statements, I didn't actually think about that here.
0
All Courses

From novice to tech pro — start learning today.