asked on

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?

ASKER CERTIFIED SOLUTION

mrichmon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

David Williamson

ASKER

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>
These are the singers that are available for #DateFormat(getJobTimes.StartDate,'DDD, MMM d, yyyy')# from #TimeFormat(getJobTimes.StartDate)# - #TimeFormat(getJobTimes.EndDate)# 
<table class="jobList">
 <tr>
 <td>
 Name
 </td>
 <td>
 Part
 </td>
 <td>
 Conflict As Entered In Schedule 
 </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?

mrichmon

It really matters what you think :o)

Does it work how you need it to?

David Williamson

ASKER

yes, it does work. However, I am always looking for ways to write better, faster, and cleaner code.

mrichmon

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>

David Williamson

ASKER

good idea. I usually use that in all my UPDATE and INSERT statements, I didn't actually think about that here.